I have an 'InList' problem

[FacilitiesCAD]

I am trying to help a friend with his database.
He currently has a way number (way) and a batch number(batch) the batch number needs to be unique but it is always similiar to some other batch number. He wants to pull a number from a list change it ie BID1111 to BID1112 and then make sure that BID1112 is not in the list. he can then make a new record with his way number and batch number and add it to the list. I can't seen to do this, any help would be appreciated.

 

[rdodge]

One way to do this is to create a Cloned recordset like:

Dim drsCurRS = Me.RecordsetClone

Then use either the Seek Method (Table Type Recordset) or one of the Find Methods (Dynaset or Snapshot Type Recordset) to see if the Batch ID is in the table. In this situation, you would rather use the Table Type Recordset cause a Dynaset or Snapshot Type Recordset could be filtered.

Note, this assumes that the table is bound to the Batch Table, but if it is not bound to the Batch Table, then you will need to create a new recordset like:

CurrentDB.OpenRecordset("BatchTable",dbOpenTable,dbSeeChanges,dbPessimistic)

This does assume that you have "Microsoft DAO 3.6 Object Library" checkmarked in the list of References as a valid reference library for the project (Tools>References within VBE window).

Either way, as long as the recordset type is Table Type, if the NoMatch property is returned as "False", then the Batch ID does not exist in the Batch Table. If the form is bound to the Batch Table, you can set the Cancel variable of the Form's BeforeUpdate event as follows:

If Not drsCurRS.NoMatch Then
Cancel = -1
End If

--OR--

If Not drsCurRS.NoMatch Then
Cancel = True
End If

and this cancel's the updating process of the bound form.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[FacilitiesCAD]

Thank you for the reply. It turns out he found another solution using a second table and Dlookup. I couldn't fully follow your soultion but it seems similiar. (We are using the 97 version and I think that added to the confussion.)

 

[rdodge]

AC97 uses DAO by default, so the part where I mentioned about DAO 3.6, that only apply to Access 2000 and later.

I wasn't sure on the details of the setup, so that may have also been why it may have been a little confusing as I could only give General type information.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.