Auto Populate Unbound Text Box

emilyebba

Registered User.
Local time
Today, 14:19
Joined
Sep 26, 2012
Messages
76
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?
 
DLookup("FieldName" , "TableName" , "Criteria")

Have you just missed out the table name?

Code:
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?
 
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.
 
If CertNum is a numeric field try;

Code:
Me.txtbox_GetBusName = DLookup("BusName", tbl_MainCust, "[CertNum] = " & Me.CertNum)
 
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
 
Try;
Code:
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 :o
 
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!
 
I think it should be:

Me.txtbox_GetBusName = DLookup("BusName", "tbl_MainCust", "[CertNum] = " & Me.CertNum)
 
Hi, Still not working. I have attached a sample. Thanks for your help!
 

Attachments

The code you want is;
Code:
    Me.txtbox_GetBusName = DLookup("BusName", "tbl_MainCust", "[CertNum] = '" & Me.CertNum & "'")

You will also need to put it in the Form's On Current event
 
Code:
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

Code:
Me.txtbox_GetBusName = DLookup("BusName", "tbl_MainCust", "[CertNum] = '" & Me.CertNum & "'")
 
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:
 
Last edited:

Users who are viewing this thread

Back
Top Bottom