Query answer into a text box in a form

Hi!
This is my first post here. I have a problem with a SQL query in the VB code for a button in a form in Access. Not good language...

Here's what I want to do:
Criteria 1:
I want to get a the highest number(High) from a list. Then add 1 to High and put it in a textbox/field in a form called frmInventory. This form is made from a table called tblIventory

Criteria 2:
High depends on another number(articlenr). articlenr is shown in frmInventory. What I want to do is to get the highest High related to articlenr.

I'm not sure if you understand what I mean. But here's my code and I guess every help is helpfull to me since I'm new to VB and SQL

You haven't done anything with your sql string. You need to execute the sql on a database connection to get a recordset returned with the results of your SELECT. Assuming an active connection con, something like


The WHERE will return a max number by inventory number, not overall. Leave out the WHERE if you want a max for the whole table.

Thaks for your help. After some research I found out that I have to execute the sql string. And I guess I have to create a connection to the access database. But how do I do that? This VB code is for a button in a Access form.

If you are using an ADO, you could try something like this:
'general declaration
Dim CN as ADODB.Connection
Dim RS as ADODB.Recordset

Then in your event Button1_Click() for example:
Set CN = CurrentProject.Connection
Set RS = New ADODB.Recordset

RS.Open "SELECT [maxIn] = MAX(InventoryNr) FROM sometable...", CN, adOpenDynamic

If Not (rs.EOF And rs.BOF) Then
Textbox1.Caption = rs!maxIn
End If

RS.Close

Of course you can do this any number of ways, this is just one. I hope it helps! This is the way I do it
if I want a result in a textbox.

Thanks alot.. That was very helpfull. It worked now.