Solved Appended Data changes to Table ID

[dullster]

I have a Payroll form that when I fill it out the MyUCA is correct. I run an Append Query to move the data to the tblDemoTrans. When it transfers it changes to the ID of tblDemoUCAs. Attached is screen shots of how it is entered, how it transfers and where the number is coming from. Thoughts on why it is doing that?

 

[DHookom]

I found your images confusing. It seems that your field in question is a lookup field and behaving like lookup fields behave. You would almost always want to use and store the ID value, not the text. The text value is stored only in one table based on sound relational database theory.

 

[arnelgp]

maybe field MyUCA on tblDemoTrans has a Lookup table on it.

 

[Mark_]

Why are you not adding your data to tblDemoTrans and instead using an append query?

 

[dullster]

Good question. At the time I did this, I had a good reason. Don't remember why now but I will look into that.

 

[Pat Hartman]

Avoiding bound forms in favor of doing it yourself means you aren't taking advantage of the work the RAD tool performs for you. Why bother to even incur the overhead of using Access if you aren't going to let it do what it does best?

 

[dullster]

I remember now why I appended the payroll after every payroll. I want to filter my subform by each CheckNr. I don't want to see all the checks. I'm not sure I can do that. I have the subform set up now to write directly to the tblDemoTrans table instead of appending.

Any ideas how to filter the subform by CheckNr?

 

[June7]

How are forms related?
If you want to provide db for analysis, follow instructions at bottom of my post.

 

[dullster]

On form frmPayroll, I want the CheckNr field on the Main form to group the subform checks and be able to add a new check.

 

[DHookom]

Is there a reason you don't simply enter the new check at the New Record on the subform?

 

[dullster]

I don't want a continue form. I want to see one check at a time or enter a new check. Does that make since?

 

[DHookom]

Tell us exactly what you want to see in the subform and how you expect to add a new record? Did you consider changing the properties of the subform to Add mode?

 

[June7]

What do you mean by "group"?

Can set subform DataEntry property to Yes. That will not display existing records, only new records input in that session.

Also suggest code to set CheckNr DefaultValue property so user does not have to repeatedly enter check number

Private Sub CheckNr_AfterUpdate()
Me.CheckNr.DefaultValue = Me.CheckNr
End Sub

And maybe VBA to automate determining next available check number at beginning of session. This does have risks in a multi-user environment.

I am not sure this is optimal structure for documenting payroll.

 

[dullster]

I want the subform to show each check individually instead a continued form.

Would changing the subform to add mode do what i want it to do?

 

[dullster]

What do you mean by "group"?

Can set subform DataEntry property to Yes. That will not display existing records, only new records input in that session.

Also suggest code to set CheckNr DefaultValue property so user does not have to repeatedly enter check number and maybe VBA to automate determining next available check number at beginning of session.

I am not sure this is optimal structure for documenting payroll.

I have set the DataEntry to yes. How do i sent CheckNr DefaultValue to to repeat?

 

[June7]

I edited my previous post while you were reading. Review again.

 

[dullster]

I edited my previous post while you were reading. Review again.

This worked, thank you.

 

[Pat Hartman]

On form frmPayroll, I want the CheckNr field on the Main form to group the subform checks and be able to add a new check.

I don't understand your schema. What are the Demo tables? Why is Marital status in tblpayrolltaxes? Should tblSalaryTable be called tblPayment? Why is salary info in tblEmployees?

tblEmployees should be demographic info.
tblEmpRate should be dated and have the hourly rate as of a start date. The end date can be null until the employee gets a raise at which point, you put in the end date.
Every payroll application has some number of deductions such as local, state, federal taxes, insurance, savings, etc. and other payments such as a shift premium or uniform allowance, etc So, you need another table that defines those rates.
tblCheck should have the employeeID, CheckNum, dateIssued, and maybe a status so you can void a check if you need to.
tblCheckDetails should have the calculated deductions, total salary, other payments. Then you calculate the net amount for the check by summing the individual items.

I suppose you need a way to cut special checks but I'm not sure what that would entail. You would have to charge tax so again you need child records but you probably wouldn't have any other deductions or additional payment types.

I think you need to go back to square one with your schema.

You are far better off getting quickbooks or something similar. QB does a good job of covering the basics. Most companies use a payroll service because they don't want to have to keep up with tax code changes which can be a nightmare.