running a query based on a record's ID from a form

I

iain

Hi,

I'm trying to get a query to run based on where a record's
Contact ID matches that of the current record in a form.

I've got working to run a report based on the contact ID,
ie bring up a report listing of developments for that of
the current contact, using the code :

Private Sub Command45_Click()
On Error GoTo Err_Command45_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Glasgow Developments by Contact"

stLinkCriteria = "[Contact ID]=" & Me![Contact ID]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_Command45_Click:
Exit Sub

Err_Command45_Click:
MsgBox Err.Description
Resume Exit_Command45_Click

End Sub

The code I currently have to try to do the same thing, but
running the query, is :

Private Sub Command48_Click()
On Error GoTo Err_Command48_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Glasgow Development by Contact Email"

stLinkCriteria = "[Contact ID]=" & Me![Contact ID]
DoCmd.OpenQuery stDocName, acNormal, acEdit, ,
stLinkCriteria

Exit_Command48_Click:
Exit Sub

Err_Command48_Click:
MsgBox Err.Description
Resume Exit_Command48_Click

End Sub

but am just getting the error message :

Compile Error :
Wrong number of arguments or invalid property assignment.

Any ideas on where to change the code to ge this to work?

Cheers,
Iain
 
P

Pavel Romashkin

I think that OpenQuery only accepts three arguments and you are trying
to pass 5 to it:
DoCmd.OpenQuery stDocName, acNormal, acEdit, ,
Click to expand...

Get rid of the last two commas. Besides, you are not about to get the
query filtered by the code you have so you might as well get rid of all
lines besides

stDocName = "Glasgow Development by Contact Email"
DoCmd.OpenQuery stDocName, acNormal, acEdit

If you need to limit the query to the ID you have selected in the form,
you can filter the existing query or create on in your code and display it.

HTH,
Pavel

Private Sub Command48_Click()
On Error GoTo Err_Command48_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Glasgow Development by Contact Email"

stLinkCriteria = "[Contact ID]=" & Me![Contact ID]
DoCmd.OpenQuery stDocName, acNormal, acEdit, ,
stLinkCriteria

Exit_Command48_Click:
Exit Sub

Err_Command48_Click:
MsgBox Err.Description
Resume Exit_Command48_Click

End Sub

but am just getting the error message :

Compile Error :
Wrong number of arguments or invalid property assignment.

Any ideas on where to change the code to ge this to work?

Cheers,
Iain
Click to expand...
 
I

iain

basically I have a form listing developments, which
includes a subform linked on development ID, so that the
subform pulls up contacts for each particular form.

included in the subform is a button to preview a report
listing the developments that fall under the currently
displayed contact.

it also includes a button to generate an email with an
excel attachment of those developments, but this is done
using a prompt in the query, so that the user has to
manually enter the contact ID. basically i'm just trying
to eliminate that step, so that the excel attachment is
automatically generated based on the developments for the
currently displayed contact.

if that makes sense?

Iain
-----Original Message-----
I think that OpenQuery only accepts three arguments and you are trying
to pass 5 to it:
DoCmd.OpenQuery stDocName, acNormal, acEdit, ,
Click to expand...

Get rid of the last two commas. Besides, you are not about to get the
query filtered by the code you have so you might as well get rid of all
lines besides

stDocName = "Glasgow Development by Contact Email"
DoCmd.OpenQuery stDocName, acNormal, acEdit

If you need to limit the query to the ID you have selected in the form,
you can filter the existing query or create on in your code and display it.

HTH,
Pavel

Private Sub Command48_Click()
On Error GoTo Err_Command48_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Glasgow Development by Contact Email"

stLinkCriteria = "[Contact ID]=" & Me![Contact ID]
DoCmd.OpenQuery stDocName, acNormal, acEdit, ,
stLinkCriteria

Exit_Command48_Click:
Exit Sub

Err_Command48_Click:
MsgBox Err.Description
Resume Exit_Command48_Click

End Sub

but am just getting the error message :

Compile Error :
Wrong number of arguments or invalid property assignment.

Any ideas on where to change the code to ge this to work?

Cheers,
Iain
Click to expand...
.
Click to expand...
 
I

iain

-----Original Message-----
basically I have a form listing developments, which
includes a subform linked on development ID, so that the
subform pulls up contacts for each particular form.
Click to expand...

that should have been each particular development at the
end there.

Iain
 
P

Pavel Romashkin

If all you need is the current [Contact ID] then you can simply add this
to the query definition in the first place:

SELECT * FROM MyTable WHERE [Contact ID] = Forms!MainForm![Contact ID]

and save this query ("Glasgow Development by Contact Email") - I guess
this is the name you used for this saved query. When this query will be
opened it will look up the [Contact ID] in the MAinForm, if it is open,
so make sure it is when you call the query.

Good luck,
Pavel

basically I have a form listing developments, which
includes a subform linked on development ID, so that the
subform pulls up contacts for each particular form.

included in the subform is a button to preview a report
listing the developments that fall under the currently
displayed contact.

it also includes a button to generate an email with an
excel attachment of those developments, but this is done
using a prompt in the query, so that the user has to
manually enter the contact ID. basically i'm just trying
to eliminate that step, so that the excel attachment is
automatically generated based on the developments for the
currently displayed contact.

if that makes sense?

Iain
-----Original Message-----
I think that OpenQuery only accepts three arguments and you are trying
to pass 5 to it:
DoCmd.OpenQuery stDocName, acNormal, acEdit, ,
Click to expand...

Get rid of the last two commas. Besides, you are not about to get the
query filtered by the code you have so you might as well get rid of all
lines besides

stDocName = "Glasgow Development by Contact Email"
DoCmd.OpenQuery stDocName, acNormal, acEdit

If you need to limit the query to the ID you have selected in the form,
you can filter the existing query or create on in your code and display it.

HTH,
Pavel

Private Sub Command48_Click()
On Error GoTo Err_Command48_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Glasgow Development by Contact Email"

stLinkCriteria = "[Contact ID]=" & Me![Contact ID]
DoCmd.OpenQuery stDocName, acNormal, acEdit, ,
stLinkCriteria

Exit_Command48_Click:
Exit Sub

Err_Command48_Click:
MsgBox Err.Description
Resume Exit_Command48_Click

End Sub

but am just getting the error message :

Compile Error :
Wrong number of arguments or invalid property assignment.

Any ideas on where to change the code to ge this to work?

Cheers,
Iain
Click to expand...
.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I receive an error message Type mismatch 3
If Statements 4
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Criteria when opening form 1
Open form "Client Details" by clicking "CientName" txtBox 3
Run time error 2494 1
A2000 form ceases working under A2002 1
transfer a field using button 1

Top