Display a RecordSet for User Selection

LarryDeLaruelle · Jun 21, 2000

THE SITUATION:  I have a supplemental database which records and reports information on new admissions.  Records are identified by a primary key “ClientID” and a secondary (indexed, duplicates allowed) “BillingID”.  ClientID is an Autonumber field used as the main linking field between tables and is "behind the scenes" -- users would not know what a client's ClientID is. The source documents, because of confidentiallity, are identified only by the BillingID and Completion Date (which usually is on or close to the Admission Date).  I am retrieving the ClientID and other identifier information from the main database based on the BillingID. Clients can have multiple admissions each of which generates a unique ClientID but the BillingID is reused. THE PROBLEM:   Because the BillingID is the field used in the SQL statement to retrieve the relevant information, I need a way for the user to associate the correct ClientID (based on the Admission Date being equal to or near the Completion Date of the source document) where a client has more than one admission.   What I would like to do, unless you have another approach, is to display a message indicating that multiple records exist on the BillingID and a list of Admission Dates and prompt the user to select the appropriate date.  I could then set the pointer to the correct record to retrieve the information I need. Here is my current code: Private Sub txtOakLawnNum_AfterUpdate() ‘Oaklawn Number is the BillingID On Error GoTo err_txtOaklawnNum_afterupdate     Dim dbcurrent As Database     Dim rsTemp As Recordset     Dim strSQL As String     Dim strOak As String     strOak = txtOaklawnNum     Set dbcurrent = CurrentDb()     strSQL = "SELECT ClientID, OaklawnNum, FullName, AdmissionDate " _             & "FROM client " _             & "WHERE OaklawnNum = '" & strOak & "';"     Set rsTemp = dbcurrent.OpenRecordset(strSQL)     rsTemp.MoveLast     rsTemp.MoveFirst HERE IS WHERE I NEED SOME CODE:     If rsTemp.RecordCount > 1 then Display some message to the user Prompt the user for record Selection    End IF THEN I WOULD SET THE RECORD POINTER BASED ON THE SELECTION     txtFullName = rsTemp.Fields(2)     txtAdmissionDate = rsTemp.Fields(3)     ClientID = rsTemp.Fields(0)          rsTemp.Close Is there a way to display a recordset in this type of situation to allow the user to make a selection? Is there a better way to accomplish what I am trying to do? Thanks much. Larry De Laruelle <a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a> <a href= > </a>

Chopper · Jun 21, 2000

Larry, I have a suggestion that might help.   On the same form, have a hidden list box.  If you get multiple client id's, set the listbox to visible, build the list box with the results of the recorset admission dates.  Display your message.  When the user closes the message and double-clicks on an admission date, set the double-click event to trigger a new recordset that queries the table based on the admission date, and the billing id and Name already entered in the form. What it boils down to is that you can build a list box (choose value list in the row source type) with the values of the recordset: dim strDates as string Do while not rsTemp.eof     if strDates <> "" then         strDates = strDates & ";"     endif     strDates = strDates & """" & rsTemp!AdmissionDate & """"     if not rsTemp.eof then         rsTemp.movenext     end if loop me.lstDates.rowsource = strDates      Now, the rowsource for the list box contains the admission dates from the recordset.  And when the user selects a date, you can kick off a new recordset using their selection.  You could also do the same with a combo box to save vertical space. I hope this helps. -Chopper <A HREF="mailto:kenneth.mai@exch.compass-usa.com">kenneth.mai@exch.compass-usa.com</A>

LarryDeLaruelle · Jun 21, 2000

Chopper: The list box tip is a great idea.  It works just fine except for one small problem. The list box returns its value as a string and I don't know how to convert that to a date format for the new SQL string. Everything I've tried so far has resulted a variable mismatch error. Do I need to set up a property in the list box or is there a way to modify the returned string to a date format? Thanks much.   Larry De Laruelle <a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a> <a href= > </a>

Chopper · Jun 22, 2000

Larry, when you write the sql string and refer to the date selected, use the # character around the date, as in: ... WHERE Admission Date = #" & me.lstDates.itemselected & "#;" I believe this is the correct format for using a date in a sql string.  You may wish to use the format statement inline to ensure you have the correct format, though I don't think you will need it. I hope this helps, Chopper

LarryDeLaruelle · Jun 22, 2000

Yeah, I finally figured that out late yesterday.  I was trying to get to fancy and assigning the returned value to a variable.   Now it works great.  Thanks again for the tip. Larry De Laruelle <a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a> <a href= > </a>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Display a RecordSet for User Selection

LarryDeLaruelle

Technical User

Chopper

Programmer

LarryDeLaruelle

Technical User

Chopper

Programmer

LarryDeLaruelle

Technical User

Similar threads

Part and Inventory Search

Sponsor