Referencing a query value in a form field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have set up a query to give me the value of a new ID number which I then
need to place in a field when I insert a new record.

I wanted to put this in the BeforeInsert event but am obviously getting the
syntax incorrect as it gives me an error. Can someone help with this?

I was also hoping to go along the lines of using a command button to add a
new record but how do I reference a query along the lines of:

Me!PROBLEM_ID = {Query value}

Drew
 
What code do you have so far for running the query?

I have set up a query to give me the value of a new ID number which I then
need to place in a field when I insert a new record.

I wanted to put this in the BeforeInsert event but am obviously getting the
syntax incorrect as it gives me an error. Can someone help with this?

I was also hoping to go along the lines of using a command button to add a
new record but how do I reference a query along the lines of:

Me!PROBLEM_ID = {Query value}

Drew
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
It is -

SELECT Max(MCSMA_HC_PROBLEM.PROBLEM_ID+1) AS Expr1
FROM MCSMA_HC_PROBLEM;

This gives me the last ID number + 1
 
I meant how are you running this SQL. Do you use a DAO.RecordSet? DoCmd?

It is -

SELECT Max(MCSMA_HC_PROBLEM.PROBLEM_ID+1) AS Expr1
FROM MCSMA_HC_PROBLEM;

This gives me the last ID number + 1
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
I created the query using the 'New' button under the Queries tab on hte
Database console that opens when you open the database and then tweaked it.
 
Create a Function by putting the following in your code page:

#### Start of Code ####
Private Function NewProblem()
On Error GoTo Err_NewProblem

Dim MySQL As String
Dim MyRs As DAO.Recordset

MySQL = "SELECT Max(MCSMA_HC_PROBLEM.PROBLEM_ID+1) AS Expr1 " & _
"FROM MCSMA_HC_PROBLEM"

Set MyRs = CurrentDb().OpenRecordset(MySQL, dbOpenSnapshot, dbReadOnly)

If Not MyRs.EOF Then
NewProblem = MyRs![Expr1]
Else
NewProblem = 0
MsgBox "Unable to retrieve a new Problem Number!"
End If

Exit_NewProblem:
On Error Resume Next
Set MyRs = Nothing
Exit Function

Err_NewProblem:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume Exit_NewProblem

End Function
#### End of Code ####

Then where ever you want, you can get this number with:

Me!PROBLEM_ID = NewProblem()

Note: Unless you provide otherwise, this will always return the same number. It
must be incremented elsewhere.

You could also accomplish the same task with the Dmax Domain function:

Me!PROBLEM_ID = Dmax("[PROBLEM_ID]", "MCSMA_HC_PROBLEM") + 1

I just thought I'd give you the other code so you would have it.



I created the query using the 'New' button under the Queries tab on hte
Database console that opens when you open the database and then tweaked it.
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
Back
Top