Context-sensitive Access Forms: Selecting one record From Many

[DenverBadger]

Problem: User enters data in an unbound field in a form to locate a record but the data isn't descriptive enough to isolate a single record. (Database is for repairing equipment. User enters a serial number, but there are more than one instances of that particular piece of equipment coming in for various repairs at various times.) The VBA procedure would like to present a list of the records found, allow the user to select the record desired, then continue processing.

Constraints: All Microsoft Office products are Office 2003. Operating System is Windows XP.

Problems with solutions already attempted:

1. VB script in the form (call it "Form1") opens a new form (call it "Form2") in datasheet mode with a filter applied to limit the records to just those few that match the limited information provided. Form2 includes an unbound checkmark against each record displayed which the user checks on the record they desire modified. The checkmark records the AutoKey value.
1A. The problem I've encountered is that the variable that Form2 assigns the AutoKey value to is either locked (because it was created in the procedure in Form1) or vanishes when Form2 closes (because it was created in the procedure in Form2), making it rather difficult to pass the value backwards to the calling form, Form1.

2. Instead of calling a secondary form (and triggering secondary procedures), Form1 actually has a subform embedded in it which has its "Visible" property set to false. The subform is a variation on the datasheet form which I called "Form2" in the first description. (Minor variations reflect the differences between being a free-standing form and being a sub-form.) The procedure sets the Visible property to True when it needs to ask the user to select the proper record.
2A. Now, the initial VBA procedure can access data and process it, but the problem is that I'm unable to apply a limiting filter to the subform, so it constantly lists all five-thousand-plus records, making it nearly useless for asking the user to select the correct record.

How do I solve this problem?

 

[dhookom]

You aren't really stating how you are applying a filter to the subform or any other form. I would probably allow the user to enter a search value and use it to filter the Row Source of a list box. When the user selects the appropriate record in the list box, it is displayed either in the current form or a subform on the main form.

Duane
Hook'D on Access
MS Access MVP

 

[DenverBadger]

The underlying tables are linked like this:

Main Table (actual name "RTRMA")
???Serial (Field lookup to "EQUIPMENT" table)
? ??? Nominclature (Field lookup to "PARTS" table)
???SiteName (Field lookup to "SITES" table)

The RTRMA table has six critical fields:
RMA: The Return Material Authorization number.
SC: The Service Call number
WO: The Work Order number
Serial: The serial number of the equipment needing repair
SiteName: The location to send replacement equipment to
AutoKey: Autonumber field to positively identify the record.

Note: It is impossible to positively identify the record by any one number that a user might have access to. Here is why:

RMA: This number is assigned by the manufacturer of the equipment, not the service help desk, and it takes roughly 24 hours from taking the trouble call before we receive this number from the manufacturer. While theoretically, RMA numbers do represent unique records, the time delay between creating the record and getting the number prevents us from making the RMA number field a unique field or a Primary Key field in Access.

SC: This number is created by the Help Desk software and is sometimes used by our warehouse to document multiple pieces of equipment being sent back to the manufacturer. For example, the warehouse might have a stack of old pieces of equipment on hand in unknown conditions. When they prepare the equipment for delivery to one of our sites, they discover a problem. When they have a dozen or so identical units all needing repair, they open a Service Call for the full dozen units. However, for tracking purposes, our tables have to have a separate entry for each unit. Thus, we would wind up with a dozen records all reflecting the same Service Call but with different Serial Numbers, and we can't depend on the Service Call number to identify unique records.

WO: Service Calls can reference work orders but they don't have to reference them. Therefore, in the above example, the warehouse might assign a separate work order to each device and then reference all dozen work orders in the same service call, but a technician taking a call from someone in the field might open a service call and not create a coorisponding work order. Therefore, we can't depend on Work Orders to identify unique records.

Serial: The same piece of equipment might go back for a broken power jack and six months later go back again for a bad motherboard and yet again six months after that for a broken screen, so we can't depend on Serial numbers to identify unique records.

Obviously, a site might send in multiple pieces of equipment, so we can't depend on the site number to reflect unique records.

All we are left with is the arbitrary AutoKey field to uniquely identify records, but users do not even know this number exists, let alone know how to find it or use it.

Therefore, when a user sits down to use our Access database, we present a screen that allows the user to enter the other five critical data elements. However, since not every record contains all five elements (the warehouse records, for example, would have blank SiteName fields), the data entry screen has to be able to accept empty elements. Thus, a user might enter just one element and then click "Find". Most of the time, that element will be the Service Call. Second most frequently entered data element might be the Serial number. Usually this will result in finding a single record and when it does, the VBA script will automatically progress to the next screen that allows editing of that record. But, when a search fails to isolate a single, unique record, then I want the VBA to present a list of the records it found, pause, allow the user to select the correct record from the list, then continue to the next screen where the record can be edited.

It is this pausing, then continuing, action that I'm having problems coding. While the VBA is running, the user is unable to select Access windows, let alone manipulate things or click buttons. If the VBA script stops to let the user interact, it has to know where to pick up again (instead of starting over). An INPUTBOX is the usual way to accomplish this PAUSE-THEN-CONTINUE dilemma but, in this case, how does one populate a list of records in an INPUTBOX, especially when the programmer can't know in advance how many records to display? Thus, I'm looking for an intuitive control element in a form to do the job.

I hope this explanation helps. If not, I can attach the code and table definitions I'm using.

 

[dhookom]

You still haven't provided any code or whatever that occurs after [blue]click "Find"[/blue].

I would use the user entered filter values to build a SQL statement to be used as the Row Source of a list box. If the list box returns multiple records, the user can review the column values and select the correct one. The list box is bound to the hidden autonumber field and is used to display an individual record.

Duane
Hook'D on Access
MS Access MVP

 

[DenverBadger]

Turns out, our office has MediaFire blocked so I can't upload my files. I took some screenshots and was intending to reference elements in the screenshots and identify where in my code those elements come into play. Without the screenshots, I'll try to describe what is what instead. Also, since I can't upload the code as a .txt file, I'll have to cut and paste what I want here in the body of this post.
------------------------------------------------------------------
First, my public variables.....
------------------------------------------------------------------
Option Compare Database
Public rstRMA, rstSh1, rstSh2, rstCust, rstEqu, rstParts, _
rstL1, rstL2, rstL3, rstP1, rstP2, rstP3, rstSol, rstRes As DAO.Recordset
Public conRMA, conSh1, conSh2, conCust, conEqu, conParts, _
conP1, conP2, conP3, conSol, conRes As DAO.Connection
Public tdfRMA, tdfSh1, tdfSh2, tdfCust, tdfEqu, tdfParts, _
tdfP1, tdfP2, tdfP3, tdfSol, tdfRes As DAO.TableDef
Public idxRMA, idxSh1, idxSh2, idxCust, idxEqu, idxParts, _
idxP1, idxP2, idxP3, idxSol, idxRes As Index
Public fldRMA, fldSh1, fldSh2, fldCust, fldEqu, fldParts, _
fldP1, fldP2, fldP3, fldSol, fldRes As DAO.Field
Public sqlRMA, sqlSh1, sqlSh2, sqlCust, sqlEqu, sqlParts, _
sqlP1, sqlP2, sqlP3, sqlSol, sqlRes, _
strValue1, strValue2, strRngA, strRng1, strRngA1, sAns As String
Public i, j, i1, i2, i3, ians, ians1, ians2, counter As Long
Public sNew As String
Public con, rs As Object
Public bTest1, bTest2, bTest3, bTest4, bAns As Boolean
Public objFSO1, objFSO2 As FileSystemObject
Public objFolder1 As Object, objFolder2 As Object
Public PathExists As Boolean
Public objFile1, objFile2 As File
Public objXL As New Excel.Application
Public XLrma, XLlookup As Excel.Workbook
Public shRMA, shRT As String
Public tStartLoop, tElapsedTime, tCurrent, tWatchStart, tWatch As Date
Public Lans As Long
------------------------------------------------------------------
Now the code in question....
------------------------------------------------------------------
Public Sub GetRecord_Click()
On Error GoTo Err_GetRecord_Click
Me!SelectOneFromMany_RTRMA.Visible = False

"SelectOneFromMany_RTRMA" is the subform which starts out hidden on the main form.

Me.Detail.Visible = True

Me.Detail.DisplayWhen = 2
Call FormInitialization

strValue1 = "SELECT * FROM RTRMA" & sqlRMA

"sqlRMA" was developed earlier as the user typed data into one or more of the five data fields for searching. It contains the variation of "WHERE" for selecting from one to all five of the fields based on whether the user entered data or left data blank.

Set rstRMA = CurrentDb.OpenRecordset(strValue1, dbOpenDynaset)

At this point, it is possible to have more than a single record in the recordset.

If rstRMA.BOF And rstRMA.EOF Then 'No record found!
ians = MsgBox("No Record Found!" & vbCr & vbCr & "Here is the SQL String I used:" & vbCr & vbCr & _
strValue1, vbOKOnly)
GoTo Exit_GetRecord_Click
End If
rstRMA.MoveFirst
rstRMA.MoveLast
Lans = rstRMA.RecordCount
If Lans > 1 Then

There is definately more than one record in the recordset. Now I need the user to isolate the specific record desired.

Me!SelectOneFromMany_RTRMA.Visible = True

The subform displays selected records in a dataset format. I don't know how to apply the sqlRMA filter to this subform because the parent form was open BEFORE the sqlRMA string was created and BEFORE the recordset was opened. The subform is tied to the parent form, not to the recordset. Remember, the primary form is a selection form for requesting the data that will be (has been, at this point) used to create the recordset.

If Me!SelectOneFromMany_RTRMA!RecordNumber > 0 Then rstRMA.FindFirst "AutoKey = " & CStr(Trim(Me!selectonefrommany!RecordNumber))

Turns out, the subform will ALWAYS display ALL the records in the RTRMA table and the !RecordNumber field will ALWAYS say "4857" (or however many records there are.) It is at this point that I'm not sure how to narrow anything down further.

Else
Me!SelectOneFromMany_RTRMA.Visible = False
Lans = rstRMA!AutoKey
End If
Me!StrawBackground.Visible = True
Me!Lbl1.Visible = True
Me!DisplayRMA.Visible = True
Me!DisplayRMA = rstRMA!RMA
Me!Lbl2.Visible = True
Me!DisplaySC.Visible = True
Me!DisplaySC = rstRMA!TICKET
Me!Lbl3.Visible = True
Me!DisplayWO.Visible = True
Me!DisplayWO = rstRMA!WO
Set rstDNS = CurrentDb.OpenRecordset("DNS2")
rstDNS.Index = "PrimaryKey"
rstDNS.Seek "=", rstRMA!DNS
Me!Lbl4.Visible = True
Me!DisplayDNS.Visible = True
Me!DisplayDNS = rstDNS!DNSLocation
rstDNS.Close
Set rstDNS = Nothing
Set rstEqu = CurrentDb.OpenRecordset("Equipment")
rstEqu.Index = "PrimaryKey"
rstEqu.Seek "=", rstRMA.Serial
strValue3 = CStr(Trim(rstRMA.Serial)) & ": " & rstEqu!Nominclature
Me!Lbl5.Visible = True
Me!DisplaySN.Visible = True
Me!DisplaySN = strValue3
rstEqu.Close
Set rstEqu = Nothing
Me!Lbl6.Visible = True
Me!DisplayMOB.Visible = True
Me!DisplayMOB = rstRMA!MOB
Me!Lbl7.Visible = True
Me!DisplayVan.Visible = True
Me!DisplayVan = rstRMA!Van
Me!Lbl8.Visible = True
Me!DisplayCreation.Visible = True
Me!DisplayCreation = rstRMA![DATE CREATED]
Me!Lbl9.Visible = True
Me!DisplayProbcode.Visible = True
Me!DisplayProbcode = rstRMA!Problems
Me!Lbl10.Visible = True
Me!DisplayProbText.Visible = True
Me!DisplayProbText = rstRMA!ProblemVerbage
Me!SC = rstRMA!TICKET
Me!WO = rstRMA!WO
Me!RMA = rstRMA!RMA
Me!SN = rstRMA!Serial
Me!AddRMA = rstRMA!RMA
Me!AddDNStrk = rstRMA![DNS TRACKING NUMBER]
Me!AddMSDtrk = rstRMA![MSD Tracking Number]
Me!Comments = rstRMA!Comment
Me!Status = rstRMA!Status

Exit_GetRecord_Click:
rstRMA.Close
Set rstRMA = Nothing
Call CollectiveUpdate

Me.Repaint
Exit Sub

Err_GetRecord_Click:
MsgBox Err.Description
Resume Exit_GetRecord_Click

End Sub

 

[dhookom]

I'm not at all sure why you need a recordset when you can use the sql statement as the record source of a form or row source of a list box.

I would display the final record in a subform that you can make visible as needed.

Duane
Hook'D on Access
MS Access MVP

 

[DenverBadger]

I think I've solved it. Here are the changes I made:
------------------------------------------------------------------
This is a snippet from the code I posted earlier. The main change is instead of calling my datasheet form as a subform, I set the value of "Lans" to the specific AutoKey value via a Function.
------------------------------------------------------------------
rstRMA.MoveFirst
rstRMA.MoveLast
Lans = rstRMA.RecordCount
If Lans > 1 Then
Lans = InStr(1, sqlRMA, "WHERE")
If more than one record found, modify the sql statement to remove the " WHERE " keyword at the beginning of the statement and the ";" at the end of the statement before passing it to the function.
s = Right(sqlRMA, Len(sqlRMA) - (Lans + 11))
s = Left(s, Len(s) - 1)
Lans = PickOneRecord(s)
Else
Lans = rstRMA!AutoKey
End If
------------------------------------------------------------------
Here is the new Function which accepts the sql as a string and passes the AutoKey value as a Long Integer.
------------------------------------------------------------------
Public Function PickOneRecord(MySQL As String) As Long
DoCmd.OpenForm "SelectOneFromMany_RTRMA", acFormDS, , MySQL, acFormEdit, acDialog

End Function
------------------------------------------------------------------
Finally, I pass the AutoKey from the correct record through this code assigned to the Close_Form event. Note that "SelectionTool" is a boolean field in the RTRMA Table. When the datasheet displays, every record has a checkbox next to it. Once the user finds the record desired and checks the box, then this script finds which record was selected, resets the field to "FALSE" for next time, and returns the AutoKey value for that record.
------------------------------------------------------------------
Private Sub Form_Close()
Me.Recordset.MoveFirst
While Not Me.Recordset.EOF
If SelectionTool Then 'This record is selected. Capture AutoKey number and end the WHILE
PickOneRecord = Me.Recordset!AutoKey
Me.Recordset.Edit
Me.Recordset!SelectionTool = False
Me.Recordset.Update
Me.Recordset.MoveLast
End If
Me.Recordset.MoveNext
Wend
If IsNull(PickOneRecord) Or PickOneRecord < 1 Then 'No checkmarks found
PickOneRecord = 0
End If

End Sub