SQL Query

T

thefonz37

I'm trying to get a set of data based on an option that the user selects on a
form (i.e., they select one of the options and then click and button, then
this code executes). Here's what I have so far:

Private Sub Command15_Click()

Dim printedRecords As String
Dim archivedQuery As String

If SelectPrint = 1 Then
DoCmd.OpenReport "rpt_All_Archived", acViewPreview
printedRecords = "SELECT tbl_Archived_Requests.ID,
tbl_Archived_Requests.[Printed?] FROM tbl_Archived_Requests INNER JOIN
qry_Print_All_Archived ON tbl_Archived_Requests.ID =
qry_Print_All_Archived.ID;"
Else
DoCmd.OpenReport "rpt_Selected_Archived", acViewPreview
printedRecords = "SELECT tbl_Archived_Requests.ID,
tbl_Archived_Requests.[Printed?] FROM tbl_Archived_Requests INNER JOIN
qry_Print_Selected_Archived ON tbl_Archived_Requests.ID =
qry_Print_Selected_Archived.ID;"
End If

Debug.Print printedRecords

Dim outRecords As DAO.Recordset

Set outRecords = CurrentDb().OpenRecordset(printedRecords)

The last line is giving me an error - "Run-time error: 3061. Too few
parameters. Expected: 3"

This is confusing because I use the OpenRecordset(<query string>) used in
another place in the database and it's working fine. So I guess I'm not sure
why the function is requesting 3 parameters here and not elsewhere.
 
M

Michel Walsh

CurrentDb does not automatically solve the references like
FORMS!formName!ControlName while DoCmd and the user interface does. I guess
that qry_Print_All_xxx use that syntax.

Do you need to open the recordset? if you can specify the string as
RecordSource of a form or of report, the automatic resolution will be just
fine.

Vanderghast, Access MVP
 
T

thefonz37

The qry_Print_XXXX items are actually queries - not sure if that makes a
difference.

What I was planning on doing is opening a report based on user input (the
choices are "new" records and records that have already been printed), then
opening a recordset for the records that show up in that report and setting
the "Printed?" column in that recordset to true if it's false.

Is there a better way to do this?

Michel Walsh said:
CurrentDb does not automatically solve the references like
FORMS!formName!ControlName while DoCmd and the user interface does. I guess
that qry_Print_All_xxx use that syntax.

Do you need to open the recordset? if you can specify the string as
RecordSource of a form or of report, the automatic resolution will be just
fine.

Vanderghast, Access MVP


thefonz37 said:
I'm trying to get a set of data based on an option that the user selects
on a
form (i.e., they select one of the options and then click and button, then
this code executes). Here's what I have so far:

Private Sub Command15_Click()

Dim printedRecords As String
Dim archivedQuery As String

If SelectPrint = 1 Then
DoCmd.OpenReport "rpt_All_Archived", acViewPreview
printedRecords = "SELECT tbl_Archived_Requests.ID,
tbl_Archived_Requests.[Printed?] FROM tbl_Archived_Requests INNER JOIN
qry_Print_All_Archived ON tbl_Archived_Requests.ID =
qry_Print_All_Archived.ID;"
Else
DoCmd.OpenReport "rpt_Selected_Archived", acViewPreview
printedRecords = "SELECT tbl_Archived_Requests.ID,
tbl_Archived_Requests.[Printed?] FROM tbl_Archived_Requests INNER JOIN
qry_Print_Selected_Archived ON tbl_Archived_Requests.ID =
qry_Print_Selected_Archived.ID;"
End If

Debug.Print printedRecords

Dim outRecords As DAO.Recordset

Set outRecords = CurrentDb().OpenRecordset(printedRecords)

The last line is giving me an error - "Run-time error: 3061. Too few
parameters. Expected: 3"

This is confusing because I use the OpenRecordset(<query string>) used in
another place in the database and it's working fine. So I guess I'm not
sure
why the function is requesting 3 parameters here and not elsewhere.
Click to expand...
Click to expand...
 
M

Michel Walsh

Exactly the case, your qry_xxx query probably uses the said syntax:
FORMS!formName!ControlName.



You can update the table without opening a recordset at all, with a


DoCmd.RunSQL "UPDATE ... SET [Printed?]= true WHERE [Printed?] = false"



where the ... is to be replace by the appropriate join. You may also have to
specify the table name in front of the field [Printed?] like:
tableName.[Printed?]

That also assumes your join leave the query updateable. You can test it in
the query designer first.



Vanderghast, Access MVP





thefonz37 said:
The qry_Print_XXXX items are actually queries - not sure if that makes a
difference.

What I was planning on doing is opening a report based on user input (the
choices are "new" records and records that have already been printed),
then
opening a recordset for the records that show up in that report and
setting
the "Printed?" column in that recordset to true if it's false.

Is there a better way to do this?

Michel Walsh said:
CurrentDb does not automatically solve the references like
FORMS!formName!ControlName while DoCmd and the user interface does. I
guess
that qry_Print_All_xxx use that syntax.

Do you need to open the recordset? if you can specify the string as
RecordSource of a form or of report, the automatic resolution will be
just
fine.

Vanderghast, Access MVP


thefonz37 said:
I'm trying to get a set of data based on an option that the user
selects
on a
form (i.e., they select one of the options and then click and button,
then
this code executes). Here's what I have so far:

Private Sub Command15_Click()

Dim printedRecords As String
Dim archivedQuery As String

If SelectPrint = 1 Then
DoCmd.OpenReport "rpt_All_Archived", acViewPreview
printedRecords = "SELECT tbl_Archived_Requests.ID,
tbl_Archived_Requests.[Printed?] FROM tbl_Archived_Requests INNER JOIN
qry_Print_All_Archived ON tbl_Archived_Requests.ID =
qry_Print_All_Archived.ID;"
Else
DoCmd.OpenReport "rpt_Selected_Archived", acViewPreview
printedRecords = "SELECT tbl_Archived_Requests.ID,
tbl_Archived_Requests.[Printed?] FROM tbl_Archived_Requests INNER JOIN
qry_Print_Selected_Archived ON tbl_Archived_Requests.ID =
qry_Print_Selected_Archived.ID;"
End If

Debug.Print printedRecords

Dim outRecords As DAO.Recordset

Set outRecords = CurrentDb().OpenRecordset(printedRecords)

The last line is giving me an error - "Run-time error: 3061. Too few
parameters. Expected: 3"

This is confusing because I use the OpenRecordset(<query string>) used
in
another place in the database and it's working fine. So I guess I'm
not
sure
why the function is requesting 3 parameters here and not elsewhere.
Click to expand...
Click to expand...
Click to expand...
 
T

thefonz37

Actually, scratch the last question - the relationship is never going to be
one-to-one, so the query isn't going to be updateable.

Any other possible workarounds?

I toyed with the idea of having the query create a temp table then reading
from that, but I'm concerned that will make it messy when I have to copy the
temp data back to the original table because I will have to delete the
original records in the original table as well.

thefonz37 said:
Oh, ok - you're absolutely right, then.

I knew the query wasn't updateable, though, which is why I was trying to do
it by creating a recordset variable to do the heavy lifting for me. To be
honest, I'm not exactly sure why the query isn't updateable - I assumed it
had to do with an indeterminate relationship somewhere between the tables
it's based on. Would better defining the keys in the tables help?

Michel Walsh said:
Exactly the case, your qry_xxx query probably uses the said syntax:
FORMS!formName!ControlName.



You can update the table without opening a recordset at all, with a


DoCmd.RunSQL "UPDATE ... SET [Printed?]= true WHERE [Printed?] = false"



where the ... is to be replace by the appropriate join. You may also have to
specify the table name in front of the field [Printed?] like:
tableName.[Printed?]

That also assumes your join leave the query updateable. You can test it in
the query designer first.



Vanderghast, Access MVP





thefonz37 said:
The qry_Print_XXXX items are actually queries - not sure if that makes a
difference.

What I was planning on doing is opening a report based on user input (the
choices are "new" records and records that have already been printed),
then
opening a recordset for the records that show up in that report and
setting
the "Printed?" column in that recordset to true if it's false.

Is there a better way to do this?

:

CurrentDb does not automatically solve the references like
FORMS!formName!ControlName while DoCmd and the user interface does. I
guess
that qry_Print_All_xxx use that syntax.

Do you need to open the recordset? if you can specify the string as
RecordSource of a form or of report, the automatic resolution will be
just
fine.

Vanderghast, Access MVP


I'm trying to get a set of data based on an option that the user
selects
on a
form (i.e., they select one of the options and then click and button,
then
this code executes). Here's what I have so far:

Private Sub Command15_Click()

Dim printedRecords As String
Dim archivedQuery As String

If SelectPrint = 1 Then
DoCmd.OpenReport "rpt_All_Archived", acViewPreview
printedRecords = "SELECT tbl_Archived_Requests.ID,
tbl_Archived_Requests.[Printed?] FROM tbl_Archived_Requests INNER JOIN
qry_Print_All_Archived ON tbl_Archived_Requests.ID =
qry_Print_All_Archived.ID;"
Else
DoCmd.OpenReport "rpt_Selected_Archived", acViewPreview
printedRecords = "SELECT tbl_Archived_Requests.ID,
tbl_Archived_Requests.[Printed?] FROM tbl_Archived_Requests INNER JOIN
qry_Print_Selected_Archived ON tbl_Archived_Requests.ID =
qry_Print_Selected_Archived.ID;"
End If

Debug.Print printedRecords

Dim outRecords As DAO.Recordset

Set outRecords = CurrentDb().OpenRecordset(printedRecords)

The last line is giving me an error - "Run-time error: 3061. Too few
parameters. Expected: 3"

This is confusing because I use the OpenRecordset(<query string>) used
in
another place in the database and it's working fine. So I guess I'm
not
sure
why the function is requesting 3 parameters here and not elsewhere.
Click to expand...
Click to expand...
Click to expand...
 
T

thefonz37

Oh, ok - you're absolutely right, then.

I knew the query wasn't updateable, though, which is why I was trying to do
it by creating a recordset variable to do the heavy lifting for me. To be
honest, I'm not exactly sure why the query isn't updateable - I assumed it
had to do with an indeterminate relationship somewhere between the tables
it's based on. Would better defining the keys in the tables help?

Michel Walsh said:
Exactly the case, your qry_xxx query probably uses the said syntax:
FORMS!formName!ControlName.



You can update the table without opening a recordset at all, with a


DoCmd.RunSQL "UPDATE ... SET [Printed?]= true WHERE [Printed?] = false"



where the ... is to be replace by the appropriate join. You may also have to
specify the table name in front of the field [Printed?] like:
tableName.[Printed?]

That also assumes your join leave the query updateable. You can test it in
the query designer first.



Vanderghast, Access MVP





thefonz37 said:
The qry_Print_XXXX items are actually queries - not sure if that makes a
difference.

What I was planning on doing is opening a report based on user input (the
choices are "new" records and records that have already been printed),
then
opening a recordset for the records that show up in that report and
setting
the "Printed?" column in that recordset to true if it's false.

Is there a better way to do this?

Michel Walsh said:
CurrentDb does not automatically solve the references like
FORMS!formName!ControlName while DoCmd and the user interface does. I
guess
that qry_Print_All_xxx use that syntax.

Do you need to open the recordset? if you can specify the string as
RecordSource of a form or of report, the automatic resolution will be
just
fine.

Vanderghast, Access MVP


I'm trying to get a set of data based on an option that the user
selects
on a
form (i.e., they select one of the options and then click and button,
then
this code executes). Here's what I have so far:

Private Sub Command15_Click()

Dim printedRecords As String
Dim archivedQuery As String

If SelectPrint = 1 Then
DoCmd.OpenReport "rpt_All_Archived", acViewPreview
printedRecords = "SELECT tbl_Archived_Requests.ID,
tbl_Archived_Requests.[Printed?] FROM tbl_Archived_Requests INNER JOIN
qry_Print_All_Archived ON tbl_Archived_Requests.ID =
qry_Print_All_Archived.ID;"
Else
DoCmd.OpenReport "rpt_Selected_Archived", acViewPreview
printedRecords = "SELECT tbl_Archived_Requests.ID,
tbl_Archived_Requests.[Printed?] FROM tbl_Archived_Requests INNER JOIN
qry_Print_Selected_Archived ON tbl_Archived_Requests.ID =
qry_Print_Selected_Archived.ID;"
End If

Debug.Print printedRecords

Dim outRecords As DAO.Recordset

Set outRecords = CurrentDb().OpenRecordset(printedRecords)

The last line is giving me an error - "Run-time error: 3061. Too few
parameters. Expected: 3"

This is confusing because I use the OpenRecordset(<query string>) used
in
another place in the database and it's working fine. So I guess I'm
not
sure
why the function is requesting 3 parameters here and not elsewhere.
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

Actually, scratch the last question - the relationship is never going to be
one-to-one, so the query isn't going to be updateable.
Click to expand...

It is emphatically NOT necessary that relationships be one to one to be
updateable. One to many joined on fields for which referential integrity is
enforced is also updateable.
 
M

Michel Walsh

Indeed, the relationship does not have to be a one to one.

Among the most common reason for not being updatable:

Using an aggregate or a group (except that in a subquery for the where
clause), using an union/union all, using a non-equi-join, referencing a
not-updateable query. Sometimes, adding the primary key or moving the
criteria from the where clause to the join may help.

For you query, you may not need the join. After all, setting to true a value
already true is not a major problem, so, it may be that you can 'relax' the
conditions and that, in turn, may help making your query updateable.


Vanderghast, Access MVP
 
T

thefonz37

Good deal, thanks for the help, guys. I will try to modify the query to
accomodate this method. I was going by what the help file says with the
one-to-one thing.
 
T

thefonz37

Maybe I misunderstood what was trying to be said, but that's what it looks
like to me:

It's in Access Home -> Queries -> Edit Data in Query
 

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

Passing Form Value to a Query in VB 2
Running Report via VBA 3
SQL pass thru query from code always propting for DSN 5
Run-time error 3061 6
Between Dates on Combo Form 10
Dynamic query - how to pass it into a report 2
Run delete query instead of report... 4
Syntax Error in Query Expression 2

Top