Data not being found - Jet Database Engine

[branston]

Hi. I have a form which shows a record with all the meeting notes that have been made about it in various meetings. I set it up with a before update event so that when something was added to a note the meeting number and item number populated automatically (Meeting number as max meeting number, and item number as max item number +1)

This worked fine, but when you went back to change a previous note it updated all the information - perfectly logical, but not what i want!

I thought if i put it in an if statement, saying if the item number box was blank, add the information, but if there was already a number in there, then do nothing, but it came up with this error:

"The microsoft Jet Database engine cannot find a record in the table "TblMeetingSetUp" with key matching fields "MeetingNumber"

I pressed help and it came up with the following: In a one-to-many relationship, you entered data on the "many" side for which there is no matching record on the "one" side.

But im asking it to find the max number from that table, so surely it must be there?!

The code im using is shown below, hopefully one of you will be able to find whats wrong!

Dim intMaxMeetNo As Integer
Dim intMaxItem As Integer
If IsNull(Me.ItemNo) Then
intMaxMeetNo = Nz(DMax("MeetingNo", "TblPvnMeetingSetUp"), 0)
Me.MeetingNo = intMaxMeetNo

intMaxItem = Nz(DMax("ItemNo", "TblPvnMeetingDetail", "[MeetingNo] =" & intMaxMeetNo), 0)
Me.ItemNo = intMaxItem + 1
Else
End If


Thank you

 

[MarkK]

The error occurs on which line?

 

[gemma-the-husky]

your error says MeetingNumber, but your code refers to MeetingNo - are you sure you posted everything correctly?

 

[branston]

Sorry Dave, the error does say MeetingNo.
I run the code through and it seems to think the ItemNo is not null, as it goes straight to the else statement.

This explains what is happening, but not why! Is my null statement ok?

 

[branston]

Ive just added an OR Me.ItemNo = 0 to the 1st part of the if statement and it works. Cant believe it - ive spent months trying to work this out!
Thanks!