Recordset viewing/editing in forms

[clarkcovey]

Hello,

I am trying to create a (normally simple) application, but not having a solid grasp on the concepts of recordsets, I find myself floundering. :banghead:

The application is serial number tracking for appliances.



The functionality needs to be able to:

1. Search for serial numbers using partial strings (i.e. serial # "T12395723K39" can be located by entering "3K3")
2. If the serial number does not already exist, allow for adding it.
3. If data was added erroneously, allow editing to correct it.

The first 2 points are complete but I am struggling with the last. After a user performs a search, I store the results of that search to a temporary table for easy viewing of applicable records.

To allow someone to edit, they simply double click on one of those records to open a seperate form and edit.

When I displayed the content of the temporary table in the form for editing, I found it was not updating the original table.

When I create a separate recordset, using the values of the record selected, the recordset appears to be empty, therefore not displaying the correct record for editing.

My main table name is "SerialNumbers". The code below is triggered when the user double clicks on the desired record to edit.

Any advice will be greatly appreciated as I am sure there is probably a much more effective and more simplistic approach to this!

Clark


Private Sub Claim_DblClick(Cancel As Integer)
'*********************************************************************
'* Subroutine to take values from search results for editing record. *
'*********************************************************************
'Initializing variables
Dim ClaimNo, ApplianceName, SerialNo, MakeName, ModelNo, sqlstr As String

' Store values from temporary table "serialSQL" to use for editing
ClaimNo = Claim
ApplianceName = Appliance
SerialNo = Serial
MakeName = Make
ModelNo = Model

'Establishing recordset
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

'Query primary table "SerialNumbers" using values from temporary search table
sqlstr = "SELECT * FROM SerialNumbers WHERE ClaimNumber = '" & ClaimNo & "' and SerialNumber = '" & SerialNo & "'"
Set rs = db.OpenRecordset(sqlstr)

'Test results of recordset
MsgBox ClaimNumber 'ERROR: Currently displays an empty message

'Activate form used for editing results
[Forms]![Serial Number Tracker]![serialEdit].SetFocus

'Update the current record on the form
[Forms]![Serial Number Tracker]![serialEdit].Requery

' END RESULT: The first record of "SerialNumbers" is displayed and can be edited, but it is the wrong record.

End Sub

 

[vbaInet]

1. If data was added erroneously, allow editing to correct it.

How do you determine erroneous data?

 

[clarkcovey]

(Trying this reply for the 4th time...the submit button on the reply with quote doesn't seem to work properly)

If the search results return a number that is perhaps only one digit or character off, the user will open the necessary documents (digital photographs of manufacturer data plates) and verify the information.

Should the user determine the entry was in error, they should be able to correct the entry.

 

[vbaInet]

If any user can edit records willy-nilly do you think it's even worth locking the form in the first place?
On the other hand if there were certain people that were allowed to edit the record then it would make sense.

 

[clarkcovey]

You have a point. I was assuming people would do the right thing... silly me!

I've removed that section and will build a separate and different form with access privileges.

 

[vbaInet]

You can still use the same form. Just run some checks on the current user and determine whether the lock/unlock button should be visible or enabled.

 

[clarkcovey]

You can still use the same form. Just run some checks on the current user and determine whether the lock/unlock button should be visible or enabled.

During the process of developing another (initially unrelated) application, I was able to extract the users' login from Windows and build accessibility into different form functions.

When I complete the new application, I'll revisit this one and add that functionality.

Thank you for the suggestion!