List Box Problems

  • Thread starter Thread starter Brandon Schultz
  • Start date Start date
B

Brandon Schultz

I have created a list box so that I can navigate through records. The
AfterUpdate event looks like this;
Me.RecordsetClone.FindFirst "[SOD_SalesOrderID] = '" & Me![lstSalesOrders] &
"'"
Me.Bookmark = Me.RecordsetClone.Bookmark

SOD_SalesOrderID is a primary key for the query the list pulls from but
there are duplicate SOD_SalesOrderID records for multiple line numbers
(SOD_SOLineNbr). So, although the list populates with the the records
whenever you select Sales Order line number 2, 3, 4 etc. it does not pull up
the records data in the corresponding text boxes I have on the form. Only
the first line number pulls in ok. I end up having to use the navigation
buttons on the bottom; Which I'd like to eliminate.

Any help would appreciative.

Brandon Schultz
 
I have created a list box so that I can navigate through records. The
AfterUpdate event looks like this;
Me.RecordsetClone.FindFirst "[SOD_SalesOrderID] = '" & Me![lstSalesOrders] &
"'"
Me.Bookmark = Me.RecordsetClone.Bookmark

SOD_SalesOrderID is a primary key for the query the list pulls from but
there are duplicate SOD_SalesOrderID records for multiple line numbers
(SOD_SOLineNbr). So, although the list populates with the the records
whenever you select Sales Order line number 2, 3, 4 etc. it does not pull up
the records data in the corresponding text boxes I have on the form. Only
the first line number pulls in ok. I end up having to use the navigation
buttons on the bottom; Which I'd like to eliminate.
Click to expand...

If I understand correctly, this search is for "detail" records related to Sales
Orders? If your listbox contains a column that includes the line number value
(and you could add that - just make the column containing that value a zero, nor
near zero, width), you should be able to specify criteria that includes the line
numbers as well:

'**************EXAMPLE START
Me.RecordsetClone.FindFirst "[SOD_SalesOrderID] = '" _
& Me![lstSalesOrders] & " And [SOD_SOLineNbr]=" _
& Me.[lstSalesOrders].Column(2)
'**************EXAMPLE END

You would need to replace the Column number in "Me.[lstSalesOrders].Column(2)"
with the column number containing the line number (my example references the 3rd
column, as combo and list box column indexes are zero-based).

If you are using Access 2000, or later, you can use the "FindFirst" method
against the forms "Recordset" directly and you won't need to bother with the
bookmark. The FindFirst (if successful, of course, and I presume the form's
recordsource to contain the same records as the list box's rowsource) will cause
the form's recordset to move directly to the desired record, making the desired
record the current record.

Was I on the right track for your setup?
 
Your solution is (I think) exactly what I need. However, I keep getting
"Syntax error in string in expression." I have looked up the syntax on MSDN
for RecordsetClone but it did not give example for this particular scenario.

Thanks again,

Brandon

Bruce M. Thompson said:
I have created a list box so that I can navigate through records. The
AfterUpdate event looks like this;
Me.RecordsetClone.FindFirst "[SOD_SalesOrderID] = '" & Me![lstSalesOrders] &
"'"
Me.Bookmark = Me.RecordsetClone.Bookmark

SOD_SalesOrderID is a primary key for the query the list pulls from but
there are duplicate SOD_SalesOrderID records for multiple line numbers
(SOD_SOLineNbr). So, although the list populates with the the records
whenever you select Sales Order line number 2, 3, 4 etc. it does not pull up
the records data in the corresponding text boxes I have on the form. Only
the first line number pulls in ok. I end up having to use the navigation
buttons on the bottom; Which I'd like to eliminate.
Click to expand...

If I understand correctly, this search is for "detail" records related to Sales
Orders? If your listbox contains a column that includes the line number value
(and you could add that - just make the column containing that value a zero, nor
near zero, width), you should be able to specify criteria that includes the line
numbers as well:

'**************EXAMPLE START
Me.RecordsetClone.FindFirst "[SOD_SalesOrderID] = '" _
& Me![lstSalesOrders] & " And [SOD_SOLineNbr]=" _
& Me.[lstSalesOrders].Column(2)
'**************EXAMPLE END

You would need to replace the Column number in "Me.[lstSalesOrders].Column(2)"
with the column number containing the line number (my example references the 3rd
column, as combo and list box column indexes are zero-based).

If you are using Access 2000, or later, you can use the "FindFirst" method
against the forms "Recordset" directly and you won't need to bother with the
bookmark. The FindFirst (if successful, of course, and I presume the form's
recordsource to contain the same records as the list box's rowsource) will cause
the form's recordset to move directly to the desired record, making the desired
record the current record.

Was I on the right track for your setup?
Click to expand...
 
Well, scratch that last post I just added the & "'" to the end of the
statement and the error went away. But now, (Using the bookmark) I can get
it to navigate through the records. My code for the book is:

Me.Bookmark = Me.RecordsetClone.Bookmark

I'm pretty sure this is correct but the form will not switch records. Any
ideas?

Thanks so much,

Brandon

Brandon Schultz said:
Your solution is (I think) exactly what I need. However, I keep getting
"Syntax error in string in expression." I have looked up the syntax on MSDN
for RecordsetClone but it did not give example for this particular scenario.

Thanks again,

Brandon

Bruce M. Thompson said:
I have created a list box so that I can navigate through records. The
AfterUpdate event looks like this;
Me.RecordsetClone.FindFirst "[SOD_SalesOrderID] = '" & Me![lstSalesOrders] &
"'"
Me.Bookmark = Me.RecordsetClone.Bookmark

SOD_SalesOrderID is a primary key for the query the list pulls from but
there are duplicate SOD_SalesOrderID records for multiple line numbers
(SOD_SOLineNbr). So, although the list populates with the the records
whenever you select Sales Order line number 2, 3, 4 etc. it does not pull up
the records data in the corresponding text boxes I have on the form. Only
the first line number pulls in ok. I end up having to use the navigation
buttons on the bottom; Which I'd like to eliminate.
Click to expand...

If I understand correctly, this search is for "detail" records related
Click to expand...
to
Sales
Orders? If your listbox contains a column that includes the line number value
(and you could add that - just make the column containing that value a zero, nor
near zero, width), you should be able to specify criteria that includes the line
numbers as well:

'**************EXAMPLE START
Me.RecordsetClone.FindFirst "[SOD_SalesOrderID] = '" _
& Me![lstSalesOrders] & " And [SOD_SOLineNbr]=" _
& Me.[lstSalesOrders].Column(2)
'**************EXAMPLE END

You would need to replace the Column number in "Me.[lstSalesOrders].Column(2)"
with the column number containing the line number (my example references the 3rd
column, as combo and list box column indexes are zero-based).

If you are using Access 2000, or later, you can use the "FindFirst" method
against the forms "Recordset" directly and you won't need to bother with the
bookmark. The FindFirst (if successful, of course, and I presume the form's
recordsource to contain the same records as the list box's rowsource)
Click to expand...
will
cause
the form's recordset to move directly to the desired record, making the desired
record the current record.

Was I on the right track for your setup?
Click to expand...
Click to expand...
 
Well, scratch that last post I just added the & "'" to the end of the
statement and the error went away.
Click to expand...

There was a missing single quote in my example. The corrected example is below:

'**************EXAMPLE START
Me.RecordsetClone.FindFirst "[SOD_SalesOrderID] = '" _
& Me![lstSalesOrders] & "' And [SOD_SOLineNbr]=" _
& Me.[lstSalesOrders].Column(2)
'**************EXAMPLE END

But now, (Using the bookmark) I can get
it to navigate through the records. My code for the book is:

Me.Bookmark = Me.RecordsetClone.Bookmark
Click to expand...

How do you know it is navigating through the recordsetclone's records if, as you
say below, the form is not moving to a new record?
I'm pretty sure this is correct but the form will not switch records. Any
ideas?
Click to expand...

It's not finding a record that matches your criteria. If your correction to the
code wasn't what I provided above, it would explain the problem. By the way, you
didn't say which version of Access you are using. I'll say this again - if you
are using Access 2000, or higher, you can use the "FindFirst" method of the
form's "Recordset" property and you won't need to worry about bookmarks.
 
But now, (Using the bookmark) I can get
it to navigate through the records. My code for the book is:

Me.Bookmark = Me.RecordsetClone.Bookmark
Click to expand...
How do you know it is navigating through the recordsetclone's records if, as you
say below, the form is not moving to a new record?
Click to expand...

I made a mistake: I meant to say "I can't get it to navigate through the
records."

I copied the example exactly and I get "Syntax error (missing operator) in
expression. If I drop the single quote and add & "'" to the end I get no
error message but I can browse records with the list. I am running Access
2000. The form is bound to a query.

Thanks for your patience.

Brandon



Bruce M. Thompson said:
Well, scratch that last post I just added the & "'" to the end of the
statement and the error went away.
Click to expand...

There was a missing single quote in my example. The corrected example is below:

'**************EXAMPLE START
Me.RecordsetClone.FindFirst "[SOD_SalesOrderID] = '" _
& Me![lstSalesOrders] & "' And [SOD_SOLineNbr]=" _
& Me.[lstSalesOrders].Column(2)
'**************EXAMPLE END

But now, (Using the bookmark) I can get
it to navigate through the records. My code for the book is:

Me.Bookmark = Me.RecordsetClone.Bookmark
Click to expand...

How do you know it is navigating through the recordsetclone's records if, as you
say below, the form is not moving to a new record?
I'm pretty sure this is correct but the form will not switch records. Any
ideas?
Click to expand...

It's not finding a record that matches your criteria. If your correction to the
code wasn't what I provided above, it would explain the problem. By the way, you
didn't say which version of Access you are using. I'll say this again - if you
are using Access 2000, or higher, you can use the "FindFirst" method of the
form's "Recordset" property and you won't need to worry about bookmarks.
Click to expand...
 
I copied the example exactly and I get "Syntax error (missing operator) in
expression. If I drop the single quote and add & "'" to the end I get no
error message but I can browse records with the list. I am running Access
2000. The form is bound to a query.
Click to expand...

Are you saying that it still isn't working? I can't tell you what is now wrong
if you don't post the code you are now using.
 
I found where I goofed up. It's working now. Thank you so much for your
help!

This has been a nightmare.

Thanks again,

Brandon Schultz
 
Back
Top