Change of thought, I just find out that I can't use the query I created in Access. I need to hard code it in vba. I have try the code below and can't seem to run the query string I created in vba. I also need to retrieve the query result as I mention above post but in a different way. Is my synatx correct for the query string?
Private Sub Command0_Click()
Dim conndb As ADODB.Connection
Dim rsmwcas As ADODB.Recordset
Dim rsmwpat as ADODB.Recordset
Dim rsCaseNumber As ADODB.Recordset
Dim MaxCaseNumber As String
Set conndb = CurrentProject.Connection
Set rsmwcas = New ADODB.Recordset
Set rsCaseNumber = New ADODB.Recordset
Set rsmwpat = New ADODB.Recordset
strSQL = "Select [Case Number] From rsmwcas Where [Chart Number] = "' & PatChart & "';"
docmd.runsql strSQL, True
MaxCaseNumber = rsmwcas.Fields("Case Number")
Thank you dynamictiger for helping me out. I greatly thankful for it.
I get an error "A RunSQL action requires an argument consisting an SQL statement." when I use the docmd.runsql strSQL, True
Before I have try it in a different way, I try your method above:
MaxCaseNumber = rsmwcas.Fields("Case Number") and I get an error as: "Item cannot be found in the collection corresponding to the requested name or ordinal."
strSQL = "Select * From mwcas Where [Chart Number] = '" & PatChart & "';"
rsmaxcase.Open strSQL, conndb
MaxCaseNumber = rsmaxcase("Case Number")
But I need to get the Max or Highest number for the case number field. I have try this and got an error for it: "Item can't be found in the cannot be found in the collection corresponding to the requested name or ordinal."
strSQL = "Select Max([Case Number]) as MaxCaseNumber From mwcas Where [Chart Number] = '" & PatChart & "';"
Ok, I'm getting closer now. I was able to retrieve the high number from the query. When I try to filter it out, it can't the value in the table. The table with the case number is an autoincrement field. When I filter it in the Access table, it would show up a record for it but when I do a filter in vba, it can't find the record for the value I obtain from the previous coding.
When I do a query in Access, it would show one record with a case number of 28 and the other is 38 but when I do a filter in the Access table for that particular person it would show the case number 28 for both records. Why is that? I'm confused.