Error on inserting new record in form

I created a small database for document tracking. There are several
tables, some of which are for lookups. I am experiencing two problems:

(1) in one case, I have a lookup for a Project table on the form that is a
combo box. The projects table is linked via a project id from the document
table. When creating a new document record, the user selects a project from the box.
There is also an "Office" combo box that must be selected which references an Offices table.
When creating a new record, the project box works, but the office box doesn't, in that upon
the commit, the office id field is evidentally "null" so the new record doesn't get committed.

(2) the other error has to do with automatically calculating a document number. The doc num
field of the Documents table should increment within a Project and Office location. Therefore, For
Project 1, Office Q, we have document number 1, 2,3,4... I'd like to automatically fill in the document
number field based on the Project and Office Selected. I have a query that appears to generate the correct
number using the Max function. However, I can't seem to get it to work in the form, even if I set the default
value property to the result of the query +1.

Help!

TIA,

Dave

It sounds like you have a project table that has one to many relationship to the Document table. However it appears, and I could be wrong that you have the document table set as a one to one relationship to the Office table. If that be the case unless you enter the ID number from the document table into the Office table records, the Office table will have a null value for the ID. The simpliest method to fix this, is to set the Office ID default value to the Document ID. Whenever a new document is opened it will automatically place that ID number in the Office ID field so new records can be added using the combobox.

I am not sure I understood the second question properly and maybe it is because the relationships are not set up as I think above. But will an autonumber for the document table work in this particular set up you are using. If not tell us how you have the current relationships set up between the three tables and we can figure it out.

lwells

Problem domain is generating document numbers and tracking documents for multiple projects and offices. The primary tables of interest are Document, Copy, Disposition(where is the copy now). There are lookup tables for Projects and Offices. There is a 1-many between Projects and Documents, and 1-many between Offices and Documents. There is a 1-many between Document and Copy, and 1-many between Copy and Disposition.

The Projects and Offices tables each have an "ID" field which is the link with the Document table. Access put that in automatically when I created the Document table and specified a "lookup" for the value.

As for the problem calculating the new document number, the document table has the following fields:

Project(id)
Office(id)
Number (int)
Title
Author
Source
DocumentID

In the form for data entry, I want to automatically calculate the next Number for the document where the project and office are what the user selected on the form (in the combo boxes). So, I have a query which does a select Max(document.number) from document where project=forms!...and office=forms!.... and then add 1 to it. If I run that query by itself (not in the form) and enter the project id, and office id, it returns the correct value. However, if I put that query in the Default value property for the document number field, or the "got focus" event, it either does nothing or gives the can't find automationobject error (respectively).

TIA

Dave

Okay, so I solved the insert problem by making the relationship between Offices and Document enforce referential integrity, however, I still cannot calculate the next document number. How do I do that!?

Thanks

Nevermind, I didn't solve it. Still getting the "index cannot have a null value" even though it appears to be set. Ugh.

Looking at the relationships you set up, it maybe that the reason you can't add a new record is a result of the combobox used for the OfficeID. This is a lookup, so it has to have the bound column set to the OfficeID and not another field, otherwise the Document field named OfficeID will have a value of null or missing when trying to create your new record. Typically the error message will look like this:

Cannot find record in table Office with key matching field(s) 'OfficeID'

On the second part, I am lost because the DMax should work. Try pasting this code in the after update event for either of the comboboxes:

Number = DMax("Number", "Document")+1

Recheck the lookup of the Office combo box to make sure that the bound column is the OfficeID and try pasting the code above in the after update of any of the two comboboxes or in the after update of the form and see if this solves the problem.

lwells

The latest update: Looks like the form almost works. There are still some error conditions that I don't understand. I didn't really do anything differently to make it work, however, which puzzles me. There seems to be some sensitivity as to what actions the user takes when inserting information for new records. Sometimes the "can't be null" value shows up. The bound column is definitely the ID.

I finally got the "got focus" event to work and calculate the next doc number:

Public Sub Doc_Num_GotFocus()

Dim strSQL As String
Dim projId As Integer
Dim officeId As Integer
Dim curNum As Integer

curNum = Forms![Document2]![Doc Num]
projId = Forms![Document2]!Project.Column(0)
officeId = Forms![Document2]!Office.Column(0)

If (curNum <= 0) Then
Dim maxNum As Integer
maxNum = DMax("[Doc Num]", "Document", "[Project]=" _
& projId & " and [Office]=" & officeId & "")
Forms![Document2]![Doc Num] = maxNum + 1
End If


End Sub

However, I get an error if there are no records for a given project. I guess I should handle that case and make the document number = 1. That must be why I get the "invalid use of NULL" error message.

Thanks for the help in looking at this problem.

Dave

Hi Dave,

Yeah, you got it figured out. Just change the code to the following to handle the error.

If (curNum <= 0) Then
Dim maxNum As Integer
maxNum = DMax("[Doc Num]", "Document", "[Project]=" _
& projId & " and [Office]=" & officeId & "")
If maxNum = "" Then
Forms![Document2]![Doc Num] = 1
Else
Forms![Document2]![Doc Num] = maxNum + 1
End If
End If

lwells