MS Access - how to store a query value in a variable

I need to store a query value ( this is a max(fieldname) from a table) into a global variable. the field name is an autonumber in a table. On a form i have a textbox and I want the value of that textbox to be my global variable. then i want to create a query with a new field, and the field to be the valule of my textbox. How can I do this in VBA? I appreciate all of your assistance.

I'm using MS Access 2007 on a Windows XP machine.

Welcome to VBForums :wave:

Thread moved from 'Other Programming Languages' forum to the 'Office Development/VBA' forum


First of all create the variable at the top of a module using the keyword Public, eg:

Code:

---

Public pMyVal as Long

---

Then add code somewhere to use a recordset to set the value based on a query, eg:

Code:

---

Dim objRS as ADODB.Recordset
  Set objRS = New ADODB.Recordset
  objRS.Open "SELECT Max(field) FROM table", CurrentProject.Connection
  If Not objRS.EOF Then
    pMyVal = objRS.Fields(0).Value
  End If
  objRS.Close
  Set objRS = Nothing

---

Thank you for taking the time to post this code. Unfortunatelly, I'm getting an error: User-defined type not defined. I pasted your code in the Private Sub Form_Load() event. The debbuger highlights the form and objRS As ADODB.Recordset.

Also, can you tell me how in VB on Form_Load you can assign this global value to the textbox.value on the form?

I much appreciate it.

The error is a bit strange, you should have that reference enabled by default... however, the "old style" DAO should work:

Code:

---

Dim objRS as DAO.Recordset
  Set objRS = New DAO.Recordset
  CurrentDb.OpenRecordset("SELECT Max(field) FROM table")
  If Not objRS.EOF Then
    pMyVal = objRS.Fields(0).Value
  End If
  objRS.Close
  Set objRS = Nothing

---

Quote:

---

Also, can you tell me how in VB on Form_Load you can assign this global value to the textbox.value on the form?

---

Like this:

Code:

---

textbox.value = pMyVal

---