Combo boxes on form with two tables update problem

[newcoder54]

I have two tables and one form. One table is the row source and the other is the data table. A common field UniqueLastName ties the two together. I have several combo boxes on the form. When the user picks a last name the whole form should populate with the persons other info and save it to the data table. As the user makes changes to the data, the After Update code should update the data table. Looking at the Northwoods example helps some but it seems to count on a sub form and uses the Me!Name = Me!Name type of method. This does not work for me. Going through some Tek-Tips and other material still has not turned me on to what I need. Ideas please.

 

[misscrf]

I could be wrong but it sounds like uniquelastname might not be an auto id number, which is best for a pk/fk.

If you can, take a look at this link on normalization.

http://www.utteraccess.com/forums/s...r=373096&page=0&view=expanded&sb=5&o=&fpart=1

follow the rules and tables will do as told. I promise.
:-D

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[newcoder54]

Thanks misscrf,

The tie on UniqueLastName keeping the two tables in sych works fine but, for example, when the user selects from a list of companies, it should update the data table with that selection. I understand the After Update but do not know the code that will do it. Something like, CompanyDropDownList = CompanyDataTableList. The Me!Compnay = Me!Company sounds like the right idea but I think that is for the form/subform method. - Larry

 

[misscrf]

I think I understand that when you select the combo, you want the next record to start with that selection. I am finding, though, that that action may not be enough to make the form want to update that as a new record. I wonder if you can put some code in the after update of the combo that starts the data record...

I would be something like

AfterUpdate etc....

docmd.refresh form.(forname?)

Either that or docmd.save to save the record...

Try those and see if either make the record save. You might have to put it in the combos onchange, but that could get messy. ie if the user picks a choice, but didnt mean to, you would have an extra record maybe.

Let me know if this helps any. If not, give me more of the table/form specifics. I will create a demo and post it for you to grab.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[newcoder54]

Here is a better way to express my problem. When a user selects the new record button, the focus goes to the UniqueLastName field. That is fine. Then the user picks the LastName which triggers the AfterUpdate. That should then populate some of the other boxed with address, title etc. That is where I am lost. I need something in the AfterUpdate that does something like Data_Table address = RowSouce_table address. Ya - I like that better that what I tried to word. Thanks

 

[misscrf]

I think I will best understand if you can relay me this:

table1:

PrimaryKey
Firstname
Lastname
Address1
Address2
City
State
Zip


Table2:
PrimaryKey
ForeignKeytoPrimaryKeyinTable1
Data1
Data2
etc.


Form: what you want from each table etc.

That will help me to visualize. Im sorry, I am such a visual person.
Thanks.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[newcoder54]

Hiya misscrf,

This all helps me also in the thought process. This is stuff I only do when I get “volunteered”.

User selects UniqueLastName in a combo box and then the rest of the form gets filled in. That persons first name, Title and address info will all populate. Selecting someone else in the combo box will run the AterUpdate again and do that all over again.

The code in AfterUpdate() “Me!RowSourceOffice = Me!Office” works but not the others. Part of what baffles me!

RowSourceTable

UniqueLastname PrimaryKey and ties to DataTable in a one to many relationship
RowSourceTitle
RowSourceOffice
RowSourcsFirstname
RowSourceAddress1
RowSourceAddress2
RowSourceCity
RowSourceState
RowSourceZip

DataTable

Counter PrimaryKey
UniqueLastname ForeignKeytoPrimaryKey in RowSourceTable
FirstName
Title
Office
Address1
Address2
City
State
Zip