Solved Help! Simple Dlookup in unbound form field not working

[slharman1]

I can't believe I am stuck on this one. I have a form with a simple Dlookup in it to populate an unbound txtBox on a form. Just can't get it to display.
See attached for database:

1. User name: orlandoc
2. Password: 3333
Job Name is not populating with the Dlookup.
Please help!
Thanks

 

[Gasman]

Why not show your code, rather than asking people to download the database?
That would be my first step.

 

[slharman1]

Why not show your code, rather than asking people to download the database?
That would be my first step.

=DLookUp("JobName","qryItemListByOrderCurrent","[WOItems]=" & [cboShopItemID]) is written in the unbound txt box, access keeps putting brackets around the control named cboShopItemID (access 2016)

 

[Minty]

Can you not bring the JobName into the combo as a hidden column and simply refer to that?

= cboShopItemID.Column(2)

(Column numbering starts at 0 so that (2) is the third column in the combo's underlying rowsource

 

[mike60smart]

I can't believe I am stuck on this one. I have a form with a simple Dlookup in it to populate an unbound txtBox on a form. Just can't get it to display.
See attached for database:

1. User name: orlandoc
2. Password: 3333
Job Name is not populating with the Dlookup.
Please help!
Thanks

Hi Change the name of the Work Item Combobox to "cboWorkItem"

Then use the following string as the DLookup:- =DLookUp("JobName","qryItemListByOrderCurrent","[WOItems]='" & [cboShopItem] & "'")

 

[slharman1]

Why not show your code, rather than asking people to download the database?
That would be my first step.

Damn Gasman, You got me again! It's the damn contactenation! (but you probably knew that already), I've got to learn how strings are done.
Working now. Thanks

 

[slharman1]

Thanks all, I've got to learn string concatenation.

 

[theDBguy]

Thanks all, I've got to learn string concatenation.

Will this help?

How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statemen...

thedbguy.blogspot.com

 

[Gasman]

Hi Change the name of the Work Item Combobox to "cboWorkItem"

Then use the following string as the DLookup:- =DLookUp("JobName","qryItemListByOrderCurrent","[WOItems]='" & [cboShopItem] & "'")

Well I'd certainly pass on that suggestion?
You advise to change the name of the combo, then do not use it in the DlookUp?

 

[Gasman]

Damn Gasman, You got me again! It's the damn contactenation! (but you probably knew that already), I've got to learn how strings are done.
Working now. Thanks

TBH, that code looks fine to me as long as the cboShopItemID is numeric (as anything with ID in it, would be in my DB ) and is the actual name?

Minty's suggestion is even better, no need for the DLookup in the first place?

 

[mike60smart]

Well I'd certainly pass on that suggestion?
You advise to change the name of the combo, then do not use it in the DlookUp?

Sorry my error the change name to should be:- "cboShopItem"

 

[Gasman]

Sorry my error the change name to should be:- "cboShopItem"

I am still unsure why you think a change of name is required?
I cannot see that being a reserved word?

 

[mike60smart]

I am still unsure why you think a change of name is required?
I cannot see that being a reserved word?

Hi Gasman

The op is using this DLookup:- =DLookUp("JobName","qryItemListByOrderCurrent","[WOItems]=" & [cboShopItemID])

The Combobox Column 0 is in fact a string and not a Primary Key.

So I just changed the name of the Combobox and then used it in the DLookup as shown:

=DLookUp("JobName","qryItemListByOrderCurrent","[WOItems]='" & [cboShopItem] & "'")

Personally I would use Minty's method as it is far easier and neater.

 

[Gasman]

OK,
I did not download the DB, as now OP said 2016, I would not be able to open it anyway
However to me, changing the name is a red herring?, just point out that they needed single quotes surrounding the control name, as it was text?
Change of name really does nothing to fix the problem?

@slharman1 You need to be consistent in your naming convention, otherwise this is going to catch you out time and time again.
For that reason, anything with ID (in caps) in any DB of mine is a key and always numeric autonumber key.
That saved me a lot of thought and heartache.

 

[mike60smart]

Hi Gasman

Points noted Thanks

 

[slharman1]

Well I'd certainly pass on that suggestion?
You advise to change the name of the combo, then do not use it in the DlookUp?

The Work Item is not a combo box anyway. It is the name of the field in the qry I am performing the dlookup on.

 

[slharman1]

Can you not bring the JobName into the combo as a hidden column and simply refer to that?

= cboShopItemID.Column(2)

(Column numbering starts at 0 so that (2) is the third column in the combo's underlying rowsource

I like this idea as well, I wonder which way is more efficient?

 

[slharman1]

Will this help?

How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statemen...

thedbguy.blogspot.com

Absolutely, I keep forgetting about your blog. Thank You

 

[Gasman]

I like this idea as well, I wonder which way is more efficient?

That one, as no extra DLookUp involved ?

 

[slharman1]

Ok Guys, I guess I still don't get it. What the hell is wrong with this code!
Quote number is a field in the tblQuotes
I've tried it with brackets around fields as well, I just am not letting the proper syntax into my brain I guess.
Microsoft website is exactly like my code.

    Dim quoteInput As Variant, quoteno As Variant
    quoteInput = InputBox("Enter quote number for new order:", "New Order From Quote")
    quoteno = DLookup("QuoteNumber", "tblQuotes", "QuoteNumber =" & quoteInput)
    If quoteno <> quoteInput Then
        MsgBox quoteInput & " is not a valid quote number"
        Exit Function
    End If