Solved Appended Data changes to Table ID (3 Viewers)

[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.