autopopulate (new slant on older question) (1 Viewer)

[MSUKevin]

This question was posted below but some additions have been made by the client and so I need to ask again, (please bear with me)

I have a field in my customers table named: mc# This field is a unique nuber for each customer. What I want to set up is a combo box that:

(1) when the user starts to enter a customer's mc# into the combo box A97' will provide "Hits" that start to fill in the rest of the number. This is necessary to speed up the transaction and so the customer is not entered in more than once.

(2) If the customer has never been to the establishment the user will be able to update the combo box with the new mc# and the rest of the customers information.

(3) It Access does find the customer to be a repeat visitor, then after update the rest of the customers personal information will be retreived and autopopulated into the other field on the form.

Does anyone know an easy way to accomplish this? I'm pretty good with the basics (but not to advanced in the coding process) any and all help would be very appreciated.

Thanks in advance...
Kevin

 

[D-Fresh]

Well, for step 1, just set the Auto Expand property to yes.

For 2, Set the Limit to List Property to Yes. Then, there is an event, Not in List, where you can write your code to insert a new customer. Perhaps when a new customer is entered, add the mc# to your table, then popup another form to enter the customer information and save it.

For 3, You should use a subform on your original form. In the after Update event, set the subforms source to the SQL Statement "SELECT & FROM [Your_Table] WHERE MCno=" & me("Combobox")

This should take care of all your problems. Hope this helps.

Doug

[This message has been edited by D-Fresh (edited 06-12-2001).]

 

[MSUKevin]

D-Fresh,

Thanks for the reply but I still have questions:

For step two you say, "where you can write your code to inster a new customer..."

That would be great if I was any good at writing code. Any idea on how I can set up the code? Any help is appreciated.

Thanks again,
Kevin

 

[D-Fresh]

Alright, what you should do is on the Not in List event of the combo box, put the following code...

dim MyDB as database
dim MySQL as string

set MyDB = currentdb
MySQL = "INSERT INTO [Your_Table] (MCnoField) values (" & me("Combobox") & ")"

This will add the MC # to your database and then you can add the customer information below, which will be blank, since it retrieves the record(your step #3) and it will have no info yet. Let me know if you need any more help.

Doug

[This message has been edited by D-Fresh (edited 06-13-2001).]

[This message has been edited by D-Fresh (edited 06-13-2001).]

 

[MSUKevin]

D-Fresh,

This is the code I put in per your recommendation:

Private Sub Combo32_NotInList(NewData As String, Response As Integer)
Dim MyDB As Database
Dim MySQl As String

Set MyDB = CurrentDb
MySQl = "INSERT TO [Customers] (CustMC #) values (" & Me("Combo32") & ")"

When I saved and went back to form view I tried to starting entering the data from a record in the table and it gave me an error message that said:

The item you selected is not in the list; please select and item from the list.

I set the Auto Expand to yes and the limit to list to yes.

Do you have any ideas what I did wrong???

Your assistance on this matter is greatly appreciated.

Thanks,
Kevin

 

[D-Fresh]

Sorry about that.. 3 things.. First off the syntax is "INSERT INTO" not "INSERT TO" and also right after that statement, put in the following code:

MyDB.Execute(MySQL)
Response = acDataErrAdded

That should do it. Sorry for the confusion, forgot the little things! Hope that works for you.

Doug

 

[MSUKevin]

D-Fresh,

I appreciate your continued assistance on this but it's still not working for me. Here's what I have now for the code:

Private Sub Combo32_NotInList(NewData As String, Response As Integer)
Dim MyDB As Database
Dim MySQl As String

Set MyDB = CurrentDb
MySQl = "INSERT INTO [Customers] (CustMC #) VALUES (" & Me("Combo32") & ")"
MyDB.Execute (MySQl)
Response = acDataErrAdded

End Sub

I still get no response when I start to type in the Mc# into the field. I have autoexpand = yes and I have not in list = yes.

Any idea why this won't work? Could it possibly be something else wrong with me form? Please Help!!!!

Thanks in advance,
Kevin

 

[D-Fresh]

Okay, I don't think this is the problem, but change your SQL statement to this..

MySQl = "INSERT INTO [Customers] (CustMC #) VALUES (" & NewData & ")"

See if that does anything. Also, What error message are you getting? Check the underlying table, is the MC # in there?