Null error after deleting data and compacting database

[Acipenser]

I had gotten my database working the way I wanted, so I cleared out all the data I used to test the database. I compacted the database to get all the autonumbers to reset. Now I am getting a null error in 2 fields on a form. The fields are supposed to be populated by the following code:

Dim stDocName As String
Dim stLinkCriteria As String
Dim Counter As String

stDocName = "frmCane2"
stLinkCriteria = "[CanisterID]=" & Me![cboCanister]
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acLast
Counter = Forms![frmCane2]![SampleID] + 1
DoCmd.Close acForm, "frmCane2", acSaveNo

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Forms![frmCane2]![CanisterID] = Forms![frmCanisterSelect]![cboCanister]
Forms![frmCane2]![SampleID] = Counter
DoCmd.Close acForm, "frmCanisterSelect", acSaveNo

The code is on the On Click Event for the button on frmCanisterSelect which opens frmCane2. This code is supposed to get the CanisterID from a selection made on the frmCanisterSelect, and then get the next number for a SampleID from tblCane. Niether CanisterId or SampleID are getting populated on frmCane2. I assume it is because there are no records in tblCane, so it can't find the last record, but I don't know how to fix it without putting in a bogus record.

Any help would be appreciated,

 

[psemianonymous]

Your problem is indeed that your program can't handle having no records to go to. The quick fix is to surround your textbox with Nz() to make the problem go away, but in the long term you're going to have to think of another way to generate an ID number (i.e. run a query and find the Max([ID]) ).

Dim stDocName As String
Dim stLinkCriteria As String
Dim Counter As String

stDocName = "frmCane2"
stLinkCriteria = "[CanisterID]=" & Me![cboCanister]
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acLast
Counter = Nz(Forms![frmCane2]![SampleID], 0) + 1
DoCmd.Close acForm, "frmCane2", acSaveNo

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Forms![frmCane2]![CanisterID] = Forms![frmCanisterSelect]![cboCanister]
Forms![frmCane2]![SampleID] = Counter
DoCmd.Close acForm, "frmCanisterSelect", acSaveNo


--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Acipenser]

Thanks