A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Set Value Macro throwing automation error



 
 
Thread Tools Display Modes
  #1  
Old July 8th, 2009, 03:45 PM posted to microsoft.public.access
floyd33 via AccessMonster.com
external usenet poster
 
Posts: 38
Default Set Value Macro throwing automation error

I have a form with fields from two tables (tblAttendeeInformation and
tblMeetings2001). I am trying to put a macro on one field that will set the
value of two other fields (date fields). Here is part 1 of my macro:

Item [Forms]![frmAttendeeInformation]![Start Date]
Expression ([DLookUp]([tblMeetings2001]![Start Date]))

I have set the macro on the first field on the form which is Project Code,
After Update. When a project code is selected from the dropdown (selections
from tblMeetings2001.ProjectCode), I need the Start Date and End Date (second
part of the macro), to populate based on the dates stored in tblMeetings2001.
What am I doing wrong?

Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

  #2  
Old July 8th, 2009, 03:59 PM posted to microsoft.public.access
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Set Value Macro throwing automation error

Your syntax is incorrect for the DLookup function.

DLookup("[Start Date]", "tblMeetings2001")

This assumes that there's only a single row of data in tblMeetings2001. If
that's not the case, you're going to essentially get the value from a random
row in the table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"floyd33 via AccessMonster.com" u17874@uwe wrote in message
news:98c54f0ee5401@uwe...
I have a form with fields from two tables (tblAttendeeInformation and
tblMeetings2001). I am trying to put a macro on one field that will set
the
value of two other fields (date fields). Here is part 1 of my macro:

Item [Forms]![frmAttendeeInformation]![Start Date]
Expression ([DLookUp]([tblMeetings2001]![Start Date]))

I have set the macro on the first field on the form which is Project Code,
After Update. When a project code is selected from the dropdown
(selections
from tblMeetings2001.ProjectCode), I need the Start Date and End Date
(second
part of the macro), to populate based on the dates stored in
tblMeetings2001.
What am I doing wrong?

Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1



  #3  
Old July 8th, 2009, 04:21 PM posted to microsoft.public.access
floyd33 via AccessMonster.com
external usenet poster
 
Posts: 38
Default Set Value Macro throwing automation error

No there are many rows of data in TblMeetings2001, so I need to match it with
the row in tblAttendeeInformation. I have ProjectCodeID in
tblAttendeeInformation and and MeetingID in tblMeetings2001. I suppose I
need a where clause in that expression?

Thanks for your help!

Douglas J. Steele wrote:
Your syntax is incorrect for the DLookup function.

DLookup("[Start Date]", "tblMeetings2001")

This assumes that there's only a single row of data in tblMeetings2001. If
that's not the case, you're going to essentially get the value from a random
row in the table.

I have a form with fields from two tables (tblAttendeeInformation and
tblMeetings2001). I am trying to put a macro on one field that will set

[quoted text clipped - 14 lines]

Thanks!


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

  #4  
Old July 8th, 2009, 04:42 PM posted to microsoft.public.access
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Set Value Macro throwing automation error

Yes.

Is the ProjectCodeID of interest available through a control on the form?

DLookup("[Start Date]", "tblMeetings2001", "ProjectCodeID = " &
Forms!NameOfForm!NameOfControl)

or, if ProjectCodeID is text,

DLookup("[Start Date]", "tblMeetings2001", "ProjectCodeID = '" &
Forms!NameOfForm!NameOfControl & "'")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"floyd33 via AccessMonster.com" u17874@uwe wrote in message
news:98c59f1d4953a@uwe...
No there are many rows of data in TblMeetings2001, so I need to match it
with
the row in tblAttendeeInformation. I have ProjectCodeID in
tblAttendeeInformation and and MeetingID in tblMeetings2001. I suppose I
need a where clause in that expression?

Thanks for your help!

Douglas J. Steele wrote:
Your syntax is incorrect for the DLookup function.

DLookup("[Start Date]", "tblMeetings2001")

This assumes that there's only a single row of data in tblMeetings2001. If
that's not the case, you're going to essentially get the value from a
random
row in the table.

I have a form with fields from two tables (tblAttendeeInformation and
tblMeetings2001). I am trying to put a macro on one field that will set

[quoted text clipped - 14 lines]

Thanks!


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1



  #5  
Old July 8th, 2009, 06:48 PM posted to microsoft.public.access
floyd33 via AccessMonster.com
external usenet poster
 
Posts: 38
Default Set Value Macro throwing automation error

Okay, it appears I am still messed up. The field named ProjectCode pulls
from tblMeetings2001 in a combo box with the MeetingID hidden from the
dropdown. I placed the ProjectCodeID field, from tblAttendeeInformation, on
the form to refer to when needed. So this was the code I used, following
your example and it is still only grabbing the first record in
tblMeetings2001's dates, no matter which project code I select.

DLookUp("[Start Date]","tblMeetings2001","MeetingID = " & [Forms]!
[frmAttendeeInformation]![ProjectCodeID])

What am I missing here? It's got to be something obvious. Thanks again!


Douglas J. Steele wrote:
Yes.

Is the ProjectCodeID of interest available through a control on the form?

DLookup("[Start Date]", "tblMeetings2001", "ProjectCodeID = " &
Forms!NameOfForm!NameOfControl)

or, if ProjectCodeID is text,

DLookup("[Start Date]", "tblMeetings2001", "ProjectCodeID = '" &
Forms!NameOfForm!NameOfControl & "'")

No there are many rows of data in TblMeetings2001, so I need to match it
with

[quoted text clipped - 18 lines]

Thanks!


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

  #6  
Old July 8th, 2009, 07:00 PM posted to microsoft.public.access
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Set Value Macro throwing automation error

If you go to the Immediate Window (Ctrl-G), type

?[Forms]![frmAttendeeInformation]![ProjectCodeID]

and hit Enter, do you see the correct value?

If you then type

?DLookUp("[Start Date]","tblMeetings2001","MeetingID = n)

(replacing n with the value you got above) and hit Enter, do you see the
correct value?


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"floyd33 via AccessMonster.com" u17874@uwe wrote in message
news:98c6e72b9efb2@uwe...
Okay, it appears I am still messed up. The field named ProjectCode pulls
from tblMeetings2001 in a combo box with the MeetingID hidden from the
dropdown. I placed the ProjectCodeID field, from tblAttendeeInformation,
on
the form to refer to when needed. So this was the code I used, following
your example and it is still only grabbing the first record in
tblMeetings2001's dates, no matter which project code I select.

DLookUp("[Start Date]","tblMeetings2001","MeetingID = " & [Forms]!
[frmAttendeeInformation]![ProjectCodeID])

What am I missing here? It's got to be something obvious. Thanks again!


Douglas J. Steele wrote:
Yes.

Is the ProjectCodeID of interest available through a control on the form?

DLookup("[Start Date]", "tblMeetings2001", "ProjectCodeID = " &
Forms!NameOfForm!NameOfControl)

or, if ProjectCodeID is text,

DLookup("[Start Date]", "tblMeetings2001", "ProjectCodeID = '" &
Forms!NameOfForm!NameOfControl & "'")

No there are many rows of data in TblMeetings2001, so I need to match it
with

[quoted text clipped - 18 lines]

Thanks!


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:45 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.