Invalid Use of Null when adding new record
I have a frmSamples that is opened from a command button. I need to add one or more samples to the form but I can only add one. When I click into the next record (continuous form) I get an invalid use of null error. The form has a text field which is used to store a concatenated numeric sampleID. What I need is for the sampleID to fill in for further records too, not just the first one. Ideas anyone?
In the form On Current I have
Private Sub Form_Current()
Me.txtSampleID = make_my_id(Now(), [ID])
End Sub
In the form load is -
Private Sub Form_Load()
If Me.OpenArgs & "" <> "" Then
' MsgBox Me.OpenArgs
If Me.NewRecord Then
Me.SubmissionNo = Me.OpenArgs
Else
End If
End If
End Sub
This is the function called in the on current
Public Function make_my_id(indate As Date, inID As Integer) As String
Dim myStr As String
Select Case Len(CStr(inID))
Case 1
myStr = "000" & CStr(inID)
Case 2
myStr = "00" & CStr(inID)
Case 3
myStr = "0" & CStr(inID)
Case 4
myStr = CStr(inID)
Case Else
myStr = Right(Trim(CStr(inID)), 4)
End Select
make_my_id = Format(indate, "YY") & myStr
End Function
In the form On Current I have
Private Sub Form_Current()
Me.txtSampleID = make_my_id(Now(), [ID])
End Sub
In the form load is -
Private Sub Form_Load()
If Me.OpenArgs & "" <> "" Then
' MsgBox Me.OpenArgs
If Me.NewRecord Then
Me.SubmissionNo = Me.OpenArgs
Else
End If
End If
End Sub
This is the function called in the on current
Public Function make_my_id(indate As Date, inID As Integer) As String
Dim myStr As String
Select Case Len(CStr(inID))
Case 1
myStr = "000" & CStr(inID)
Case 2
myStr = "00" & CStr(inID)
Case 3
myStr = "0" & CStr(inID)
Case 4
myStr = CStr(inID)
Case Else
myStr = Right(Trim(CStr(inID)), 4)
End Select
make_my_id = Format(indate, "YY") & myStr
End Function
ASKER
I tried the code you suggested and still get Invalid Use of Null when I click into the next record on the form. What it should do is fill in the txtSampleID box with the next number in the series. Any other ideas?
Thanks!
Thanks!
Do you mean the next "NewRecord" on the form?
Where does the [ID] value in this:
Me.txtSampleID = make_my_id(Now(), [ID])
come from?
Is ID an autonumber field in the table that is the recordsource for the form? Does the form contain the [ID] field, linked to any control? If not, create a hidden control bound to the [ID] field.
Is your data all in Access, or are you using Access for the front-end for some other database? This might have an effect, as Access will generate a value for an autonumber field as soon as the form moves to a new record, but most other databases don't work that way.
Where does the [ID] value in this:
Me.txtSampleID = make_my_id(Now(), [ID])
come from?
Is ID an autonumber field in the table that is the recordsource for the form? Does the form contain the [ID] field, linked to any control? If not, create a hidden control bound to the [ID] field.
Is your data all in Access, or are you using Access for the front-end for some other database? This might have an effect, as Access will generate a value for an autonumber field as soon as the form moves to a new record, but most other databases don't work that way.
ASKER
Hi. Yes ID is an autonumber field in the table the query and form is based on. I added the ID field to the form and now when I click into the next new record (continuous form) on the form I get the invalid use of Null but then it carries on and fills in the next SampleID when I click End and click into the SampleID field. This is in the version of the form that opens in Edit mode (Same form design they just open in different modes).
The one for adding new records which opens in Add mode gives me the Invalid Use of Null and then won't fill in any fields in the second new record. I could skip the Add Mode form altogether if I could suppress the error in the Edit Mode form and add records there.
Yes the data is all in Access.
Thanks!
The one for adding new records which opens in Add mode gives me the Invalid Use of Null and then won't fill in any fields in the second new record. I could skip the Add Mode form altogether if I could suppress the error in the Edit Mode form and add records there.
Yes the data is all in Access.
Thanks!
Can you post a sanitized version of your database with just this form (s) and the associated tables? If so:
1. create a new database, copy just those objects (tables, forms, queries) that are necessary to duplicate the problem into the new database.
2. Review the data tables and make sure there is not personal or proprietary information such as names, addresses, phone #s, ... If you need to, just make up some data
3. Open your forms and make sure the problem persists from the original to the sample database.
4. Upload the file here and provide some instructions on how to duplicate the problem.
1. create a new database, copy just those objects (tables, forms, queries) that are necessary to duplicate the problem into the new database.
2. Review the data tables and make sure there is not personal or proprietary information such as names, addresses, phone #s, ... If you need to, just make up some data
3. Open your forms and make sure the problem persists from the original to the sample database.
4. Upload the file here and provide some instructions on how to duplicate the problem.
ASKER
Here is the database with made up dummy data. The flow goes, customer form, add new submission from subform command button (add date first to generate new record). Then command button add new samples. The first sampleID fills in and other info can be entered but clicking into the next new record generates invalid use of null.
The other path - Click the command button beside any existing submission number, click edit samples, clicking into a new record generates the error but if I click end on the error message and click around various fields the sampleID will fill in eventually.
Thanks for having a look!
Nbdc-test-2.accdb
The other path - Click the command button beside any existing submission number, click edit samples, clicking into a new record generates the error but if I click end on the error message and click around various fields the sampleID will fill in eventually.
Thanks for having a look!
Nbdc-test-2.accdb
When you go to the new record, this new record is not yet created, so its ID is indeed null, but event "current" does run. The event where just-created autonumber becomes available is "afterinsert".
When the error hits, your code should break in VBA window on the line where the error occurred, and you can examine the contents of the variables. If your code has "on error" statements, set in VBA Tools/Options/General/Brea
When the error hits, your code should break in VBA window on the line where the error occurred, and you can examine the contents of the variables. If your code has "on error" statements, set in VBA Tools/Options/General/Brea
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Hi datAdrenaline. Your solution would work great except for one thing. The client wants to restart the numbering each year. So for this year it would go 13-0001 to 13-9999 then on Jan 1 would restart at 14-0001. I don't understand Access nearly as well as I thought I did but from what I can see your solution will not restart the numbering. I did originally want the SampleID stored in the database but couldn't figure out a way to do it. Any other ideas?
Thanks so much for having a look!
Thanks so much for having a look!
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks! That's a big help. I will play around with the format and see what I can come up with.
You're welcome! Good luck with your project!
Public Function make_my_id(indate As Date, inID As Integer) As String
Make_My_ID = Format(indate, "YY") & Format(inID, "0000")
End Function
2. Then, change:
Me.SubmissionNo = Me.OpenArgs
to:
me.SubmissionNo = NZ(Me.OpenArgs, 0)
or something like that. It looks like the problem is that you are opening the form without passing in the opening argument, and without that, there is no value for SubmissionNo.