GoTo Record

[ScrmingWhisprs]

For the library I work at, I designed a Shelflist Database. This keeps all of our items records that I export from our ILS (integrated library system). I have a form that I designed as a spine label generator. I have an unbound textbox where I scan a book barcode then an Append query takes that record from the Shelflist table to the Label Generator table, which is then listed in a listbox. Clicking on an item in the listbox will find the record on my form, which show more information at the bottom. Everyone should be familiar with it:

Private Sub lboLG_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![lboLG], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

What I would like to do is when the barcode is scanned, that record is what shows at the bottom, as if I clicked on the first item in the list. Currently, it only displays the first record in the table until I click on a list item.

Please let me know if you have further questions.

 

[DCrake]

Simple Software Solutions

Hi

Here is two approaches:


Me.Listbox.SetFocus
SendKeys "{DOWN}"

or

Me.ListBox.DefaultValue = Me.ListBox.ItemData(0)


Code Master:

 

[ScrmingWhisprs]

Thanks for your reply, DCrake, but neither of those did the trick.

The first one (SendKeys) made the form go a little berserk, and was caught in a loop that I couldn't stop unless I did Ctrl Alt Del.

The second one set the default value to the first record in the set, but the underlying table is sorted by the Call Number field, so after a barcode is scanned, it requeries the form, and defaults it to the first record alphabetically.

I tried to adapt this code that I found, but I get an error message saying "Run-time error 3464: Data type mismatch in criteria expression."

Dim rs As Object

Set rs = Me.Recordset.Clone
    rs.FindFirst "[BARCODE] = " & Me![tboBarcode]
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I don't know what I'm doing wrong!

ScrmingWhisprs

 

[DCrake]

Simple Software Solutions

Sorry about the last thread not working

So if I am reading you correctly you have a list box that contains a list of all the books in a Label table. You also have a set of books not in the label table. By Scanning the barcode it get the info from the sheflist table and copies the fields into the label gen table and displays them on screen.

However if you scan a code that already exists in the label table you want it to appear on screen with all the data populated from the label table and you also want your listbox to show the highlighted item in the list.

Lets assume the latter; after you have scanned the code a further chunk of code does something similar to :-

For x = 0 to Me.ListBox.ListCount -1

If me.ListBox.ItemData(x) = Code Then

me.ListBox.Selected(x) = True

Exit For

End If

Next

in essesence the above code loops through all the items in the listbox and compares the bound column to the barcode. When it finds a match it then selects that item in the listbox.

The only problem I have got with that is that if the item is the 1000th item then your list box only displays say 20 items the user will not see the highlighted item unless they scroll down the list box.


If my assumptions are incorrect please readvise

Code Master:

 

[ScrmingWhisprs]

When I scan a barcode, whether it's a duplicate barcode (if I were to need 2 of the same label) or not, that record goes to the top of the listbox because I have it descended sort on the label generator table's primary key. So each time I scan something in, I need to have that record "selected" per se from the listbox to display on the form.

I'm not sure if this answers your question about my question.

Thanks

 

[DCrake]

Simple Software Solutions

So your label table uses an autonumber as the PK and the list box has this table as its rowsource. by sorting descending it allways shows the most recent entry first.

And you want the list box to requery itself once a code has been scanned.

You need to base your listbox on a query not a table as tables do not have sorts they have indexes.

So you listbox rowsource will be Select * from Label order by PK DESC;

Then after you scan a record from the shelf list table and appended it to the label table call a Me.ListBox.Requery, followed by me.listbox.itemdata(0)

Code Master:

 

[DCrake]

Simple Software Solutions

Hi

Please find a working example of your request in the demo mdb.

Code Master:

 

[ScrmingWhisprs]

I have attached a brief version of my shelflist database, with just the label generator, so that you can see how it works.

Here are a few barcodes that you can test the label generator:

32075001506421
32075001506223
32075001506389
32075001506397
32075001506454
32075001506413

 

[DCrake]

Simple Software Solutions

Hi

Right got it working...

Step 1 remove the CmdSearch_Enter() code this is purely replication of the Click event

Step 2
place the following at the end of the cmdSearch_Click() Event

Me.lboLG = ""
Me.lboLG.SetFocus
Me.lboLG.Selected(1) = True

The last line was previously coded as Selected(0) where 0 (zero) is the column heading. By changing this to 1 it highlights the first item in the list box.

Hope this resovles it for you.

Code Master:

 

[ScrmingWhisprs]

Thanks a lot!! It works great!

The reason why I have the same code in the On Enter code of the button, is because when a barcode is scanned, the barcode scanner also includes a hard return, or "Enter". Therefore, I have set the textbox to tab to the next field in the tab order on "Enter", so that way when a barcode is scanned, it goes through the whole code and brings the focus back to the textbox so I can scan another barcode without touching the keyboard.


Thanks for your help!!!!

ScrmingWhisprs