populate related fields based on a combo box 2

[ray436]

I have a pull-down combo box where the user selects a client's name and I would like the client's address, account number, etc fields to be populated based on the selection. I have the two tables, set a relationship, based the first on a list, etc , the combo list works but the other fields don't get updated with the corresponding info........

I have done this before, unfortunately that database is no longer available to me.........

Any help greatly appreciated!!!!

 

[Sarah28]

If you are using Access 2000 this should work.
Place this code in the After Update of your combo box.
Replace Fields in CAPITALS with your Values
Here goes:

Dim strFilter As String


'(Evaluate filter before it's passed to DLookup function.)
strFilter = "CLIENTTABLENAME = " & Me!NAMEOFCLIENTFIELDONFORM

'(Look up fields and assign it to proper control boxes.You will need this line for each value you assign)
Me!NAMEOFADDRESSFIELDONFORM = DLookup("NAMEOFADDRESSFIELDINTABLE", "NAMEOFTABLEITISIN", strFilter)

Hope this is clear- If it is confusing e-mail me and I will clear it up.

Thanks Sarah

 

[ray436]

Thanks Sarah,

forgot to say Office97, but I think it will work.
I do something similar to assign values to the "machine-operator" field for record generation tracking.

will let you know how it goes.....

thanks
Ray

 

[ray436]

tried to implement, but no end of grief

one reason is due to a field on my Db that combines one of the address fields for a letter merge, ( that I could get around, ) but mainly it tells me that I cannot use the combo box since the field is based on an expression. ( the Code I guess )

Oh well, maybe a query would do it ?

 

[OverDrive]

Could anyone step me through this process . . . I have been wanting to do this for while, but am not sure on the process of getting it done?

Thanks
Chance~

 

[ghubbell]

Here's an example that I think will work for everyone:

Toss a combobox on your form (use the wizard its faster...) and base it off of the same table or query as the form it sits on. Do Not have it bound to any field. Lets call it "CboSearch". In its "after update" event in VB, paste this in and adjust to your needs:

On Error GoTo FASerr
Me![LetsSayTheCustomerNumberFieldsName].SetFocus
DoCmd.FindRecord Me.CboSearch, acEntire, False, acDown, False, acCurrent

FASexit:
Exit Sub

FASerr:
MsgBox Err.Description, vbInformation, "Search error."
Resume FASexit

In this example CboSearch contains in its "bound column" the customer number. Move the focus to the customer number field on your form, then find the number that matches the combo. The rest of your data will happen along as well. Enjoy!

Gord
ghubbell@total.net

 

[OverDrive]

How in the hell do you know so much?

Thanks man . . .
Chance~

 

[OverDrive]

Ok, I have the unbound combobox created (named it cbosearch like u said). To make it simple, lets say I have in my table 3 fields "ID","name", and "shop". I would like to have the combobox work so once I input or choose a name, it would automatically find the shop and load it.

I did just like your example said, but I got lost at this part

"Move the focus to the customer number field on your form, then find the number that matches the combo. The rest of your data will happen along as well."

This is what I have . . .

Private Sub Combo8_AfterUpdate()
On Error GoTo FASerr
Me![Name].SetFocus
DoCmd.FindRecord Me.cbosearch, acEntire, False, acDown, False, acCurrent

FASexit:
Exit Sub

FASerr:
MsgBox Err.Description, vbInformation, "Search error."
Resume FASexit

End Sub


thanks again for the help man...

 

[ghubbell]

During the winter,there's lots of chilly days and nights up here...Get in lotttts of reading....amongst other things! Can't build igloos and hunt beavers all the time. (scratch that last part.)
Chance you're oh so close! The bound column of "CboSearch" must be ID? (Check "Bound Column&quot.

If the bound colums is the ID, you have to search the ID field in the form so:
Me![NameOfIDField].SetFocus

Give that a try while I feed the sled dogs....

Gord
ghubbell@total.net

 

[LarsonK]

I'm trying to populate a text field based on a combo box. I tried using the suggestion of Sarah28

This is my code:

Dim strFilter As String
strFilter = "BuildingNo =" & Me!cmbBuildingNo
Me!txtBuilding = DLookup("BuildingName", "tblBuilding", strFilter)

this is my error
Run-time error '3075':
Syntax error (missing operator) in query expression 'BuildingNo=002A'.

Please help any suggestion would be wonderful.

 

[ghubbell]

Slippery little devils:
strFilter = "BuildingNo = " & Me!cmbBuildingNo

needs to be:

strFilter = "BuildingNo = '" & Me!cmbBuildingNo & "'"

'cause its a string!



Gord
ghubbell@total.net