By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,105 Members | 1,040 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,105 IT Pros & Developers. It's quick & easy.

String Link Criteria for List Box

P: n/a
I have tried many iterations of constructing an expanded string link
criteria for a list box selection, but have not been successful. The
List Box uses a Totals Select Query that has a CustID, Customer Name,
Invoice Date and Invoice Amount. There is no InvoiceID/number,
otherwise it would be easy to resolve.

Currently, I must use a parameter query to key in the invoice date in
order to preview/print an invoice from the Invoice Dialog Box. Of
course, this is not desirable. Any suggestions would be welcomed.

Thanks, Rolan

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim strDocName As String
Dim strLinkCriteria As String

If IsNull(Me!CustID) Then
MsgBox "Please click a Customer sale from the list."
Else

strDocName = "InvoiceDialog"
strLinkCriteria = "CustID = " & Me!CustID

DoCmd.OpenReport strDocName, acViewPreview, , strLinkCriteria

Me.Visible = False

End If

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Const conErrDoCmdCancelled = 2501
If (err = conErrDoCmdCancelled) Then
Resume Exit_Preview_Click
Else
MsgBox "Error in preview function."
Resume Exit_Preview_Click
End If

End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"Rolan" <co******@safe-mail.net> wrote
I have tried many iterations of constructing an expanded string link
criteria for a list box selection, but have not been successful. The
List Box uses a Totals Select Query that has a CustID, Customer Name,
Invoice Date and Invoice Amount. There is no InvoiceID/number,
otherwise it would be easy to resolve.

Currently, I must use a parameter query to key in the invoice date in
order to preview/print an invoice from the Invoice Dialog Box. Of
course, this is not desirable. Any suggestions would be welcomed.

Thanks, Rolan

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim strDocName As String
Dim strLinkCriteria As String

If IsNull(Me!CustID) Then
MsgBox "Please click a Customer sale from the list."
Else

strDocName = "InvoiceDialog"
strLinkCriteria = "CustID = " & Me!CustID

DoCmd.OpenReport strDocName, acViewPreview, , strLinkCriteria

Me.Visible = False

End If

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Const conErrDoCmdCancelled = 2501
If (err = conErrDoCmdCancelled) Then
Resume Exit_Preview_Click
Else
MsgBox "Error in preview function."
Resume Exit_Preview_Click
End If

End Sub

First, why is there no involice number? You should add it.
I have tried many iterations of constructing an expanded string link
criteria for a list box selection, but have not been successful. The
List Box uses a Totals Select Query that has a CustID, Customer Name,
Invoice Date and Invoice Amount. There is no InvoiceID/number,
otherwise it would be easy to resolve.


Second, this is kinda confusing. You capitalize "Totals Select Query" as if
it has meaning, but it means nothing to me. All I can tell for sure is that
you have a listbox with columns CustID, Customer Name, Invoice Date and
Invoice Amount. I cannot tell where these fields come from based on the
data you have provided. So I'll just go with what it appears.

You are using a listbox. The value of all the columns in a lsitbox are
accessible via code:

CustID = Me.lstMyInvoiceListBox.Value

or

CustID = Me.lstMyInvoiceListBox.Column(0)

and

Customer Name = Me.lstMyInvoiceListBox.Column(1)
Invoice Date = Me.lstMyInvoiceListBox.Column(2)
Invoice Amount = Me.lstMyInvoiceListBox.Column(1)

The selected row of the listbox will hold the values you want. Since you
just want to print an invoice, all you need is those values that are already
in your listbox, AND accessible. You can push the values ito your report,
or pull them.

To pull the values in, you would need textboxes in your report to have
values like:

=Forms("FormWhereTheListBoxIs").lstMyInvoiceListBo x.Column(1)

To push the values, you would need code in the open event of the Report.
Unfortunately, you cannot place a value into a textbox in the open event of
a report (or form), and a report has no onLoad event. So use a label
instead, and set the text of the label's caption:

Me.lblCustName.Caption =
Forms("FormWhereTheListBoxIs").lstMyInvoiceListBox .Column(1)

Your code to preview the report is:

DoCmd.OpenReport "Invoice", acViewPreview

Of course, put a simple error-handler around any report action.
Darryl Kerkeslager



Nov 13 '05 #2

P: n/a
Rolan wrote:
I have tried many iterations of constructing an expanded string link
criteria for a list box selection, but have not been successful. The
List Box uses a Totals Select Query that has a CustID, Customer Name,
Invoice Date and Invoice Amount. There is no InvoiceID/number,
otherwise it would be easy to resolve.

Currently, I must use a parameter query to key in the invoice date in
order to preview/print an invoice from the Invoice Dialog Box. Of
course, this is not desirable. Any suggestions would be welcomed.
That doesn't appear too hard.

I'll point you to a few properties and you can read up on them in online
help.

With a listbox, you have a single select and multiselect. I suppose you
are using a single select (Multiselect set to None) or simple/extended.

The properties to look at are ItemsSelected, Selected, ListCount (read
up on that...the count is affected whether or not you have headers),
ItemData and ListIndex. And one more...Column

For Each var In Me.ListTools.ItemsSelected
datDate = Me.ListBoxName.Column(ColumnNum, var)
Next

Lets say you display 5 cols. The last col is the date field. Well,
then ColumnNum above would be 4. The first column is always 0.

So if you know the column, get the data from it. Since it appears you
want to select 1 record, use ListIndex to get the RowCount (acts the
same way as the Column...starts at 0) and the Column num minues 1.

You might want to also check the list count before calling the report
since the list box may be empty.


Thanks, Rolan

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim strDocName As String
Dim strLinkCriteria As String

If IsNull(Me!CustID) Then
MsgBox "Please click a Customer sale from the list."
Else

strDocName = "InvoiceDialog"
strLinkCriteria = "CustID = " & Me!CustID

DoCmd.OpenReport strDocName, acViewPreview, , strLinkCriteria

Me.Visible = False

End If

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Const conErrDoCmdCancelled = 2501
If (err = conErrDoCmdCancelled) Then
Resume Exit_Preview_Click
Else
MsgBox "Error in preview function."
Resume Exit_Preview_Click
End If

End Sub


Nov 13 '05 #3

P: n/a
Thanks Darryl and Salad for your suggestions. I will try them. Having
an invoice number was a consideration and would still like to
implement, however, due to the convoluted design and extensive elements
of the form, and the occasional need for users to change parts of the
invoice because of errors made or simply unknown changes, and in a
compressed time frame, I opted not to use a separate invoice and
invoice details table/queries.

So, what might be your suggestions to incorporate an auto-incrementing
invoice number when there is no related table auto-number to utilize?

Thanks
Salad wrote:
Rolan wrote:
I have tried many iterations of constructing an expanded string link criteria for a list box selection, but have not been successful. The List Box uses a Totals Select Query that has a CustID, Customer Name, Invoice Date and Invoice Amount. There is no InvoiceID/number,
otherwise it would be easy to resolve.

Currently, I must use a parameter query to key in the invoice date in order to preview/print an invoice from the Invoice Dialog Box. Of
course, this is not desirable. Any suggestions would be welcomed.
That doesn't appear too hard.

I'll point you to a few properties and you can read up on them in

online help.

With a listbox, you have a single select and multiselect. I suppose you are using a single select (Multiselect set to None) or simple/extended.
The properties to look at are ItemsSelected, Selected, ListCount (read up on that...the count is affected whether or not you have headers),
ItemData and ListIndex. And one more...Column

For Each var In Me.ListTools.ItemsSelected
datDate = Me.ListBoxName.Column(ColumnNum, var)
Next

Lets say you display 5 cols. The last col is the date field. Well,
then ColumnNum above would be 4. The first column is always 0.

So if you know the column, get the data from it. Since it appears you want to select 1 record, use ListIndex to get the RowCount (acts the
same way as the Column...starts at 0) and the Column num minues 1.

You might want to also check the list count before calling the report since the list box may be empty.


Thanks, Rolan

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim strDocName As String
Dim strLinkCriteria As String

If IsNull(Me!CustID) Then
MsgBox "Please click a Customer sale from the list."
Else

strDocName = "InvoiceDialog"
strLinkCriteria = "CustID = " & Me!CustID

DoCmd.OpenReport strDocName, acViewPreview, , strLinkCriteria

Me.Visible = False

End If

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Const conErrDoCmdCancelled = 2501
If (err = conErrDoCmdCancelled) Then
Resume Exit_Preview_Click
Else
MsgBox "Error in preview function."
Resume Exit_Preview_Click
End If

End Sub


Nov 13 '05 #4

P: n/a
Rolan wrote:
Thanks Darryl and Salad for your suggestions. I will try them. Having
an invoice number was a consideration and would still like to
implement, however, due to the convoluted design and extensive elements
of the form, and the occasional need for users to change parts of the
invoice because of errors made or simply unknown changes, and in a
compressed time frame, I opted not to use a separate invoice and
invoice details table/queries.

So, what might be your suggestions to incorporate an auto-incrementing
invoice number when there is no related table auto-number to utilize?
I am not sure what the question is/asks. Some folk use DMax() to get
the next highest number...ex
Me.NextNum = NZ(Dmax("[InvNum]","Invoices"),0)
It would be best to assign numbers like this in the BeforeUpdate event
of the form since if you assign it when a record is created...but not
saved...another person may come in to that screen and you both could end
up with the same number. If you don't use invnums, it's irrelevent.


Thanks
Salad wrote:
Rolan wrote:

I have tried many iterations of constructing an expanded string
link
criteria for a list box selection, but have not been successful.
The
List Box uses a Totals Select Query that has a CustID, Customer
Name,
Invoice Date and Invoice Amount. There is no InvoiceID/number,
otherwise it would be easy to resolve.

Currently, I must use a parameter query to key in the invoice date
in
order to preview/print an invoice from the Invoice Dialog Box. Of
course, this is not desirable. Any suggestions would be welcomed.


That doesn't appear too hard.

I'll point you to a few properties and you can read up on them in


online
help.

With a listbox, you have a single select and multiselect. I suppose


you
are using a single select (Multiselect set to None) or


simple/extended.
The properties to look at are ItemsSelected, Selected, ListCount


(read
up on that...the count is affected whether or not you have headers),
ItemData and ListIndex. And one more...Column

For Each var In Me.ListTools.ItemsSelected
datDate = Me.ListBoxName.Column(ColumnNum, var)
Next

Lets say you display 5 cols. The last col is the date field. Well,
then ColumnNum above would be 4. The first column is always 0.

So if you know the column, get the data from it. Since it appears


you
want to select 1 record, use ListIndex to get the RowCount (acts the
same way as the Column...starts at 0) and the Column num minues 1.

You might want to also check the list count before calling the report


since the list box may be empty.
Thanks, Rolan

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim strDocName As String
Dim strLinkCriteria As String

If IsNull(Me!CustID) Then
MsgBox "Please click a Customer sale from the list."
Else

strDocName = "InvoiceDialog"
strLinkCriteria = "CustID = " & Me!CustID

DoCmd.OpenReport strDocName, acViewPreview, , strLinkCriteria

Me.Visible = False

End If

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Const conErrDoCmdCancelled = 2501
If (err = conErrDoCmdCancelled) Then
Resume Exit_Preview_Click
Else
MsgBox "Error in preview function."
Resume Exit_Preview_Click
End If

End Sub


Nov 13 '05 #5

P: n/a
I have still been unsuccessful in making this work :-( I tried the
various themes for list boxes, but it seems to be an abstruse approach
in addressing the issue, and could not get it to work. Perhaps by
sharing the following points, a different direction can be taken.

I'm interested in using string link criteria or a variation thereof to
preview invoices from a dialog form list box. Actually, the one being
used is a derivative of the one that is in the Northwind Db to
preview/print invoices. Of course, if I had a InvoiceID number, then it
would be a simple process as shown in the code above, but I do not. The
specific identifying criteria is the CustomerID number and the Invoice
Date.

Currently, I must use a query parameter box for manually keying the
Invoice Date to preview the invoice that is selected from the dialog
box. I would like to know if it is possible to use multiple string link
criteria in this regard and how to construct it. I tried using the
BuildCriteria method, but with no success. Any suggestions will be
welcomed. Thanks.

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.