Variable not assign when using query as form's record source.

[Dennis]

All,

I'm on XP Pro w SP3 using Access on XP Office Pro w SP3.

My computer data entry form's record source is a query which join the
customer and computer tables. The last two fields on the computer table is
AddDate and ChgDate. These fields are both set in the Form's Before Update
event using the following code.

If Me.NewRecord Then AddDate = Now() ' Date stamp the
records
ChgDate = Now()

Access is telling me that AddDate is an undefined variable. I know it is
not because I copied and pasted the names from the table defintion window to
the above code. I also checked the names in the query and they are spelled
the same.

I use the above code in another form and it works. The only difference I
can see is Form A's record source is a physical table and this form's record
source is a logical view of the customer and computer tables.

Any idea why I get this error?

 

[Allen Browne]

Suggestions:

1. Check the RecordSource of the form. Is this the table itself? Or is it a
query that does not return these fields?

2. If that does not solve the problem, put text boxes on your form for
AddDate and ChgDate. Set their Visible properties to No if you want to hide
them. (This may address a problem with the AccessField data type.)

3. If that does not solve it either, check that Name AutoCorrect is off, and
decompile. Details here:
http://allenbrowne.com/recover.html

 

[Dennis]

Allen, DStegon,

1. Check the RecordSource of the form. Is this the table itself? Or is it a
query that does not return these fields?
- RecordSource is the query. The table has the fields. The query has the
fields. I looked at the query in datasheet view and the fields were there.
- Before I posted this question, I copied and pasted the variable names from
the table definition to the code just to make sure I was not mistyping
something.

2. Put text boxes on your form for AddDate and ChgDate.
- That did not work.

3. AutoCorrect was off.
- Since it was already off, I did not decompile.


Then I notices that ME.NewRecord was not being compared to anything. So I
tried that.

The line " If Me.NewRecord Then AddDate = Now() " produces the "Variable not
assigned message".

The line "If Me.NewRecord = True Then AddDate = Now()" does NOT produce the
error message and it works! I did not retype the line, I just inserted "=
True"

I checked in the immediate window and Me.NewRecord is a boolean value. I
thought that you could say "If boolean then".

If I change the code back to "If Me.NewRecord Then AddDate = Now()" I get
the error message. If I put "= True" back it, it works.

Does anyone know why? I am doing something wrong in the first If? Do I
still need to decompile?

 

[Allen Browne]

Try:
If (Me.NewRecord) Then Me.AddDate = Now()

If that doesn't work, I suggest you do down the decompile path. Follow the
steps here:
http://allenbrowne.com/recover.html