How can we pass result from SQL query to VBA code

[SachAccess]

Hi,

I have a table. One field in the table is serial number.
Serial number will be increased with every new entry.
At the moment am using 'MyMax = DMax("Serial_Number", "Tbl_Tool_Open_Log") + 1' line to get the number.

I have written an SQL query for the same task. Please see below.

SELECT MAX([Serial_Number]+1) AS NewNumnber
FROM Tbl_Tool_Open_Log;

My doubt is, how can we pass SQL query result to VBA.
I do not know how to do it. Can anyone please help me in this.

Sub MakeEntryInLogTable()
    Dim TblLog As DAO.Recordset
    Dim MyMax As Long
    
    '    DoCmd.OpenQuery "Q_MaxNmbrFrmLgTbl"'I want to pass result from the query to VBA code.
    Set TblLog = CurrentDb.OpenRecordset("SELECT * FROM [Tbl_Tool_Open_Log]")
    
    TblLog.AddNew
    On Error Resume Next
        MyMax = DMax("Serial_Number", "Tbl_Tool_Open_Log") + 1
    On Error GoTo 0
    
    If MyMax = 0 Then MyMax = 1
    TblLog![Serial_Number] = MyMax
    TblLog![User Name] = Trim(UCase(Environ("UserName")))
    TblLog![Opened Date] = Date & " - " & Time
    TblLog.Update
    TblLog.Close
    Set TblLog = Nothing
    DoCmd.Close
End Sub

 

[Minty]

The Dmax is doing exactly the same thing, but making the value easily accessible, why do you want to do it the harder more long-winded way?

 

[plog]

I don't understand. Your code works right? So, what needs to be fixed?

Technically you can load a recordset with the query you posted in your first paragraph, but why? You've loaded a query into the TblLog recordset, so you know how to do that. What am i missing?

 

[SachAccess]

The Dmax is doing exactly the same thing, but making the value easily accessible, why do you want to do it the harder more long-winded way?

Thanks for the help. I agree with you. Dmax is the more efficient way. I just used this as an example.
Since I do not know if we can get the query result the VBA code I thought I should seek help using above code as an example.

Have a nice day ahead.

 

[SachAccess]

Thanks for the help. Yes, I agree, the code does works. Have a nice day ahead.

I don't understand. Your code works right? So, what needs to be fixed?

Technically you can load a recordset with the query you posted in your first paragraph, but why? You've loaded a query into the TblLog recordset, so you know how to do that. What am i missing?

 

[MarkK]

To return a value from SQL to VBA you would open a recordset, like...

Function GetNextSerial() As Integer
    Const SQL As String = _
        "SELECT MAX(Serial_Number) " & _
        "FROM Tbl_Tool_Open_Log;"
        
    With CurrentDb.OpenRecordset(SQL)
        GetNextSerial = .Fields(0) + 1
        .Close
    End With
    
End Function

 

[Minty]

As Plog has mentioned - you know how to load a record set and retrieve values, the technique is identical to the one you have used further on in the code you posted.

Edit: as demonstrated by @MarkK in post #6 above.

 

[Pat Hartman]

The proper way to use the dMax() in this situation is:

MyMax = Nz(DMax("Serial_Number", "Tbl_Tool_Open_Log"), 0) + 1

Without the Nz() it won't work for the first row. Also, most of the time, these custom numbers are within some other field such as OrderID or perhaps Year(SaleDT).

Why are you using:
TblLog![Opened Date] = Date & " - " & Time
rather than
TblLog![Opened Date] =Now()

 

[SachAccess]

Thanks for the help @MarkK , @Minty and @Pat Hartman . Have a nice day ahead.

 

[Pat Hartman]

Did you change the way you were populating [Opened Date] ? Are you sure that [Opened Date] is defined as DateTime?

 

[SachAccess]

Did you change the way you were populating [Opened Date] ? Are you sure that [Opened Date] is defined as DateTime?

Hi @Pat Hartman , to be honest, I was totally occupied in other things yesterday.
Did not get time to work on this. I am yet to change it to DateTime.
At the moment it is 'Short Text'.
I will change it, thanks a lot for the help.

PS - Changed it to DateTime now.

 

[ADIGA88]

The way to access data (or pass data to VBA) is to use recordsets. it's the way VBA is designed to work with data but it's a bit unnecessarily complicated for non programmers.

For me, I am using the DAO (Data Access Object) model, other may use ADO (ActiveX Data Object).

An example in order:

Dim db as DAO.Database
Set db = Currentdb
Dim rs as DAO.Recordset
Set rs = db.OpenRecordset("Table or query name")

rs.Find "ID = 5" ' in case of Integer/long.
Dim result as String
result = rs("fieldName")

GoSub CleanUp
Exit Sub
CleanUp:   ' Error handling here is required this for simplicity
rs.close
Set rs = Nothing
Set db = Nothing

 

[SachAccess]

The way to access data (or pass data to VBA) is to use recordsets. it's the way VBA is designed to work with data but it's a bit unnecessarily complicated for non programmers.

For me, I am using the DAO (Data Access Object) model, other may use ADO (ActiveX Data Object).

An example in order:

Dim db as DAO.Database
Set db = Currentdb
Dim rs as DAO.Recordset
Set rs = db.OpenRecordset("Table or query name")

rs.Find "ID = 5" ' in case of Integer/long.
Dim result as String
result = rs("fieldName")

GoSub CleanUp
Exit Sub
CleanUp:   ' Error handling here is required this for simplicity
rs.close
Set rs = Nothing
Set db = Nothing

Thanks for the help @ADIGA88 . Have a nice day ahead.

 

[GPGeorge]

The way to access data (or pass data to VBA) is to use recordsets. it's the way VBA is designed to work with data but it's a bit unnecessarily complicated for non programmers.

For me, I am using the DAO (Data Access Object) model, other may use ADO (ActiveX Data Object).

An example in order:

Dim db as DAO.Database
Set db = Currentdb
Dim rs as DAO.Recordset
Set rs = db.OpenRecordset("Table or query name")

rs.Find "ID = 5" ' in case of Integer/long.
Dim result as String
result = rs("fieldName")

GoSub CleanUp
Exit Sub
CleanUp:   ' Error handling here is required this for simplicity
rs.close
Set rs = Nothing
Set db = Nothing

That's definitely, one way to pull a single value from a single field in a table. I'm not sure at all that most Access developers would consider it the way to do it. Pat's explanations are on point, so pay close attention to what she has to say.