Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-05-2018, 10:44 AM   #1
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 444
Thanks: 106
Thanked 1 Time in 1 Post
murray83 is on a distinguished road
dlookup from combox selection

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
Attached Files
File Type: accdb Colleague Birthday Countdown.accdb (668.0 KB, 25 views)

murray83 is offline   Reply With Quote
Old 03-05-2018, 10:51 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,369
Thanks: 0
Thanked 742 Times in 727 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: dlookup from combox selection

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

Quote:
Originally Posted by murray83 View Post
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
Ranman256 is offline   Reply With Quote
Old 03-05-2018, 10:52 AM   #3
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,974
Thanks: 114
Thanked 3,002 Times in 2,729 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: dlookup from combox selection

Quote:
Originally Posted by murray83 View Post
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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 03-05-2018, 10:57 AM   #4
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 444
Thanks: 106
Thanked 1 Time in 1 Post
murray83 is on a distinguished road
Re: dlookup from combox selection

sorry its on the form frm_Edit

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

Last edited by murray83; 03-05-2018 at 11:28 AM.
murray83 is offline   Reply With Quote
Old 03-05-2018, 12:27 PM   #5
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 444
Thanks: 106
Thanked 1 Time in 1 Post
murray83 is on a distinguished road
Re: dlookup from combox selection

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 ??
murray83 is offline   Reply With Quote
Old 03-05-2018, 01:03 PM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,974
Thanks: 114
Thanked 3,002 Times in 2,729 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: dlookup from combox selection

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 03-05-2018, 01:13 PM   #7
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 444
Thanks: 106
Thanked 1 Time in 1 Post
murray83 is on a distinguished road
Re: dlookup from combox selection

Quote:
Originally Posted by ridders View Post
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

murray83 is offline   Reply With Quote
Old 03-05-2018, 01:19 PM   #8
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,974
Thanks: 114
Thanked 3,002 Times in 2,729 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: dlookup from combox selection

You're welcome

In answer to your earlier question, you don't need .Value as that's the default
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
murray83 (03-05-2018)
Old 03-05-2018, 04:02 PM   #9
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,974
Thanks: 114
Thanked 3,002 Times in 2,729 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: dlookup from combox selection

WHEN is a reserved word. WHO appears to be ok.

http://allenbrowne.com/AppIssueBadWord.html

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Reply

Tags
combo box , dlookup

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dlookup from a combo box selection rplohocky Queries 7 07-04-2017 10:03 AM
Run query from Combox Selection Sharon Borland Queries 9 06-25-2013 05:45 AM
Combox value selection ramindya Queries 1 03-02-2012 10:52 AM
Filtering a combobox based on another combox selection dataheadache Forms 2 02-24-2004 11:40 PM
Using combox selection to generate a score MICUDOC Forms 1 05-20-2003 06:30 PM




All times are GMT -8. The time now is 02:27 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World