dlookup from combox selection (1 Viewer)

murray83

Games Collector
Local time
Today, 08:05
Joined
Mar 31, 2017
Messages
728
Code:
Private Sub ComboWho_AfterUpdate()

Me.txtWhen = DLookup("[When]", "[tbl_data]", "[Who] = [ComboWho] ")

End Sub

works as in doesn't put up a fault. But then it doesn't put in the data required which should be the date in the txtwhen text box

please help i'm sure ive prob put one of these " in wrong place but not to sure

attached is example of db

ta
 

Attachments

  • Colleague Birthday Countdown.accdb
    668 KB · Views: 57

Ranman256

Well-known member
Local time
Today, 04:05
Joined
Apr 9, 2015
Messages
4,339
you dont have to do the Dlookup.
instead make the combo query pull 2 columns. (you can hide col 2 )
the combo gets 1 and another field in col 2.

when user picks the combo ,fill a text box with it.
(why do 2 Dlookups?)

NOTE: in vb column# begin with zero, so the actual col2 is column(1) in vb.
Code:
sub cboBox_afterupdate()
txtbox = cboBox.column(1)
end sub


Code:
Private Sub ComboWho_AfterUpdate()

Me.txtWhen = DLookup("[When]", "[tbl_data]", "[Who] = [ComboWho] ")

End Sub

works as in doesn't put up a fault. But then it doesn't put in the data required which should be the date in the txtwhen text box

please help i'm sure ive prob put one of these " in wrong place but not to sure

attached is example of db

ta
 

isladogs

MVP / VIP
Local time
Today, 08:05
Joined
Jan 14, 2017
Messages
18,186
Code:
Private Sub ComboWho_AfterUpdate()

Me.txtWhen = DLookup("[When]", "[tbl_data]", "[Who] = [ComboWho] ")

End Sub

works as in doesn't put up a fault. But then it doesn't put in the data required which should be the date in the txtwhen text box

please help i'm sure ive prob put one of these " in wrong place but not to sure

attached is example of db

ta

No idea where to find that code in your database
However you need to use appropriate delimiters in the criteria section

E.g.
Code:
"[Who]='" & me.combowho & "'"
That's if it's a text string
 

murray83

Games Collector
Local time
Today, 08:05
Joined
Mar 31, 2017
Messages
728
sorry its on the form frm_Edit

and the data i'm trying to get is a date so not text
 
Last edited:

murray83

Games Collector
Local time
Today, 08:05
Joined
Mar 31, 2017
Messages
728
thanks for that but, it works ie no error msg but the textbox is blank am i being daft and missing something obvious here is the new code

Code:
Private Sub ComboWho_AfterUpdate()

txtWhen = DLookup("[When]", "[tbl_data]", "[Who]='" & Me.ComboWho & "'")

End Sub

should i put .value after txtWhen ??
 

isladogs

MVP / VIP
Local time
Today, 08:05
Joined
Jan 14, 2017
Messages
18,186
Your combobox has 2 columns with ID as the first bound column and Who as the visible 2nd column

So you have two equally valid ways of doing this:

1. Use the bound column and NUMBER delimiters
Code:
Me.txtWhen = DLookup("[When]", "[tbl_data]", "[ID] = " & Me.[ComboWho])

2. Use the visible column & TEXT delimiters
Code:
Me.txtWhen = DLookup("[When]", "[tbl_data]", "[Who] = '" & Me.[ComboWho].Column(1) & "'")

The delimiters used depend on the datatype of the field used in the filter criteria
The fact that When is a date field is irrelevant

BTW - I've no idea if Who / When are Access reserved words but it wouldn't surprise me. Suggest you change the field names
 

murray83

Games Collector
Local time
Today, 08:05
Joined
Mar 31, 2017
Messages
728
Your combobox has 2 columns with ID as the first bound column and Who as the visible 2nd column

So you have two equally valid ways of doing this:

1. Use the bound column and NUMBER delimiters
Code:
Me.txtWhen = DLookup("[When]", "[tbl_data]", "[ID] = " & Me.[ComboWho])

worked a charm many thanks, and i shall look at those words but access didn't complain like it usually would if it was reserved
 

isladogs

MVP / VIP
Local time
Today, 08:05
Joined
Jan 14, 2017
Messages
18,186
You're welcome

In answer to your earlier question, you don't need .Value as that's the default
 

Users who are viewing this thread

Top Bottom