Search in recordset

[oltroman]

Hi, I have written this code for a search in inventory using recordset. It does work but it got bug and sometimes return the wrong lines

Set RstInv = CurrentDb.OpenRecordset("Inventory")
With RstInv
    .GetRows ([StrBarcode])
    .MovePrevious
    .Edit
    .Fields("Job_No").Value = StrJob_No
    .Update
End With

StrBarcode is a double, that's equal to the field "ID" number of what i want to search. Let's say i want to search TST0001 and StrBarcode = 30 then

ID ------------------------- BARCODE
30 ------------------------- TST0001 (EXAMPLE)

Is there another way to do this?

 

[oltroman]

I tried to use .FindFirst but got some error. Can someone guide me how to use findfirst? Thanks

 

[boblarson]

1. Why are you moving back and then editing?

2. You are opening the recordset but not using a query with an ORDER BY which needs to be set otherwise you are going to not necessarily get the data in the same order each time. Access doesn't store the data in the table in the order it was input. It stores it in the first available location, which can mean that you can't rely on the order unless you apply an order in a query and use that query instead of the table.

3. The FindFirst is what you want not GetRows (as far as I know) but you don't even need that. You can use

"SELECT * FROM Inventory WHERE [ID] = " & dblID

By the way, ID should NOT be double. That can have serious consequences due to rounding errors which can cause it not to match with the actual INTEGER. So you should be using LONG INTEGER instead.

 

[MikeLeBen]

Also, it is good practice when working with recordsets, to check for EOF to make sure the recordset you're opening does contain any records.

Therefore:

If Not RstInv.EOF Then
RstInv.MoveFirst
'do stuff you need here
End If

 

[vbaInet]

Also, it is good practice when working with recordsets, to check for EOF to make sure the recordset you're opening does contain any records.

Therefore:

If Not RstInv.EOF Then
RstInv.MoveFirst
'do stuff you need here
End If

A more accurate approach is:

If Not (RsIntv.BOF and RsIntv.EOF) Then

Or use the RecordCount property and test for > 0

 

[LPurvis]

Hi all.

Random thoughts as they pass through my fingertips (having bypassed my brain of course!)

Unles otherwise specified, records should be returned ordered by the primary key - though it's true they aren't necessarily stored in that order (unless you've just compacted the database).

GetRows is indeed not what you want (you're returning an array there - and doing nothing with it...). As mentioned, even FindFirst isn't necessary if you're only looking for one record at a time.
(Though with recordset manipulation, learning about FindFirst et al isn't a bad place to start.)
You were possibly finding yourself fortunately positioned at the record you wanted after the GetRows method...
.FindFirst "FieldName = " & StrBarcode
But if this is a one shot deal - then do look at Bob's SQL suggestion. (But by all means learn to do both

As for the choice of datatype, I imagine you felt you had no choice. Barcode values could be outside the range of a Long Integer. You could go with Decimal instead, it doesn't suffer from comparison - but to be fair, you should be fine with Double for the purposes you're using it. (The version of Access you're using may play a part in your data type choice too.)
Failing all else, as you won't be performing arithmentic operations or comparisons on the value, you could use a Text field to hold the value. (That it looks numeric doesn't mean it has to be stored as such.)
I notice that you've even called the variable "StrBarcode". That must say something. ;-)

Checking for any returned rows before attempting navigation is important yes - unless your error handling is prepared for that eventuality.
(i.e. you're completely happy to get the hell out of that procedure if there aren't any matchine rows

Cheers

 

[oltroman]

Guys I appreciate all helps, I am using Access 2007, here's my full routine

Private Sub Command37_Click()

If Me.oBarcode = "" Then
MsgBox "Please Enter Barcode Input"
Exit Sub
End If

Dim StrBarcode As String
Dim Counter As Long
Dim StrJob_No As String

Dim RstInv As DAO.Recordset2
Dim RstMan As DAO.Recordset2


'Search and Edit Routine--------------------------------------
StrBarcode = Me.oBarcode
StrJob_No = Me.oJob_No

'Test -----------------------------------------------------------
'MsgBox "StrBarcode:" & StrBarcode

Set RstInv = CurrentDb.OpenRecordset("Inventory")
With RstInv
'    .GetRows ([StrBarcode])
'    .MovePrevious
    .FindFirst "Barcode = " & StrBarcode

    .Edit
    .Fields("Job_No").Value = StrJob_No
    Me.TPrice = Me.TPrice + .Fields("Price").Value
    .Update
End With

'Set Date routines--------------------------------------------

Counter = 19
With RstInv
    Do Until IsNull(.Fields(Counter).Value)
        Counter = Counter + 1
    Loop
    
    .Edit
    .Fields(Counter).Value = Me.oJob_No & ":" & Date
    .Update
End With
'Set Date routines--------------------------------------------

Set RstMan = CurrentDb.OpenRecordset("Manifest")
With RstMan
     .AddNew
     .Fields("Name").Value = RstInv.Fields("Name").Value
     .Fields("Barcode").Value = RstInv.Fields("Barcode").Value
     .Fields("Serial_No").Value = RstInv.Fields("Serial_No").Value
     .Fields("Price").Value = RstInv.Fields("Price").Value
     .Fields("Dimension").Value = RstInv.Fields("Dimension").Value
     .Fields("Weight").Value = RstInv.Fields("Weight").Value
     .Fields("Job_No").Value = RstInv.Fields("Job_No").Value
     .Fields("Condition").Value = RstInv.Fields("Condition").Value
     .Fields("Box_No").Value = Me.oBox_No.Value
     .Update
End With

If RstInv.Fields("Dimension").Value = "NA" Then
    Counter = 19
    'MsgBox "Then"
    Else
    RstMan.AddNew
    RstMan.Fields("Name").Value = "Dim: " & RstInv.Fields("Dimension").Value
    RstMan.Update
    RstMan.AddNew
    RstMan.Fields("Name").Value = RstInv.Fields("Weight").Value & "Kgs"
    RstMan.Update
    RstMan.AddNew
    RstMan.Fields("Name").Value = ""
    RstMan.Update
    Me.TWeight = Me.TWeight + RstInv.Fields("Weight").Value
    'MsgBox "Else"
End If


Set RstInv = Nothing
Set RstMan = Nothing
Set RstDate = Nothing
Me.oBarcode.Value = ""


End Sub

If you notice on

Set RstInv = CurrentDb.OpenRecordset("Inventory")
With RstInv
'    .GetRows ([StrBarcode])
'    .MovePrevious
    .FindFirst "Barcode = " & StrBarcode

    .Edit
    .Fields("Job_No").Value = StrJob_No
    Me.TPrice = Me.TPrice + .Fields("Price").Value
    .Update
End With

I am using FindFirst because of of multiple manipulation on record.

My field name is Barcode and StrBarcode is a string for example BCN0001, I got error

"Run-time error '3251'
Operation is not supported for this type of object"

PLS Help

 

[jdraw]

What is me.oBarcode?
You could also try
.FindFirst "Barcode = '" & StrBarcode & "'"

But that doesn't really address the 3251 error...

You could try some debug.print statements, and/or step through your code.

I have acc2003 so can not work with an accdb. If you post a copy in mdb format, I'd take a look.

 

[boblarson]

Okay, a few more questions.

1. Why are you taking records from one table and copying to another?

2. From what I see you could just write an update or append query to deal with this whole mess.

 

[Pat Hartman]

Unles otherwise specified, records should be returned ordered by the primary key - though it's true they aren't necessarily stored in that order (unless you've just compacted the database).

By definition tables are unordered sets. The only way to get records returned in a predictable order is to use a query with an order by.

When an Access database is compacted, Access copies all the records to the new database in primary key order but as soon as you start adding records or updating, you can no longer rely on physical order.

 

[oltroman]

Boblarson, this meant to be for equipment tracking program. Job number is stored in a table, and equipment in another, when ever an equipment is out, it will be sent to a location with certain job number, I need to attach that job number to that equipment and date as well. That's why it's done that way

Jdraw and LPurvis, I've tried all the .FindFirst method I could find in this forum including yours but seems it's not supported (really scrathing my head right now hehe)

 

[oltroman]

Jdraw
Forgot to mention, I only know access 2007, and in access 2007 Me.oBarcode is a way to call what ever inside a form. In this case a combo box named oBarcode instead of Forms!Inventory!oBarcode (I know I got the wrong order hehe but you got the idea right?)

 

[Pat Hartman]

I believe Bob mentioned that there is no need to search the recordset. Use a query that opens to the record you want. The query engine is much more efficient than searching through a recordset in memory.

 

[oltroman]

I used

"SELECT * FROM Inventory WHERE [ID] = " & dblID

and Got;

Compile Error,
Expected: line number or label or statement or end of statement

 

[oltroman]

Thanks for those who comments my thread I solved this one

    .MoveFirst
    Do Until .Fields("Barcode").Value = StrBarcode
    .MoveNext
    Loop

Cheers.. what a relief

 

[spikepl]

No you did not "solve it", but found another way for how not to do it!

"SELECT * FROM Inventory WHERE [ID] = " & dblID is not a statement by itself, but is the SQL you ought to put into

Set RstInv = CurrentDb.OpenRecordset("SELECT * FROM Inventory WHERE [ID] = " & dblID )

and then you have the right record right away, instead of searching.

Your .FindFirst error was due to the the fact that .FindFirst des not work with dbOpenTable, and dbOpenTable is assumed, when you say OpenRecordset("myTableName"), and the table is not linked. All this is in fact written in the documentation for the FindFirst method. Read the documentation when in trouble!

 

[oltroman]

Spikepl thanks for the explanation, yes, I found another way, but going to try select method

 

[LPurvis]

By definition tables are unordered sets. The only way to get records returned in a predictable order is to use a query with an order by.

When an Access database is compacted, Access copies all the records to the new database in primary key order but as soon as you start adding records or updating, you can no longer rely on physical order.

As far as I'm concerned, nothing I said is contradicted there.
I stand by what I said.
Note, I said records are returned. (Not stored.)

 

[LPurvis]

And to be clear, I wasn't advocating anything. Just stating eventualities.
Naturally, records wanted in a specific order should be requested in that order. (Even if they're wanted in primary key order.)