Getting largest number from the table (1 Viewer)

[SachAccess]

Hi,

I am getting bug while trying to run below code in my FORM. Please note that I have changed names of Tables and Fields due to security reasons.
Task I am trying to achieve is get the maximum number of a field from the table.

Tbl_Vendor_Details has a column for Location_Id.
Location_Id are numbers.
Region_Id is field from Tbl_Vendor_Details.
Location_Id is different for each Region_Id.
Form_Frm_Dashboard.Cmb_Region.Value is showing correctly in the code (checked in IM window).

However am getting error at 'Set rs = DB.OpenRecordset(SqlStr, dbOpenDynaset)' this line.
Can anyone please help me in this. Thanks.

Private Sub Form_Load()
    Me.cmb_Recorded_By.Enabled = True
    Me.cmb_Recorded_By.SetFocus
    Me.cmb_Recorded_By.Text = Environ("UserName")
    Me.cmb_Recorded_By.Enabled = False
    Me.Cmb_Region_ID.Value = Form_Frm_Dashboard.Cmb_Region.Value
    
    If IsNull(Me.Tbx_Vendor_Id.Value) Then
            SqlStr = "Select max(Vendor_Id) as PrdId from Tbl_Vendor_Details Where Region_Id = " & Form_Frm_Dashboard.Cmb_Region.Value
            
            Set rs = DB.OpenRecordset(SqlStr, dbOpenDynaset)
            If IsNull(rs!PrdID) Then
                    NewID = 1
                Else
                    NewID = rs!PrdID + 1
            End If
            rs.AddNew
        Else
            msg = MsgBox("Do you want to update the record?", vbYesNo + vbExclamation, "Please Specify :")
            If msg = vbNo Then rs.Close: Exit Sub
            rs.Close
            SqlStr = "Select * from Tbl_Vendor_Details where Vendor_Id = " & Me.Tbx_Vendor_Id.Value & ";"
            'SqlStr = "Select * from Tbl_Product_Category where Product_Category_Id = " & Me.Tbx_Product_Category_Id.Value & ";"
            Set rs = DB.OpenRecordset(SqlStr, dbOpenDynaset)
            rs.Edit
    End If
End Sub

 

[June7]

Exactly what error message?

Where is DB variable declared and set?

Should declare all variables at beginning of procedure (if not declared as global at top of module or in another procedure). Should include Option Explicit line at top of every module. Can set VBA editor to automatically include in new modules.

Could use DMax() function instead of opening a recordset.

 

[SachAccess]

Hi @June7 sir, thanks lot for the help. Please give me some time to reply. Have a nice day ahead.

 

[oleronesoftwares]

try
Set rs = DB.OpenRecordset("SqlStr", dbOpenDynaset)

 

[SachAccess]

Hi @June7 sir, am getting error as Run-Time error 424
Object required

At below line.
Set rs = DB.OpenRecordset(SqlStr, dbOpenDynaset)

 

[SachAccess]

try
Set rs = DB.OpenRecordset("SqlStr", dbOpenDynaset)

Hi @oleronesoftwares sir, thanks for the help. Getting same issue with this change too.
Have a nice day ahead.

 

[oleronesoftwares]

Can you state what the error message is ?

 

[June7]

@oleronesoftwares, error was stated as "Object Required" in post 5.
Possibly because the DB object variable is not declared and set.

Dim DB As DAO.Database
Set DB = CurrentDb()

.AddNew and .Edit must precede code setting field values and then .Update must follow setting field values.

 

[SachAccess]

At the moment getting result with DMAX, will keep updated, thanks @June7 sir, checking if DMAX will alter the result in any way.

 

[June7]

After I suggested DMax(), I noticed code attempts to edit the recordset to add record. However, cannot add a record to an aggregated dataset. Could instead execute an INSERT action SQL to create record in table.

Then code attempts to open another recordset for user edit. How do you expect user to edit recordset? Should open a form filtered to the new record.

 

[SachAccess]

Hi @June7 sir, Set DB = CurrentDb() resolved the issue, you are correct, I decided not to use DMAX now.
My apologies sir, am new to Access, please give me some time to revert to your latest post, trying to understand in details.
Thanks.