Auto fill data

[buratti]

I was working on a database design several months ago and learned very much quickly at that time. For whatever reason back then I just stopped working on it when I was so close to finishing it. Today I started working on it again, but unfortunately I forgot mostly everything I learned back then and am stuck on one of many problems.

I have a customer charge detail form. The form is structured as follows: The main form has customer info like name, address, phone, and account number. The subform has charge detail like charge type, amount, charge date, etc.
Right now it is set up so you first enter the account number of the customer and tab over, it will auto fill the name, address, phone, etc. (that is if the account number already exists, and if not opens the customer form to enter a new customer). That works great and I don't need to change it.

My problem is I just forgot how or what it did to make it work like that. What I want to do now is take that same concept for the account number field, and add it to the name address fields also. So the end result would be: If I enter the customer name first and tab over, the account number, address, phone, etc would auto fill, or, if I enter the Address first the name and account number fill in and so on.

I tend to give more information than needed sometimes which makes the question confusing at times, but if anyone understands what I am trying to do could you please give me assistance on how to do this. Thanks for any help.

 

[clyons]

This is pretty much exactly what I need, hopefully someone has it.

 

[dcb]

I assume that you did something in the lines of

TextBox_AfterUpdate()
Me.SomeTextbox = DLookup("[CustomerName]", "Customers", "[CustomerID] =" & Forms![YourForm]!CustomerID)
ETC
End Sub

Why not have a look at your current code?

Basicaly The Idea is
Lookup each value for each required box - based on a ID - and fill it in .....
However!!
If you are filling in customer details in this way i can only assume that you are writing this to a table, unless there is a specific reason that you require a snapshot of this data I would relook the normalisation of your DB

 

[dcb]

The Northwind traders DB - I have to admit that I havent looked at it for years - had a great example of this on their orders form...
Note the usage was to lookup the current price when creating a order as this had to be written for each product to the orders table.... If this had not been done all the orders would change if you updated your current prices

Have a look - again forgive me if I have got something wrong - this is from memory

 

[clyons]

I did this a bit different, I selected the field I wanted then added a macro SearchForRecord and in the sql where added ="[Order Number] = " & "'" & [Screen].[ActiveControl] & "'"

 

[dcb]

I did this a bit different, I selected the field I wanted then added a macro SearchForRecord and in the sql where added ="[Order Number] = " & "'" & [Screen].[ActiveControl] & "'"

OK - Sorry have to bow out then - I don't do macro's.... just a personal thing

 

[buratti]

OK, so I kind of remembered how I configured the account number field to "autofill" other data on my field. When I created the form, I added the account number field as a combo box form control. From there was a wizard to with one option to "Find a record on my form based on the value I select in my combo box". I think that is how it is working now. But if I try to just add another combo box for the Name and Address fields like the one already created for the Account Number field, that same option is not available. There is nothing in VBA that is making it do whatever it is doing. I searched through every property option inside access and cannot figure out the settings to duplicate them.
However it is working for the account number field, I don't want to change that. Since I can’t figure out how to duplicate the account number field settings, is there any alternate way to add that functionality to the two other fields in my form?

 

[dcb]

Ah your not actually doing an autofill here you are looking up a record...

The reason that you are not getting the option is because with the Name and address fields you are not giving it a ID
Use AccountNumber, Name, Address and your option will return
Simply Hide the account number

 

[dcb]

There is another way to get where you are going without filling up your form

Have a look at this example attached - Gives you an example of how to manipulate a cmbBox