Help with populating a report

[graeme34 via AccessMonster.com]

Hi

I have command button with the following code on:

Private Sub cmdPrintDespatchNote_Click()

Dim lngOrderNum As Long
Dim rsDes As DAO.Recordset


Set rsDes = CurrentDb.OpenRecordset("tblDespatch")

lngOrderNum = GetOrderNumber

If lngOrderNum = 0 Then
MsgBox "No Order Number Chosen", vbOKOnly, "NO CHOICE!"
Exit Sub
Else

With rsDes
.AddNew
!DespatchNumber = Nz(DMax("[DespatchNumber]", _
"tblDespatch"), 0) + 1
!SalesOrderNumber = lngOrderNum
!DateOfDespatch = Now()
.Update
.MoveLast
End With

End If

End Sub

Where GetOrderNumber is a public function which prompts the user to enter the
order number in an input box.
The subroutine then creates a new record in the despatch table, now I'm stuck
on how to populate my report (despatch note) with the required data. I have
the
following query built, but when I try to open the report I am prompted with a
parameter value to be added for my report, I assume I need to place some
criteria somewhere in the query, but not too sure where. Ideally I would like
it to
be based on the despatch record I have just added.
Here is the query:

SELECT tblAccount.AccountName, tblAccount.[Account Address 1], tblAccount.
[Account Address 2], tblAccount.[Account Address 3], tblAccount.[Account Town
/ City], tblAccount.[Post Code], tblDespatch.DespatchNumber, tblSalesOrder.
SalesOrderNumber, tblSalesOrder.CustomerOrderNumber, tblSalesOrder.
DeliveryMode, tblSalesOrderLine.[Product Code], tblSalesOrderLine.
QuantityOrdered
FROM ((tblAccount INNER JOIN tblSalesOrder ON tblAccount.AccountIndex =
tblSalesOrder.AccountIndex) INNER JOIN tblDespatch ON tblSalesOrder.
SalesOrderNumber = tblDespatch.SalesOrderNumber) INNER JOIN tblSalesOrderLine
ON tblSalesOrder.SalesOrderNumber = tblSalesOrderLine.SalesOrderNumber;

Thanks in advance

 

[tina]

first two questions: is the report bound to the query you included in your
post? if so, when you open the report manually from the database window,
does it prompt you for a parameter? if so, then there is an issue with the
query that has nothing to do with the VBA code; you'll need to address that
before anything else.

once the query is running successfully, the question becomes: how to filter
the report to display only the record that you just created with the VBA
code? i'll assume that the DespatchNumber field is a unique index in
tblDespatch. suggest you modify the code slightly, to save the "new"
DespatchNumber value in a variable, by creating the variable, as

Dim lngOrderNum As Long, lngDespatchNo As Long

and then saving the newly generated value to the variable, as

lngDespatchNo = Nz(DMax("[DespatchNumber]", _
"tblDespatch"), 0) + 1
!DespatchNumber = lngDespatchNo

now that you have the new value available in the code, you can use it to set
criteria when you open the report, as

DoCmd.OpenReport "ReportName", , , "DespatchNumber = " _
& lngDespatchNo

hth

Hi

I have command button with the following code on:

Private Sub cmdPrintDespatchNote_Click()

Dim lngOrderNum As Long
Dim rsDes As DAO.Recordset


Set rsDes = CurrentDb.OpenRecordset("tblDespatch")

lngOrderNum = GetOrderNumber

If lngOrderNum = 0 Then
MsgBox "No Order Number Chosen", vbOKOnly, "NO CHOICE!"
Exit Sub
Else

With rsDes
.AddNew
!DespatchNumber = Nz(DMax("[DespatchNumber]", _
"tblDespatch"), 0) + 1
!SalesOrderNumber = lngOrderNum
!DateOfDespatch = Now()
.Update
.MoveLast
End With

End If

End Sub

Where GetOrderNumber is a public function which prompts the user to enter the
order number in an input box.
The subroutine then creates a new record in the despatch table, now I'm stuck
on how to populate my report (despatch note) with the required data. I have
the
following query built, but when I try to open the report I am prompted with a
parameter value to be added for my report, I assume I need to place some
criteria somewhere in the query, but not too sure where. Ideally I would like
it to
be based on the despatch record I have just added.
Here is the query:

SELECT tblAccount.AccountName, tblAccount.[Account Address 1], tblAccount.
[Account Address 2], tblAccount.[Account Address 3], tblAccount.[Account Town
/ City], tblAccount.[Post Code], tblDespatch.DespatchNumber, tblSalesOrder.
SalesOrderNumber, tblSalesOrder.CustomerOrderNumber, tblSalesOrder.
DeliveryMode, tblSalesOrderLine.[Product Code], tblSalesOrderLine.
QuantityOrdered
FROM ((tblAccount INNER JOIN tblSalesOrder ON tblAccount.AccountIndex =
tblSalesOrder.AccountIndex) INNER JOIN tblDespatch ON tblSalesOrder.
SalesOrderNumber = tblDespatch.SalesOrderNumber) INNER JOIN tblSalesOrderLine
ON tblSalesOrder.SalesOrderNumber = tblSalesOrderLine.SalesOrderNumber;

Thanks in advance

 

[graeme34 via AccessMonster.com]

Hi Tina,

Thanks for your tips, getting there now

Still a problem with filtering the query as the report is always opening with
the first record in the queries dynaset.

I have tried placing this criteria in my query but getting a data mismatch
error.
You'll have to bear with me I'm fairly new to this. As the subroutine is
private can I not use the lngDespatchNum variable in my query, as follows:

WHERE (((tblDespatch.DespatchNumber)="lngDespatchNum"));

Thanks again Graeme

first two questions: is the report bound to the query you included in your
post? if so, when you open the report manually from the database window,
does it prompt you for a parameter? if so, then there is an issue with the
query that has nothing to do with the VBA code; you'll need to address that
before anything else.

once the query is running successfully, the question becomes: how to filter
the report to display only the record that you just created with the VBA
code? i'll assume that the DespatchNumber field is a unique index in
tblDespatch. suggest you modify the code slightly, to save the "new"
DespatchNumber value in a variable, by creating the variable, as

Dim lngOrderNum As Long, lngDespatchNo As Long

and then saving the newly generated value to the variable, as

lngDespatchNo = Nz(DMax("[DespatchNumber]", _
"tblDespatch"), 0) + 1
!DespatchNumber = lngDespatchNo

now that you have the new value available in the code, you can use it to set
criteria when you open the report, as

DoCmd.OpenReport "ReportName", , , "DespatchNumber = " _
& lngDespatchNo

hth

Hi

[quoted text clipped - 52 lines]

Thanks in advance

 

[tina]

no, you can't use a VBA variable in a query object like that. reread my
previous post; i suggested that you filter the *report*, not the underlying
query.

hth

Hi Tina,

Thanks for your tips, getting there now

Still a problem with filtering the query as the report is always opening with
the first record in the queries dynaset.

I have tried placing this criteria in my query but getting a data mismatch
error.
You'll have to bear with me I'm fairly new to this. As the subroutine is
private can I not use the lngDespatchNum variable in my query, as follows:

WHERE (((tblDespatch.DespatchNumber)="lngDespatchNum"));

Thanks again Graeme

first two questions: is the report bound to the query you included in your
post? if so, when you open the report manually from the database window,
does it prompt you for a parameter? if so, then there is an issue with the
query that has nothing to do with the VBA code; you'll need to address that
before anything else.

once the query is running successfully, the question becomes: how to filter
the report to display only the record that you just created with the VBA
code? i'll assume that the DespatchNumber field is a unique index in
tblDespatch. suggest you modify the code slightly, to save the "new"
DespatchNumber value in a variable, by creating the variable, as

Dim lngOrderNum As Long, lngDespatchNo As Long

and then saving the newly generated value to the variable, as

lngDespatchNo = Nz(DMax("[DespatchNumber]", _
"tblDespatch"), 0) + 1
!DespatchNumber = lngDespatchNo

now that you have the new value available in the code, you can use it to set
criteria when you open the report, as

DoCmd.OpenReport "ReportName", , , "DespatchNumber = " _
& lngDespatchNo

hth

Hi

[quoted text clipped - 52 lines]

Thanks in advance

 

[graeme34 via AccessMonster.com]

Hi again Tina

By filtering the report did you mean adding the line of code:

DoCmd.OpenReport "repDespatchNote", acViewPreview, "DespatchNumber = " _
& lngDespatchNum

I've done this but all this does is open the report with the depatch number
field = lngDespatchNum, but all the other fields have the values from the
first recordset of the reports, i.e the values that are related to despatch
number one. Don't I still need to filter the query so that there is only the
required data in the queries recordset ?

Thanks again for your help!

no, you can't use a VBA variable in a query object like that. reread my
previous post; i suggested that you filter the *report*, not the underlying
query.

hth

Hi Tina,

[quoted text clipped - 45 lines]

 

[graeme34 via AccessMonster.com]

I think I'm gradually getting there.....

I've managed to get my query right by using a textbox box from a criteria
form.

My problem now is when I run the Docmd.OpenReport.....I'm still being
prompted for a parameter value for the Despatch number, should it not be set
to the value of lngDespatchNum??

lngDespatchNum is intialised in the line...

lngDespatchNum = Nz(DMax("[DespatchNumber]", _
"tblDespatch"), 0) + 1

Then later on I use the Docmd line....

DoCmd.OpenReport "repDespatchNote", acViewPreview, "DespatchNumber = " _
& lngDespatchNum

Any ideas why I'm being prompted for the despatch number value???

Thank you.

Hi again Tina

By filtering the report did you mean adding the line of code:

DoCmd.OpenReport "repDespatchNote", acViewPreview, "DespatchNumber = " _
& lngDespatchNum

I've done this but all this does is open the report with the depatch number
field = lngDespatchNum, but all the other fields have the values from the
first recordset of the reports, i.e the values that are related to despatch
number one. Don't I still need to filter the query so that there is only the
required data in the queries recordset ?

Thanks again for your help!

no, you can't use a VBA variable in a query object like that. reread my
previous post; i suggested that you filter the *report*, not the underlying

[quoted text clipped - 7 lines]

 

[tina]

sounds like Access does not recognize [DespatchNumber] as a field in the
report's underlying query. *is* it a field in the query? and is the field
name spelled correctly in the VBA code?

hth

I think I'm gradually getting there.....

I've managed to get my query right by using a textbox box from a criteria
form.

My problem now is when I run the Docmd.OpenReport.....I'm still being
prompted for a parameter value for the Despatch number, should it not be set
to the value of lngDespatchNum??

lngDespatchNum is intialised in the line...

lngDespatchNum = Nz(DMax("[DespatchNumber]", _
"tblDespatch"), 0) + 1

Then later on I use the Docmd line....

DoCmd.OpenReport "repDespatchNote", acViewPreview, "DespatchNumber = " _
& lngDespatchNum

Any ideas why I'm being prompted for the despatch number value???

Thank you.

Hi again Tina

By filtering the report did you mean adding the line of code:

DoCmd.OpenReport "repDespatchNote", acViewPreview, "DespatchNumber = " _
& lngDespatchNum

I've done this but all this does is open the report with the depatch number
field = lngDespatchNum, but all the other fields have the values from the
first recordset of the reports, i.e the values that are related to despatch
number one. Don't I still need to filter the query so that there is only the
required data in the queries recordset ?

Thanks again for your help!

no, you can't use a VBA variable in a query object like that. reread my
previous post; i suggested that you filter the *report*, not the

underlying
[quoted text clipped - 7 lines]

Thanks in advance

 

[graeme34 via AccessMonster.com]

Hi Tina

Well I've finally found the root of the problem! (thanks to your help )
It seems the control source property for DespatchNumber on the report was,
for some reason spelt with a space character between Despatch and Number??
I must of altered it by accident when I have been naming the textbox during
the design of the report.

Thanks again Tina for helping me through this problem, very much appreciated!!

sounds like Access does not recognize [DespatchNumber] as a field in the
report's underlying query. *is* it a field in the query? and is the field
name spelled correctly in the VBA code?

hth

I think I'm gradually getting there.....

[quoted text clipped - 38 lines]

 

[tina]

you're welcome

Hi Tina

Well I've finally found the root of the problem! (thanks to your help )
It seems the control source property for DespatchNumber on the report was,
for some reason spelt with a space character between Despatch and Number??
I must of altered it by accident when I have been naming the textbox during
the design of the report.

Thanks again Tina for helping me through this problem, very much appreciated!!

sounds like Access does not recognize [DespatchNumber] as a field in the
report's underlying query. *is* it a field in the query? and is the field
name spelled correctly in the VBA code?

hth

I think I'm gradually getting there.....

[quoted text clipped - 38 lines]

Thanks in advance