Input box to variable????

  • Thread starter Thread starter SusanV
  • Start date Start date
S

SusanV

I have an Access report based on the following user-input query:
''''''''''''''''''''
SELECT AO.PMQA, AO.Title, AO.Completed, AO.SentSSI, AOLCode.LCode,
LSGCode.TITLE, LSGCode.NARR, LSGCode.ManHours, LSGCode.[L-CauseCode],
LSGCode.AUTH
FROM LSGCode INNER JOIN (AO INNER JOIN AOLCode ON AO.PMQA = AOLCode.PMQA) ON
LSGCode.LSGCODE = AOLCode.LCode
WHERE (((AO.PMQA) Like [Enter the Report Number (1-52 NOT 001-052)]));
''''''''''''''''
This works fine when the user needs just that specific report, but there is
a "set" of reports they are required to generate weekly.
The other Access reports are based on queries similar to this, and always
the user-input is the same criteria - AO.PMQA (referred to as "Report
Number"). Users need all the reports related to the input "Report Number" at
the same time. As they have to output 4 different reports, based on between
5 and 10 different "Report Numbers," they are complaining about having to
enter the Report Number over and over. Can't say as I blame them there!

How can I get the value of AO.PMQA (which is an autonumber field and the PK)
as a variable and pass it to VBA for use in DoCmd OutputTo acReport... ?
This way they can enter the "Report Number" once, get all the snapshot files
they need, then enter another "Report Number" for the next recordset and so
on.

Sorry if this seems like a simple thing, but I've searched the web and the
groups and can't seem to get anything I find to work. For example:
'''''''''''''''
Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like [Enter the Report Number];"
''''''''''''''''
Error is "Type Mis-match."

Change var Rep to String and the var actually hold the SQL statement, which
is useless, I need the var to hold the value of the field, numeric 1-52.

Frustrating... and probably right in front of my nose!

TIA,

SusanV
 
Your Type Mismatch is because you were dimming Rep as Integer, then putting
text in it. Your select statement is incorrect. It should be:

Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like " & Cstr(intRptNo) & ";"

As far as doing multiple reports, that will take either a Do Loop or a For
Next, but I don't know whether the report numbers will be like from 5 throuth
10 or like 5,9,3,8,10. Depending on which way it needs to be will determine
how you do it.
 
Hi Klatuu,

Thanks for replying! Tried your code in a new form, button OnClick function:
'''''''''''''''''''''''''''
Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like " & CStr(intRptNo) & ";"

Dim repNum As String
repNum = Format(Rep)

MsgBox ("Report Number is " & repNum)
''''''''''''''''''''''''''''''
Again, Type mismatch. Blech. This basic code works perfectly on a form when
Rep = Me.PMQA.... For example I have this code to open a document based on
the record shown in the form:
'''''''''''''''''''''''''''''
Private Sub cmdViewRep_Click()
Dim Rep As Integer
Rep = Me.PMQA
Dim repNum As String

If (Rep > 99) Then
repNum = "AO-" & Format(Rep)
ElseIf (Rep > 9) Then
repNum = "AO-0" & Format(Rep)
Else: repNum = "AO-00" & Format(Rep)
End If

'Verify Report Number is correctly formatted
msgBox ("Report Number is " & repNum)

StartDoc "W:\PMQA\AO\Reports\" & repNum & "REP.doc"

End Sub
'''''''''''''''''''''''''''''''''
I grabbed that code and substituted the Rep = line you supplied, and again
Type mismatch on both the msgBox and the StartDoc function. I'm getting very
frustrated, and thinking maybe I'm going about this the wrong way?

As to looping, what I'd *like* to do is for users to enter a "Report
Number," automate the 4 snapshot file output (which I kind of have worked
out, but until I get this part I can't fully test) then they can click the
button again to output the set of report for a different "Report Number"
until they're all set. Every week it's a different batch of "Report Numbers"
and not always the same number of records, so manual input for a single
"Report Number" is the simplest way for this I think. At any rate, I'll
cross that bridge if I get in sight of it <grin>

Any other ideas?

Thanks again for your time and effort,

SusanV



Klatuu said:
Your Type Mismatch is because you were dimming Rep as Integer, then
putting
text in it. Your select statement is incorrect. It should be:

Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like " & Cstr(intRptNo) & ";"

As far as doing multiple reports, that will take either a Do Loop or a For
Next, but I don't know whether the report numbers will be like from 5
throuth
10 or like 5,9,3,8,10. Depending on which way it needs to be will
determine
how you do it.

SusanV said:
I have an Access report based on the following user-input query:
''''''''''''''''''''
SELECT AO.PMQA, AO.Title, AO.Completed, AO.SentSSI, AOLCode.LCode,
LSGCode.TITLE, LSGCode.NARR, LSGCode.ManHours, LSGCode.[L-CauseCode],
LSGCode.AUTH
FROM LSGCode INNER JOIN (AO INNER JOIN AOLCode ON AO.PMQA = AOLCode.PMQA)
ON
LSGCode.LSGCODE = AOLCode.LCode
WHERE (((AO.PMQA) Like [Enter the Report Number (1-52 NOT 001-052)]));
''''''''''''''''
This works fine when the user needs just that specific report, but there
is
a "set" of reports they are required to generate weekly.
The other Access reports are based on queries similar to this, and always
the user-input is the same criteria - AO.PMQA (referred to as "Report
Number"). Users need all the reports related to the input "Report Number"
at
the same time. As they have to output 4 different reports, based on
between
5 and 10 different "Report Numbers," they are complaining about having to
enter the Report Number over and over. Can't say as I blame them there!

How can I get the value of AO.PMQA (which is an autonumber field and the
PK)
as a variable and pass it to VBA for use in DoCmd OutputTo acReport... ?
This way they can enter the "Report Number" once, get all the snapshot
files
they need, then enter another "Report Number" for the next recordset and
so
on.

Sorry if this seems like a simple thing, but I've searched the web and
the
groups and can't seem to get anything I find to work. For example:
'''''''''''''''
Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like [Enter the Report Number];"
''''''''''''''''
Error is "Type Mis-match."

Change var Rep to String and the var actually hold the SQL statement,
which
is useless, I need the var to hold the value of the field, numeric 1-52.

Frustrating... and probably right in front of my nose!

TIA,

SusanV
Click to expand...
Click to expand...
 
Create an unbound Form with a control for entry of the report number
and a "Continue" button to Hide it (NB NOT Close it!), open it as a
Dialog box from the VBA Code which runs the Reports, reference the
Form Control in the Report Formats and Queries where you want the
report number, and close the Form from your code after running the
last Report for that report number.

I have an Access report based on the following user-input query:
''''''''''''''''''''
SELECT AO.PMQA, AO.Title, AO.Completed, AO.SentSSI, AOLCode.LCode,
LSGCode.TITLE, LSGCode.NARR, LSGCode.ManHours, LSGCode.[L-CauseCode],
LSGCode.AUTH
FROM LSGCode INNER JOIN (AO INNER JOIN AOLCode ON AO.PMQA = AOLCode.PMQA) ON
LSGCode.LSGCODE = AOLCode.LCode
WHERE (((AO.PMQA) Like [Enter the Report Number (1-52 NOT 001-052)]));
''''''''''''''''
This works fine when the user needs just that specific report, but there is
a "set" of reports they are required to generate weekly.
The other Access reports are based on queries similar to this, and always
the user-input is the same criteria - AO.PMQA (referred to as "Report
Number"). Users need all the reports related to the input "Report Number" at
the same time. As they have to output 4 different reports, based on between
5 and 10 different "Report Numbers," they are complaining about having to
enter the Report Number over and over. Can't say as I blame them there!

How can I get the value of AO.PMQA (which is an autonumber field and the PK)
as a variable and pass it to VBA for use in DoCmd OutputTo acReport... ?
This way they can enter the "Report Number" once, get all the snapshot files
they need, then enter another "Report Number" for the next recordset and so
on.

Sorry if this seems like a simple thing, but I've searched the web and the
groups and can't seem to get anything I find to work. For example:
'''''''''''''''
Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like [Enter the Report Number];"
''''''''''''''''
Error is "Type Mis-match."

Change var Rep to String and the var actually hold the SQL statement, which
is useless, I need the var to hold the value of the field, numeric 1-52.

Frustrating... and probably right in front of my nose!

TIA,

SusanV
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Hi Peter,

Thanks for replying, but I'm afraid you lost me...

SusanV

Peter R. Fletcher said:
Create an unbound Form with a control for entry of the report number
and a "Continue" button to Hide it (NB NOT Close it!), open it as a
Dialog box from the VBA Code which runs the Reports, reference the
Form Control in the Report Formats and Queries where you want the
report number, and close the Form from your code after running the
last Report for that report number.

I have an Access report based on the following user-input query:
''''''''''''''''''''
SELECT AO.PMQA, AO.Title, AO.Completed, AO.SentSSI, AOLCode.LCode,
LSGCode.TITLE, LSGCode.NARR, LSGCode.ManHours, LSGCode.[L-CauseCode],
LSGCode.AUTH
FROM LSGCode INNER JOIN (AO INNER JOIN AOLCode ON AO.PMQA = AOLCode.PMQA)
ON
LSGCode.LSGCODE = AOLCode.LCode
WHERE (((AO.PMQA) Like [Enter the Report Number (1-52 NOT 001-052)]));
''''''''''''''''
This works fine when the user needs just that specific report, but there
is
a "set" of reports they are required to generate weekly.
The other Access reports are based on queries similar to this, and always
the user-input is the same criteria - AO.PMQA (referred to as "Report
Number"). Users need all the reports related to the input "Report Number"
at
the same time. As they have to output 4 different reports, based on
between
5 and 10 different "Report Numbers," they are complaining about having to
enter the Report Number over and over. Can't say as I blame them there!

How can I get the value of AO.PMQA (which is an autonumber field and the
PK)
as a variable and pass it to VBA for use in DoCmd OutputTo acReport... ?
This way they can enter the "Report Number" once, get all the snapshot
files
they need, then enter another "Report Number" for the next recordset and
so
on.

Sorry if this seems like a simple thing, but I've searched the web and the
groups and can't seem to get anything I find to work. For example:
'''''''''''''''
Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like [Enter the Report Number];"
''''''''''''''''
Error is "Type Mis-match."

Change var Rep to String and the var actually hold the SQL statement,
which
is useless, I need the var to hold the value of the field, numeric 1-52.

Frustrating... and probably right in front of my nose!

TIA,

SusanV
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher
Click to expand...
 
Hi Peter,

Thanks - Once I realized I could use Forms!Form.Field as the query criteria
I grasped the rest of what you said and I'm well on my way.

Thank you VERY much for this clue - I had no idea you could use a form field
like this - SWEET!!

SusanV

Peter R. Fletcher said:
Create an unbound Form with a control for entry of the report number
and a "Continue" button to Hide it (NB NOT Close it!), open it as a
Dialog box from the VBA Code which runs the Reports, reference the
Form Control in the Report Formats and Queries where you want the
report number, and close the Form from your code after running the
last Report for that report number.

I have an Access report based on the following user-input query:
''''''''''''''''''''
SELECT AO.PMQA, AO.Title, AO.Completed, AO.SentSSI, AOLCode.LCode,
LSGCode.TITLE, LSGCode.NARR, LSGCode.ManHours, LSGCode.[L-CauseCode],
LSGCode.AUTH
FROM LSGCode INNER JOIN (AO INNER JOIN AOLCode ON AO.PMQA = AOLCode.PMQA)
ON
LSGCode.LSGCODE = AOLCode.LCode
WHERE (((AO.PMQA) Like [Enter the Report Number (1-52 NOT 001-052)]));
''''''''''''''''
This works fine when the user needs just that specific report, but there
is
a "set" of reports they are required to generate weekly.
The other Access reports are based on queries similar to this, and always
the user-input is the same criteria - AO.PMQA (referred to as "Report
Number"). Users need all the reports related to the input "Report Number"
at
the same time. As they have to output 4 different reports, based on
between
5 and 10 different "Report Numbers," they are complaining about having to
enter the Report Number over and over. Can't say as I blame them there!

How can I get the value of AO.PMQA (which is an autonumber field and the
PK)
as a variable and pass it to VBA for use in DoCmd OutputTo acReport... ?
This way they can enter the "Report Number" once, get all the snapshot
files
they need, then enter another "Report Number" for the next recordset and
so
on.

Sorry if this seems like a simple thing, but I've searched the web and the
groups and can't seem to get anything I find to work. For example:
'''''''''''''''
Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like [Enter the Report Number];"
''''''''''''''''
Error is "Type Mis-match."

Change var Rep to String and the var actually hold the SQL statement,
which
is useless, I need the var to hold the value of the field, numeric 1-52.

Frustrating... and probably right in front of my nose!

TIA,

SusanV
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher
Click to expand...
 
Susan,

ARGGHHHHHHHHHHHH
Sorry, code was not tested. If the code you sent is current, you are still
dimming Rep as Interger and trying to put a string in it. That will give a
type mismatch. You can replace your multiple If statements to format RepNum
with:
Format(RepNum,"00")
That will force always 2 digits with leading zero.
What data type is PMQA - That may be a problem
don't see why the message box isn't working, worked for me here.
Also, can you post the code for StartDoc. Can't tell much without seeing
the code.


SusanV said:
Hi Klatuu,

Thanks for replying! Tried your code in a new form, button OnClick function:
'''''''''''''''''''''''''''
Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like " & CStr(intRptNo) & ";"

Dim repNum As String
repNum = Format(Rep)

MsgBox ("Report Number is " & repNum)
''''''''''''''''''''''''''''''
Again, Type mismatch. Blech. This basic code works perfectly on a form when
Rep = Me.PMQA.... For example I have this code to open a document based on
the record shown in the form:
'''''''''''''''''''''''''''''
Private Sub cmdViewRep_Click()
Dim Rep As Integer
Rep = Me.PMQA
Dim repNum As String

If (Rep > 99) Then
repNum = "AO-" & Format(Rep)
ElseIf (Rep > 9) Then
repNum = "AO-0" & Format(Rep)
Else: repNum = "AO-00" & Format(Rep)
End If

'Verify Report Number is correctly formatted
msgBox ("Report Number is " & repNum)

StartDoc "W:\PMQA\AO\Reports\" & repNum & "REP.doc"

End Sub
'''''''''''''''''''''''''''''''''
I grabbed that code and substituted the Rep = line you supplied, and again
Type mismatch on both the msgBox and the StartDoc function. I'm getting very
frustrated, and thinking maybe I'm going about this the wrong way?

As to looping, what I'd *like* to do is for users to enter a "Report
Number," automate the 4 snapshot file output (which I kind of have worked
out, but until I get this part I can't fully test) then they can click the
button again to output the set of report for a different "Report Number"
until they're all set. Every week it's a different batch of "Report Numbers"
and not always the same number of records, so manual input for a single
"Report Number" is the simplest way for this I think. At any rate, I'll
cross that bridge if I get in sight of it <grin>

Any other ideas?

Thanks again for your time and effort,

SusanV



Klatuu said:
Your Type Mismatch is because you were dimming Rep as Integer, then
putting
text in it. Your select statement is incorrect. It should be:

Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like " & Cstr(intRptNo) & ";"

As far as doing multiple reports, that will take either a Do Loop or a For
Next, but I don't know whether the report numbers will be like from 5
throuth
10 or like 5,9,3,8,10. Depending on which way it needs to be will
determine
how you do it.

SusanV said:
I have an Access report based on the following user-input query:
''''''''''''''''''''
SELECT AO.PMQA, AO.Title, AO.Completed, AO.SentSSI, AOLCode.LCode,
LSGCode.TITLE, LSGCode.NARR, LSGCode.ManHours, LSGCode.[L-CauseCode],
LSGCode.AUTH
FROM LSGCode INNER JOIN (AO INNER JOIN AOLCode ON AO.PMQA = AOLCode.PMQA)
ON
LSGCode.LSGCODE = AOLCode.LCode
WHERE (((AO.PMQA) Like [Enter the Report Number (1-52 NOT 001-052)]));
''''''''''''''''
This works fine when the user needs just that specific report, but there
is
a "set" of reports they are required to generate weekly.
The other Access reports are based on queries similar to this, and always
the user-input is the same criteria - AO.PMQA (referred to as "Report
Number"). Users need all the reports related to the input "Report Number"
at
the same time. As they have to output 4 different reports, based on
between
5 and 10 different "Report Numbers," they are complaining about having to
enter the Report Number over and over. Can't say as I blame them there!

How can I get the value of AO.PMQA (which is an autonumber field and the
PK)
as a variable and pass it to VBA for use in DoCmd OutputTo acReport... ?
This way they can enter the "Report Number" once, get all the snapshot
files
they need, then enter another "Report Number" for the next recordset and
so
on.

Sorry if this seems like a simple thing, but I've searched the web and
the
groups and can't seem to get anything I find to work. For example:
'''''''''''''''
Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like [Enter the Report Number];"
''''''''''''''''
Error is "Type Mis-match."

Change var Rep to String and the var actually hold the SQL statement,
which
is useless, I need the var to hold the value of the field, numeric 1-52.

Frustrating... and probably right in front of my nose!

TIA,

SusanV
Click to expand...
Click to expand...
Click to expand...
 
Klatuu,

I don't understand why it didn't work as the Format(Rep) converts it to a
string... And thanks for the Format (Rep, "00") tip - didn't know that would
populate the required zeroes.

This seems to be working well using a dialog form and
Forms!DialogForm.InputField for the query, I'm to the point now of refining
it and bringing the query into VBA.

Many thanks to you and to Peter for the hand up!

SusanV


Klatuu said:
Susan,

ARGGHHHHHHHHHHHH
Sorry, code was not tested. If the code you sent is current, you are
still
dimming Rep as Interger and trying to put a string in it. That will give
a
type mismatch. You can replace your multiple If statements to format
RepNum
with:
Format(RepNum,"00")
That will force always 2 digits with leading zero.
What data type is PMQA - That may be a problem
don't see why the message box isn't working, worked for me here.
Also, can you post the code for StartDoc. Can't tell much without seeing
the code.


SusanV said:
Hi Klatuu,

Thanks for replying! Tried your code in a new form, button OnClick
function:
'''''''''''''''''''''''''''
Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like " & CStr(intRptNo) & ";"

Dim repNum As String
repNum = Format(Rep)

MsgBox ("Report Number is " & repNum)
''''''''''''''''''''''''''''''
Again, Type mismatch. Blech. This basic code works perfectly on a form
when
Rep = Me.PMQA.... For example I have this code to open a document based
on
the record shown in the form:
'''''''''''''''''''''''''''''
Private Sub cmdViewRep_Click()
Dim Rep As Integer
Rep = Me.PMQA
Dim repNum As String

If (Rep > 99) Then
repNum = "AO-" & Format(Rep)
ElseIf (Rep > 9) Then
repNum = "AO-0" & Format(Rep)
Else: repNum = "AO-00" & Format(Rep)
End If

'Verify Report Number is correctly formatted
msgBox ("Report Number is " & repNum)

StartDoc "W:\PMQA\AO\Reports\" & repNum & "REP.doc"

End Sub
'''''''''''''''''''''''''''''''''
I grabbed that code and substituted the Rep = line you supplied, and
again
Type mismatch on both the msgBox and the StartDoc function. I'm getting
very
frustrated, and thinking maybe I'm going about this the wrong way?

As to looping, what I'd *like* to do is for users to enter a "Report
Number," automate the 4 snapshot file output (which I kind of have worked
out, but until I get this part I can't fully test) then they can click
the
button again to output the set of report for a different "Report Number"
until they're all set. Every week it's a different batch of "Report
Numbers"
and not always the same number of records, so manual input for a single
"Report Number" is the simplest way for this I think. At any rate, I'll
cross that bridge if I get in sight of it <grin>

Any other ideas?

Thanks again for your time and effort,

SusanV



Klatuu said:
Your Type Mismatch is because you were dimming Rep as Integer, then
putting
text in it. Your select statement is incorrect. It should be:

Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like " & Cstr(intRptNo) & ";"

As far as doing multiple reports, that will take either a Do Loop or a
For
Next, but I don't know whether the report numbers will be like from 5
throuth
10 or like 5,9,3,8,10. Depending on which way it needs to be will
determine
how you do it.

:

I have an Access report based on the following user-input query:
''''''''''''''''''''
SELECT AO.PMQA, AO.Title, AO.Completed, AO.SentSSI, AOLCode.LCode,
LSGCode.TITLE, LSGCode.NARR, LSGCode.ManHours, LSGCode.[L-CauseCode],
LSGCode.AUTH
FROM LSGCode INNER JOIN (AO INNER JOIN AOLCode ON AO.PMQA =
AOLCode.PMQA)
ON
LSGCode.LSGCODE = AOLCode.LCode
WHERE (((AO.PMQA) Like [Enter the Report Number (1-52 NOT 001-052)]));
''''''''''''''''
This works fine when the user needs just that specific report, but
there
is
a "set" of reports they are required to generate weekly.
The other Access reports are based on queries similar to this, and
always
the user-input is the same criteria - AO.PMQA (referred to as "Report
Number"). Users need all the reports related to the input "Report
Number"
at
the same time. As they have to output 4 different reports, based on
between
5 and 10 different "Report Numbers," they are complaining about having
to
enter the Report Number over and over. Can't say as I blame them
there!

How can I get the value of AO.PMQA (which is an autonumber field and
the
PK)
as a variable and pass it to VBA for use in DoCmd OutputTo acReport...
?
This way they can enter the "Report Number" once, get all the snapshot
files
they need, then enter another "Report Number" for the next recordset
and
so
on.

Sorry if this seems like a simple thing, but I've searched the web and
the
groups and can't seem to get anything I find to work. For example:
'''''''''''''''
Dim Rep As Integer
Rep = "SELECT PMQA FROM AO WHERE PMQA Like [Enter the Report Number];"
''''''''''''''''
Error is "Type Mis-match."

Change var Rep to String and the var actually hold the SQL statement,
which
is useless, I need the var to hold the value of the field, numeric
1-52.

Frustrating... and probably right in front of my nose!

TIA,

SusanV
Click to expand...
Click to expand...
Click to expand...
 
Back
Top