Two Tabels One Form Problem

[samohtwerdna]

Hello again,

I have a strange idea that I wondered if any could help me with.

I have two main tables created for different purposes. The first table contains all the major order confirmation information and is called MainJobInfo. The second Table contains all the Short Order info and is called tblShortOrderMain. The second table has similar job names and job numbers (except the short orders have suffix added e.g. 3321 [jobNum] .101 [shortOrderSuffix]) and a few other shared feilds. I would like to pull as much info from the MainJobInfo Tables as possible to limit re-entry.

I have a frmMain for the Short Orders where I have the user enter the main feild info needed. On this Form I would like to - for example - pull the detailers name from the MainJobInfo Table when I enter the Job # - Keep in mind on this form the jobNum will have the added Suffix - So when the user types in "3321.101" I want to match the first four digits to the jobNum in my first Table "3321" and then retreive the detailers name and populate my combo Box that holds the detailers name with it.

What do you think? - Does this make sense?? Any Ideas???

Oh, and yes, for know I kneed to keep the two tables seperate...

Thanks

[OBP]

No problem, you just need to filter the Main table records using a query.
I would have a field on the short order form that is updated with the 3321. The query would then use this field in the queries criteria row.
The Main form is opened with this record and the data transferred to your short form using VBA.
You could also use a drop down combo where you select the Main number first and then add the .101 to it.