A Cry for Help (about Autopopulate)... (1 Viewer)

[MSUKevin]

Good morning all,

If someone could help me out on this I would really appreciate it. I have posted this question a few times in the past and many people have jumped in to help but unfortunitly non of the help I have recieved in the past has worked. Anyway, enough with my begging, here goes...

I have a form with a linked subform. The main form has customer info. The subform has transaction information. The two forms are linked by an autonumber [CustID] field. Each customer will have a unique ID named [mc#]. I want to be able to set up a combo box that lists the mc#.

1) If the [mc#] is listed when the user starts to type the person's identifier in the combo box, (i.e. the customer has been here before) I want that persons info to autopopulate into the form by either tabbing out of the box or clicking out.

2) If the [mc#] is not listed when the user types it into the box, (i.e. the customer is new), I want the user to be able to fill in the information in the fields.

Now, I have tried to do this many times with no luck. I have built notinlist funtions reccommended by members of this board, Microsoft support, and I even called a Microsoft tech that cost me big $$$. I'm sure the real problem lies in the fact that I am not a programmer nor never claimed to be. I am a student who thinks Access is really cool and I started to dable with it, next thing I know I'm being asked to build stuff I can't do...

If some of you good people would be interested in helping me out I would be forever in your debt. I am also willing to send out the database (its not big) to anyone who would like to take a look at it. I have to have this done by tomarrow and if I can't get it to work I'll just leave it as is....

Thanks for your time and consideration, and I'm sorry this was so long

(p.s. using A'97)

Sincerely,
Kevin

[This message has been edited by MSUKevin (edited 07-31-2001).]

 

[charityg]

I've done this many times. By all means, send me the database and I'll take a look at it.

 

[MSUKevin]

I really appreciate it Charityg,

I'm sending over the db now....

Again, thanks
Kevin

 

[MSUKevin]

Charity,

I case my last e-mail did not reach you, (because of e-mail problems), I just want to thank you again for your help with my db. Taking the time out of your day to help me was great and I really appreciate it...

Again... thanks,
Kevin

 

[foolishone]

MSUKevin,
can you post the answer for this ,I have a need for the same input on member forms
thanx ken

 

[MSUKevin]

foolishone,

Not a problem, I will gladly post the answer that Charityg created for me. (I hope this works as well for you as it did for me

)

Private Sub custmcID_AfterUpdate()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("customers", dbOpenDynaset)
rst.FindFirst "custmcID='" & Me!custmcID & "'"
If rst.NoMatch Then
Exit Sub
Else
With rst
Me!CustFirst = !CustFirst
Me!CustLast = !CustLast
Me!CustAddress = !CustAddress
Me!CustCity = !CustCity
Me!CustState = !CustState
Me![custphone number] = ![custphone number]
Me![custname of vessel] = ![custname of vessel]
End With
[transaction subform].SetFocus
Forms!customers![transaction subform]!gasoline_Sale.SetFocus
End If
End Sub


Obviously you will need to change the names to you situation. For your reference:

custmcID = the unique identifier for each customer
customers = table
transaction form = subform

p.s. the last two lines of code are to set the focus to my subform after the autopopulate... If you don't need to do this as well, just remove them.

hth,
Kevin



[This message has been edited by MSUKevin (edited 07-31-2001).]