populate field in form from vba sql

[Guest]

Hi

i wish to popupulate the primary index field of a form with the highest value plus one (to increment the index- similar to 'autonumber'). The code i'm using is

Private Sub Form_Activate(

DoCmd.GoToRecord , , acNewRe

Dim nObservationId As Lon
Dim ObservationId As Long (this is the name of the text box to be populated
Dim sSqlStr2 As Strin
Dim oCurrentDb As Databas
Dim oRecSet As Recordse
Dim oField As Fiel


'** Determine the new ObservationId by finding the greatest +
sSqlStr2 = "SELECT MAX(tbl_Observation.ObservationId) + 1 AS 'ObservationId' FROM tbl_Observation

'** Attach to the database and run the above quer
Set oCurrentDb = currentd
Set oRecSet = oCurrentDb.OpenRecordset(sSqlStr2

'** Take the value from the open recordset's first column and populate the ObservationI
Set oField = oRecSet.Fields(0
nObservationId = oField.Valu
ObservationId = nObservationI
Me.Refres

End Su

Problem is that when the form opens the field is unpopulated despite the correct value being known throughout the code, consequently get an 'invalid use of null' when saving the contents of the form
Any ideas

Ta.

 

[Marshall Barton]

i wish to popupulate the primary index field of a form with the highest value plus one (to increment the index- similar to 'autonumber'). The code i'm using is:

Private Sub Form_Activate()

DoCmd.GoToRecord , , acNewRec

Dim nObservationId As Long
Dim ObservationId As Long (this is the name of the text box to be populated)
Dim sSqlStr2 As String
Dim oCurrentDb As Database
Dim oRecSet As Recordset
Dim oField As Field


'** Determine the new ObservationId by finding the greatest + 1
sSqlStr2 = "SELECT MAX(tbl_Observation.ObservationId) + 1 AS 'ObservationId' FROM tbl_Observation"

'** Attach to the database and run the above query
Set oCurrentDb = currentdb
Set oRecSet = oCurrentDb.OpenRecordset(sSqlStr2)

'** Take the value from the open recordset's first column and populate the ObservationId
Set oField = oRecSet.Fields(0)
nObservationId = oField.Value
ObservationId = nObservationId
Me.Refresh

End Sub

Problem is that when the form opens the field is unpopulated despite the correct value being known throughout the code, consequently get an 'invalid use of null' when saving the contents of the form.

You've declared ObservationId as a Long variable in the
procedure so Access uses that instead of the text box. I
think you might get what you want if you just delect the
line with Dim ObservationId.

You should also close and dereference the object variables
before exiting the procedure.

I can't be sure if this all you need because you have
significantly more code than is necessary to do this job. I
believe you can do it this way and get the desired result:

Private Sub Form_Activate()
DoCmd.GoToRecord , , acNewRec

'** Determine the new ObservationId by
' finding the greatest + 1
Me.ObservationId = DMax("ObservationId", _
"tbl_Observation") + 1
Me.Refresh
End Sub

I do not understand why you are using the Activate event,
which can fire for other reasons than just opening the form.
You probably want to use the Load event instead.

Also, It's important that you understsand that there is a
chance of a conflict if you can have more than one user
doing this at the same time.

 

[Ted Allen]

Hi Alex,

The reason that the value is not being assigned to your
field is that your code is assigning it to a variable.
Naming a variable the same name as your form field does
not relate it to that field. Instead, to reference
controls on your forms you can use

Me![YourControlName]

Where you substitute the actual control name. The
brackets are not needed if the control name does not
include spaces.

But, I think that you could do the same thing by
replacing all of your code with (watch the wrapping):

Me!ObservationId = DMax
("[ObservationId]","tbl_Observation") + 1

However, keep in mind that this will only give you an ID
that is one greater than those existing in your table.
This is not the same functionality as Autonumber because
it will allow you to re-use numbers if records are
deleted and there are not any higher numbered records.

For many database applications this is not desireable,
but for some it is ok.

If you want to replicate the Autonumber functionality a
little more closely, you could have a simple table that
stores the last assigned ID. Then, when assigning a new
ID you would add 1 to generate the new field ID and you
would also update the ID in the reference table as well.

Hope that helps.

-Ted Allen

-----Original Message-----
Hi,

i wish to popupulate the primary index field of a form

with the highest value plus one (to increment the index-
similar to 'autonumber'). The code i'm using is:

Private Sub Form_Activate()

DoCmd.GoToRecord , , acNewRec

Dim nObservationId As Long
Dim ObservationId As Long (this is the name of the text box to be populated)
Dim sSqlStr2 As String
Dim oCurrentDb As Database
Dim oRecSet As Recordset
Dim oField As Field


'** Determine the new ObservationId by finding the greatest + 1
sSqlStr2 = "SELECT MAX

(tbl_Observation.ObservationId) + 1 AS 'ObservationId'
FROM tbl_Observation"

'** Attach to the database and run the above query
Set oCurrentDb = currentdb
Set oRecSet = oCurrentDb.OpenRecordset(sSqlStr2)

'** Take the value from the open recordset's first

column and populate the ObservationId

Set oField = oRecSet.Fields(0)
nObservationId = oField.Value
ObservationId = nObservationId
Me.Refresh

End Sub

Problem is that when the form opens the field is

unpopulated despite the correct value being known
throughout the code, consequently get an 'invalid use of
null' when saving the contents of the form.

 

[George Nicholson]

Try this:

get rid of the line: Dim ObservationId As Long (unnecessary)

instead of: ObservationId = nObservationId
(this only assigns the value of nObservationId to another variable and you
never do anything else with that variable. Nothing ever gets assigned to the
control itself.)

use: Me.ObservationId = nObservationId
(assigns the value of nObservationId to the textbox itself. "Me" represents
the form being activated.)
This assumes ObservationID is the name of the control. If the name of the
bound field is also ObservationID, you might want to change the name of the
textbox to txtObservationID to avoid confusing Access. If you do that then
use: Me.txtObservationId = nObservationId

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi,

i wish to popupulate the primary index field of a form with the highest

value plus one (to increment the index- similar to 'autonumber'). The code
i'm using is:

Private Sub Form_Activate()

DoCmd.GoToRecord , , acNewRec

Dim nObservationId As Long
Dim ObservationId As Long (this is the name of the text box to be populated)
Dim sSqlStr2 As String
Dim oCurrentDb As Database
Dim oRecSet As Recordset
Dim oField As Field


'** Determine the new ObservationId by finding the greatest + 1
sSqlStr2 = "SELECT MAX(tbl_Observation.ObservationId) + 1 AS

'ObservationId' FROM tbl_Observation"

'** Attach to the database and run the above query
Set oCurrentDb = currentdb
Set oRecSet = oCurrentDb.OpenRecordset(sSqlStr2)

'** Take the value from the open recordset's first column and populate the ObservationId
Set oField = oRecSet.Fields(0)
nObservationId = oField.Value
ObservationId = nObservationId
Me.Refresh

End Sub

Problem is that when the form opens the field is unpopulated despite the

correct value being known throughout the code, consequently get an 'invalid
use of null' when saving the contents of the form.