Auto populate customer information from text box with account number

[carangos]

Hello,

I am creating a database to log and track issues and I'm trying to auto populate the customer's information (Name, ID, Account #, Product) after the user enters the account number in a text box.

I have created a separate table with the customer's infomation, and I need to store those values in the Log table along with the rest of the information captured in the form.

I have been working on this database for over a month ago, this is my first access project and every day I discover I could have done things different (better) but It's a learning curve. I have used your forum as my primary resource. (Thank you for all the help so far!!!!)



Attached is the database, in the form I am using to capture frm_Combo as my attempt to accomplish what I want, is a textbox with a macro button to search on the tbl_customers and populate the CISIDCapture, AccountCapture, FNameCapture, LNameCapture text boxes, maybe even the cboProduct combo box, all these values need to be saved in the Log table as a new issue everytime the Add Record button is clicked.

Please let me know if you see something else than can be improved.

Thanks,

CA

[June7]

Why do you need to duplicate the customer info (Name, Account#) into Log table? Why not just save the customer ID?

What is tbl_Issues for?

Advise to avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be AccountNum, Account_Num, Account_No
Also avoid reserved words as names (Name is a reserved word) - however, I see in the db you have not used Name as fieldname.

[carangos]

Hi June7,

As I explained before I am really new in access, the tbl_Issues is my way of connecting the issues with the owner for the drop down lists.

Thanks,

[June7]

I don't understand data structure. The owners own what?

Regardless of my inadequate understanding of your business process, if you want to create a record in another table with data on a form, that requires code. I use only VBA, something like:

CurrentDb.Execute "INSERT INTO Log(LastName, FirstName, ID, AccountNum, Product) VALUES('" & Me.LastName & "', '" & Me.FirstName & "', " & Me.ID & ", '" & Me.AccountNum & "', " & Me.Product & ")"

[carangos]

Hello-
Every issue listed in the tbl_Issues has a unique OWNER, the log table stores the information captured in the form, the frm_combo (Capture form) "reads" from all the other tables in its combo boxes. My idea is to have the user entering the account number in a text box, query from the tbl_Cutomers and populate the customer's information if found, then use that queried data and store it with the other captured info in the Log Table.

Thanks,

[June7]

Here is one method to 'find' customer record: http://www.datapigtechnologies.com/f...tomfilter.html

Why is form not bound to Log table?

Maybe you should be using a form/subform arrangement.