Variable is not defined?

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi,
Here is the thing:

With rs
If Not (.BOF And .EOF) Then
CountOfOrderID = CountOfOrderID + 1
.Update
Else ' there is a new insertion
.AddNew
CountOfOrderID = 1
PickupDate = !PickupDate
.Update
End If
End With

on CountOfOrderID it says - "variable is not defined"
I don't have to define it. It is in the recordset and
there IS such a field name in the table rs is connected to.

What is wrong? thank you for you help.
 
Steve said:
Hi,
Here is the thing:

With rs
If Not (.BOF And .EOF) Then
CountOfOrderID = CountOfOrderID + 1
.Update
Else ' there is a new insertion
.AddNew
CountOfOrderID = 1
PickupDate = !PickupDate
.Update
End If
End With

on CountOfOrderID it says - "variable is not defined"
I don't have to define it. It is in the recordset and
there IS such a field name in the table rs is connected to.

What is wrong? thank you for you help.
Click to expand...

You haven't told VBA that "CountOfOrderID" is a field in the recordset,
so as far as it knows this is supposed to be a variable, but isn't
defined. You need to put a bang (!) in front of the name, to indicate
that it's a member of the Fields collection of the rs recordset:

!CountOfOrderID = !CountOfOrderID + 1
....
!CountOfOrderID = 1

I don't understand this line, though:
PickupDate = !PickupDate
Click to expand...

Here you have one unqualified reference to something named "PickupDate",
and one bang-qualified reference. "!PickupDate" would be interpreted as
a reference to a field in recordset rs, but since this is a new record
being inserted I'm guessing that you are trying to assign a value from a
control on your form to a field in the recordset. To do that you would
want to write:

!PickupDate = Me!PickupDate

Here you have two different bang-qualified references. The first, since
it doesn't specify the base object, will be taken to be referring to the
"With" object, which is the recordset rs. The second is explicitly
based on the Me object, which is a reference to the form on which the
code is running, and hence Me!PickupDate is assumed to be a control in
that form's Controls collection.
 
Thank you Dirk.
-----Original Message-----


You haven't told VBA that "CountOfOrderID" is a field in the recordset,
so as far as it knows this is supposed to be a variable, but isn't
defined. You need to put a bang (!) in front of the name, to indicate
that it's a member of the Fields collection of the rs recordset:

!CountOfOrderID = !CountOfOrderID + 1
....
!CountOfOrderID = 1

I don't understand this line, though:


Here you have one unqualified reference to something named "PickupDate",
and one bang-qualified reference. "!PickupDate" would be interpreted as
a reference to a field in recordset rs, but since this is a new record
being inserted I'm guessing that you are trying to assign a value from a
control on your form to a field in the recordset. To do that you would
want to write:

!PickupDate = Me!PickupDate

Here you have two different bang-qualified references. The first, since
it doesn't specify the base object, will be taken to be referring to the
"With" object, which is the recordset rs. The second is explicitly
based on the Me object, which is a reference to the form on which the
code is running, and hence Me!PickupDate is assumed to be a control in
that form's Controls collection.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Back
Top