Auto Populate Unbound Text Box

[emilyebba]

Hi,

I am fairly new to programming and am trying to figure this one out. I have searched this forum at length and found another thread that had something similiar but I cant quite get it to work ( http://www.access-programmers.co.uk/forums/showthread.php?t=201644 ).

On my form when the user enters in the CertNum I would like an unbound text box to autopopulate with the Business Name associated with that CertNum.

txtbox_GetBusName - My unbound textbox that I would like to be autopopulated with BusName
CertNum - Where the user enters in a number
BusName - Business Name

This is what I have tried:

Private Sub txtbox_GetBusName_AfterUpdate()

Me.txtbox_GetBusName = DLookup("BusName", "[CertNum] = '" & Me.CertNum & "'")

End Sub

What am I doing wrong? It also occured to me to set the control source of the unbound text box to a query, but that didnt work also. What is the best and most efficient way to perform this task?

 

[nanscombe]

DLookup("FieldName" , "TableName" , "Criteria")

Have you just missed out the table name?

Private Sub CertNum_AfterUpdate()

Me.txtbox_GetBusName = DLookup("BusName", [B]yourTableName[/B], "[CertNum] = '" & Me.CertNum & "'")

End Sub

And shouldn't it be in the AfterUpdate (or LostFocus) event of the CertNum textbox, ie where they are entering the value to be looked up?

 

[emilyebba]

Hmmm. Still not working. I tried it in the AfterUpdate of the CertNum textbox and the LostFocus. I have two tables. My Customer table and my Payments table. I did:

Private Sub CertNum_LostFocus()

Me.txtbox_GetBusName = DLookup("BusName", tbl_MainCust, "[CertNum] = '" & Me.CertNum & "'")

End Sub

Is this the best way to perform this function of autopopulating a text box? Please advise. Thank you.

 

[John Big Booty]

If CertNum is a numeric field try;

Me.txtbox_GetBusName = DLookup("BusName", tbl_MainCust, "[CertNum] = " & Me.CertNum)

 

[emilyebba]

Hi John Big Booty...CertNum is alpha-numeric. Thanks.

 

[dansc0tt]

Couple of quick things to try:

1. Is [certnum] a text or numeric field in your table? If it is numeric then you don't need the "'" around me.certnum
2. try me.refresh or me.txtbox_GetBusName.requery at the end of your code to get the form to refresh or control to requery

HTH

Dan

 

[John Big Booty]

Try;

Me.txtbox_GetBusName = DLookup("BusName", [B][COLOR="Red"]"[/COLOR][/B]tbl_MainCust[B][COLOR="Red"]"[/COLOR][/B], "[CertNum] = '" & Me.CertNum & "'")

Sorry missed those missing double quotes

 

[emilyebba]

Still not working and this is what I entered:

Private Sub CertNum_AfterUpdate()

Me.txtbox_GetBusName = DLookup("BusName", "tbl_MainCust", "[CertNum] = & Me.CertNum")
Me.txtbox_GetBusName.Requery


End Sub

Thanks for helping!

 

[bob fitz]

I think it should be:

Me.txtbox_GetBusName = DLookup("BusName", "tbl_MainCust", "[CertNum] = " & Me.CertNum)

 

[emilyebba]

Hi, Still not working. I have attached a sample. Thanks for your help!

 

[John Big Booty]

The code you want is;

    Me.txtbox_GetBusName = DLookup("BusName", "tbl_MainCust", "[CertNum] = '" & Me.CertNum & "'")

You will also need to put it in the Form's On Current event

 

[nanscombe]

Me.txtbox_GetBusName = DLookup("BusName", "tblMainCust", "{CertNum] = " & Me.CertNum)

Three problems that I could see:

1) tblMainCust instead of tbl_MainCust
2) CertNum is alphanumeric, so requires single quotes around it
3) Curly bracket '{' instead of '[' before CertNum

Me.txtbox_GetBusName = DLookup("BusName", "tbl_MainCust", "[CertNum] = '" & Me.CertNum & "'")

 

[John Big Booty]

I think it should be:

Me.txtbox_GetBusName = DLookup("BusName", "tbl_MainCust", "[CertNum] = " & Me.CertNum)

Dude she says it's an Alpha Numeric (string) field back in post number 5 :banghead:

 

[emilyebba]

Yeah! It worked! You guys are great! Thanks! Amazing!

 

[bob fitz]

Dude she says it an Alpha Numeric (string) field back in post number 5 :banghead:

You are, of course, quite right. I should have read through the posts with more care.