Bring up record if value entered in primary key is already entered

[Shinken]

When a user enters the value of an existing primary key into a form, I would like to display the existing record in a form, or allow the user to continue if the record key doesn't already exist.

I've tried some code using FindRecord but may not have the syntax correct. I've placed the recordkey into vRecKey and then used FindRecord = vRecKey but it doesn't seem to work, and the FindRecord documentation is somewhat incomplete.

Does anyone have a snippet of code that will do this? I'm sure it's a pretty common use.

Also, where should the code be placed? On Exit, On Lost Focus, or where?

Thanks,

Shin

 

[KenReay]

Hi

I normally do this in a combo box, but you could do it in a text box, I suugest putting the code in the after update event.

So cboKey is the unique id entered, code would be:

Me.REcordsetClone.FindFirst "UniqueId = " & cboKey

above is assuming UNiqueID is numeric, if it is a string:

Me.REcordsetClone.FindFirst "UniqueId = '" & cboKey & "'"

Hope this helps

Ken Reay

 

[Shinken]

Thanks for the response. It's much appreciated. However...

I tried this variation of the code in the form's primary key field as an After Update expression. Didn't work, so I tried it again in On Lost Focus. Still didn't work.

Dim vSID As Control
'set the the primary key field value [SID] into var vSID
Set vSID = [SID]
Me.RecordsetClone.FindFirst "SID = " & vSID

I've verified that the value of the primary key is being initialized into the vSID variable. After Me.Recordset...
the record with the key vSID is not displayed on the form. I then checked the values of other fields and they are all null, so it is apparent that the record with the primary key value vSID is not being found.

Any suggestions?

 

[KenReay]

Hi

Yes,

why are you declaring the variable containing the value of the key as an object, and using SET?

try instead

Dim lngSID As Long
'set the the primary key field value [SID] into var lngSID
lngSID = [SID]
Me.RecordsetClone.FindFirst "SID = " & lngSID

 

[Shinken]

Ken,

Thanks again. Until about 10 years ago I was a DBMS programmer (later called application developer, now called who knows what?) but haven't done any serious code writing since then. The office staff is using Excel for everything...and I more or less volunteered to learn Access/VBA and put something together. Access/VBA is a new dog to me, however, and so I spent the week-end playing around with it. The SQL and VBA coding is, for the most part, pretty straightforward, although there seems to be just enough difference from what I'm used to (dBase, FoxBase/FoxPro, etc.) to be very frustrating.

That was on Friday, and all things considered, it's kinda like riding a bicycle. However, as this project started spur of the moment, I didn't have time to get any reference books (I live in a rural part of the Oregon Coast) and, the MS Help being rather arcane, mostly had to wing it. I didn't fully understand the use of SET to initialize a variable, and this should explain my inappropriate use.

Anyway, what I'm trying to do is...when the user enters a value into a primary key field, if the value/key already exists, I want the form to display the record associated with that value/key.

Let's call the form I'm working on with this problem, FORM A. I've set up a different form (FORM B...associated with a related table) and can successfully call up FORM A with the correct record displayed, by entering a a FORM A key value in FORM B. Unfortunately, I haven't been able to
get FORM B to goto a record in the associated table and refresh the screen to display the data. I don't even think that the FindRecord or FindFirst functions are working, when I test to see what the current record is.

I've also thought of using the GoToRecord function, but until I get a reference book, I don't know how to ascertain the recordnumber...and recordnumber() doesn't seem to be usable in VBA.

Regards,

Shin

 

[KenReay]

HI Again

I too have been around long enough to have experienced DBase, Clipper etc.

First to to subject in hand, the following code snipit does what you are trying to do (via a combobox)

Private Sub cboSupplierId_AfterUpdate()
Dim Rs As Recordset
'
Set Rs = Me.RecordsetClone
Rs.FindFirst "supplierid = '" & cboSupplierId & "'"
If Rs.NoMatch Then
Else
Me.Bookmark = Rs.Bookmark
End If
Rs.Close
Set Rs = Nothing
End Sub

You need to be aware of a couple of things, first which version of Access are you using?, if you are using Access97 then the default database access method is DAO, the above code reflects this, if you are using A2K or AXP then the default data access method is ADO. You can still use DAO in theses later versions but you need to add the DAO library to the references collections (open any code module in desgin view and choose tools\references from the menu, you will see what I mean). The syntax of the two methods (DAO, ADO) is somewhat different, but since they both provide essentially the same functionality then as you might expect there are significant similarities. Ie there is a method to connect to your datasource, a method to instantiate a recordset, methods to move through a recordset etc, nothing you will find alien I am sure.

Another point I found a bit of a hurdle when switching from XBase etc, was that although you can still move about the data 'file' wise using findfirst, movenext etc, you should not neglect the use of the SQL statements as a means to restrict the recordset to the record(s) you are interested in. So we could have achieved the same result as above by settinging the forms recordsource to an SQL statement (Me.Recordsource = "SELECT * FROM tblName WHERE SupplierId = '" & cboSupplierId & "';&quot, but then we would be able to 'see' ONLY that record.

You mention RecNo() from DBase, the nearest equivalent which access has is the bookmark property, which will allow you to save the 'address' of a record and then return to it without searching etc, but beware Bookmark is invalidated if you requery/refersh the recordset, it is NOT an absolute record address, it is a temporary pointed to the record within the current recordset.

There is also a RecordCount property of the record set, which gives you the number of records in the recordset, but bewrae when using this, it is not always populated with the full number of records immediately, and it is safest to execute a movelast before relying on the value in the recordcount.

Finally (for now)if you want to buy a book, then there are many, but one you must buy is Access Developers Handbook , by Ken Getz and others, there are different versions (ie Access 97 etc) but it really is excellent.

Good luck

Regards

Ken Reay

 

[FrustratedPgmr]

If you are using Access2002 and attaching you tables via the form property - try the following

first create an unbound text box name it "SearchBox"

place the following code in the after update event of the "SearchBox"

This works by comparing the value you enter in the "SearchBox" against your primary field - this is why it only works with the table directly attached in the form data property. You can look up additional info on the Access Help screen. Good Luck![Gorgeous]

With CodeContextObject

DoCmd.GoToControl "Fieldname" 'Name of field Control
DoCmd.FindRecord .SearchBox, acEntire, False, _ acSearchAll, acCurrent, acCurrent, True
End With

 

[Shinken]

Ken,

Thanks for the info. FYI, I'm using A2K on my home machine and AXP on the office machine. I haven't run into any problems moving the files back and forth and working on them in both versions.

I found a copy of Access 2000 Bible, which seems good for an overview of the way Access works, but is pretty weak in SQL, functions, etc. I'll pick up a copy of the Developer's Hanbook...sounds like what I need.

If I may ask one more question, and then I'll leave you alone. Using 2 tables, where Table A has a one-to-many relationship with Table B, I'd like to display a few fields from A in a Form that is otherwise connected to B. I've tried setting up text boxes in A that use DLookup to display the fields in B, and it works fine if I use a combo box to select the record from B, but it won't update the values if I use the record selection buttons at the bottom of the screen (I've temporarily cheated by removing the buttons). Any suggestions about automatically refreshing the DLookup whenever the record in table A is changed?

I haven't tried your code snippet yet, but will do so later today.

Thanks much %-)


Also a note to FrustratedPgmr: I have been using an inputbox in much the manner you are suggesting, but think it's a bit kludgy. I'd really like to automatically bring up and display the record whenever an existing recordkey is inputted into the recordkey's field. It's more elegant.

Thx,

Shin

 

[Rolliee]

Send me an email and I will send you code for such a search.

rollie@bwsys.net

 

[KenReay]

Hi

Your TableA/TableB question.

If you need to fresh the DLookups, then do it in the ObCurrent Event of the form... but why do it that way at all, why not make a query (use Query Design grid) joining TAbleA to TableB and base your form on that query?

Regards

Ken Reay
Freelance Developer
kenneth.reay@talk21.com