subform not autopopulating

[RuralGuy]

I added a Lookup ComboBox to your form.

[slimjen]

OK. got the invoice number combo box. next i need to get the carrierid and carriername to populate from the main form based on invoicenumber. i don't want them as combo boxes. that would imply users have to select. i want it to autopoplate based on the invoice number. then i need the remainder of the subform to autopopulate. do i need to base the subform off a query to get all the fields i want? thanks

[RuralGuy]

Right now your form is displaying a record from the RecordSource table. I'm not sure what you mean.

[slimjen]

i want the carrierid and carriername to populate based on the invoice number. i have one record in the table. when i select the invoice number i have the code in the afterupdate event with the following: Me.TXT_CARRIERID = Me.TXT_INVOICENUMBER.Column(2) And Me.TXT_CARRIERNUMBER = Me.TXT_CARRIERID.Column(2). I am certain my code in the vba event is incorrect but i don't know how to populate multiple fields with the previous record!

[RuralGuy]

The RecordSource for that form has 2 records in it and those fields already have data in them.

[slimjen]

yes for the invoice table. i want to populate the shipment table with the cooresponding invoice data. the subform recordsource is the shipment table begining with the carrierid and carriername. sorry for the confusion. thanks

[RuralGuy]

There was no SubForm in the db you posted. BTW, you are using an mdb but you are showing that you use ac2007. Why the mdb rather than accdb?

[slimjen]

sorry. i don't know. i am using 2003. see attached. the subform is on the shipment tab. thanks

[slimjen]

all, by day; i am working on this database in access 2003. I couldn't get the database that contains tabs and subforms. ironically; i took the database home to work on it. i have access 2007. strange enough mostly everything i was trying to do fell into place! the problem is that i was trying to get my fields to autopopulate fields based on previous records. that's still a problem. also; the one field i was able to get to autopopulate; no longer works now that i am using 2007! can anyone explain this to me. i know 2007 is not backwards capatiable so i've been told but you can open a 2003 database in 2007. i ve attached a sample of my database for review. i need help please at least point me in the right direction. i have a deadline and i am spending too much time trying to figure things out. please help thanks

[RuralGuy]

I've been working on it but some things just take time; especially with my feeble mind.

[slimjen]

didn't know Thought i lost ya. I'll be more patient and have faith that i get through this to meet my deadline. Thanks so much!

[RuralGuy]

Jen,
You do not have the PrimaryKey of one table as the PrimaryKey of another table. You need to set up your relationships better. Try to do it with AutoNumbers and the PrimaryKey of each table and LongIntegers and the ForeignKey in the other tables.

[slimjen]

the txt_invoicenumber is primary key in invoice table and foreign in the shipment table. i.e. i have bothe txt_invoicenumber in both tables and setup a relationship between the two. Is this not correct. So, i should use a autonumber for all like invoiceid and trackingid and build 1:many relationship? I don't mind learning. Please give me an example.
Thank you

[RuralGuy]

The Shipment and the Accounting tables have the *same* PrimaryKey. Not a good idea.

[slimjen]

Oops! I didn't see this. I will correct. So are the primary keys, the foriegn keys and relationships correct between the invoice and shipment tables?
Thanks