Form Combo Box Saving Wrong Information

[Steve62]

Hi, I am an Access 2010 novice and have created a database for our Debit Notes/Credit Notes; however I am experiencing a problem. I have a form which contains a combo box listing Debit Note numbers, this auto-populates 6 text boxes with the customer details relating to the Debit Note, the main form then has a sub-form into which the Credit Note details are entered.

My problem is this: When I select the Debit Note number from the combo box, all the correct customer details are automatically filled into the text boxes as expected, however when I exit the form (via a "close" button which saves the data) and then open the Credit Notes table the Debit Note number is missing from the table and the wrong Supplier ID number is listed. The Supplier ID numbers are unique (i.e.: 1, 2, 3, 4, etc.), but the table saves any entry made via the above method as Supplier ID 50.



Could you please help resolve this issue as I am getting nowhere with this!!!

Many thanks in advance,

Steve62

[June7]

You want the DebitNote number saved to CreditNote table?

What are the Master/Child links properties of the CreditNote subform container control set to?

Are you saving customer details into the note tables? Sounds like duplication of data.

Want to provide db for analysis? Follow instructions at bottom of my post. Identify the objects involved in this issue.

[Steve62]

Hi June7, many thanks for your reply. In answer to your questions, both the Debit Notes and Credit Notes tables list the Debit Note number as a cross-reference - could this duplication be causing the problem? The Master/Child links of the Credit Note sub-form are set to SupplierID.

To clarify the problems I'm having: when using the InputCreditNote form the information should be saved directly to the CreditNotes table; however the Debit Note number does not save and the SupplierID reference is the same for every entry, when it should list the reference for the supplier the Credit Note has been entered for.

The form InputDebitNote saves all information correctly but does not save the SupplierID. As I said, I am a complete novice and am getting increasingly frustrated at not being able to resolve this. Any help you are able to provide would be most gratefully received.

[June7]

I will have to review db. See previous post.

[Steve62]

Hi June7,

Please find a zipped copy of the database attached. Any advice you can offer will be gratefully received.

Kind Regards,

Steve62

DebitNotes2 (COPY)_Backup.zip

[June7]

Part of issue is in DebitNotes table the autonumber field is designated the primary key but you are saving the DebitNo field value in CreditNotes. Designate DebitNo field as primary key. Then in Relationships you link CreditNotes and DebitNotes on SupplierID fields. Links should be on PK/FK fields. So both Notes tables should be linked to Suppliers (jointype 'Include all records from Notes...'). Then you can also show a link between the Notes tables on the DebitNo fields (DebitNo in DebitNotes as PK).

Don't need q_DebitNotes. Combobox RowSource can pull from the table.

The f_InputCreditNote has RecordSource that links the Notes tables on SupplierID fields. This won't work - see above. Can include DebitNotes table in the query to view related data (in locked textboxes), join on PK/FK fields (jointype 'Include all records from CreditNotes...'). If you want the DebitNo saved to CreditNotes, bind the combobox to DebitNo field in CreditNotes.