I am using an input box to get a ID value for a query. On close of the input box a SQL statement is built using the ID in the where clause. This SQL statement is set to a list box's row source and the list box is required.
The problem is that the results are inconsistant. Using the same ID willnot always get all the results - sometimes it will. You can run it 2, 3,4 - 10 times it doesn't matter sometime it wil be a partial set sometimes the full set.
This is Acess 2000 on XP pro.
I can run the SQL statement manually and it always returns the full result set.
Any ideas???? I am at a loss.
Here is the code:
kitid = CLng(InputBox("Enter Kit Number to get history for:", "History Info"))
Set conn = CurrentProject.Connection
Set cmd.ActiveConnection = conn
rs.CursorLocation = adUseClient
frmsort.Visible = False
strsql = "Select * from source_history where kitid = " & kitid
cmd.CommandText = strsql
If rs.EOF Then
MsgBox "That kit does not exists.", vbOKOnly
strsql = "SELECT source_history.historyid, source_history.kitid, " & _
"customer.customername, Nuclide.name, source_history.SerialNumber, " & _
"source_history.WipeDate, source_history.AnalysisDate, source_history.kitaction, " & _
"source_history.actiondate " & _
"FROM (source_history INNER JOIN customer ON source_history.CustomerID = " & _
"customer.customerid) INNER JOIN Nuclide ON " & _
"source_history.nuclide = Nuclide.index " & _
"where kitid = " & kitid & " Order By actiondate"