null... Help Please

[alekkz]

hello everyone.

let me start by saying that your help is much appreciated and by saying that I do not know much about this, please be patient.

my issue is the following

I have a txtbox which is a custom autonumber which is supposed to automatically fill when the form is loaded with a new record.

I keep running into some "null" issues, I have tried using "isnull",
"not isnull", and "isempty" and I have not been able to get around this issue

here is what I have

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
If IsNull(txtControlNumber) Then
txtControlNumber = DMax([ControlNumber], "LOG_tbl") + 1
End If



please help

 

[Minty]

Try referring to the control on the form as Me.txtControlNumber

Make sure at the top of all your code modules you have Option Explicit, and also try compiling your code.

 

[alekkz]

Try referring to the control on the form as Me.txtControlNumber

Make sure at the top of all your code modules you have Option Explicit, and also try compiling your code.

thanks for your help


but I still have the same issues


here is what I have now



Option Compare Database
Option Explicit

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
If IsNull(txtControlNumber) Then
Me.txtControlNumber = DMax([ControlNumber], "LOG_tbl") + 1
End If
End Sub

 

[alekkz]

by the way the error am encountering is "invalid use of null"

 

[Minty]

Option Compare Database
Option Explicit

Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
    If IsNull([COLOR="Red"]Me.[/COLOR]txtControlNumber) Then
         Me.txtControlNumber = DMax([ControlNumber], "LOG_tbl") + 1
    End If
End Sub

You missed the first reference to it (in red), however which line is the error highlighting?

 

[alekkz]

You missed the first reference to it (in red), however which line is the error highlighting?

thanks Minty..


the highlighting line is the line is the following


"Me.txtControlNumber = DMax([ControlNumber], "LOG_tbl") + 1"

 

[Minty]

Okay so it's not your IsNull part that is the problem, it's the DMax.

Is ControlNumber definitely a number? Is the table it is in actually called "LOG_tbl"

What happens if you type

?DMax([ControlNumber], "LOG_tbl")

in the immediate window?

 

[missinglinq]

Couple of things:

• How, exactly, is the 'custom autonumber' supposed to 'automatically fill' when a new record is started?
  
• What Datatype is this 'custom autonumber?'
  
• Are there any Records currently in LOG_tbl?

Linq ;0)>

 

[alekkz]

Couple of things:

• How, exactly, is the 'custom autonumber' supposed to 'automatically fill' when a new record is started?
• What Datatype is this 'custom autonumber?'
• Are there any Records currently in LOG_tbl?

Linq ;0)>

thanks missinglinq...

• How, exactly, is the 'custom autonumber' supposed to 'automatically fill' when a new record is started?

• • I thought this code was supposed to check to make sure the box is empty or null and when it is to find the biggest "ControlNumber" and add 1 to it, and make it so into that txtbox

If IsNull(Me.txtControlNumber) Then
Me.txtControlNumber = DMax([ControlNumber], "LOG_tbl") + 1

• What Datatype is this 'custom autonumber?'

• • numeric? if that's what you mean.

Are there any Records currently in LOG_tbl?

LOG_tbl ID Date In Work Order Logged in by ControlNumber Status Date Out Accepted by 1 10/17/2016 12345678 Auribe 123 In-Process 10/19/2016 Agonzalez 5 10/19/2016

124


10 10/8/2016

125


11 10/19/2016

126


12 10/19/2016

127


13 10/20/2016

128


14 11/4/2016 1234 alex Uribe 129 asdf 11/26/2016

 

[alekkz]

sorry all that look better before I posted it but anyhow.. there is stuff on that tbl... to be specific the numbers 123-129 are all listed under "ControlNumber" with the table having 7 different entries

 

[alekkz]

ok so am making some head ay here I still need some help though.

so if I take off the line where am adding a new record and the "if" statement, the code works fine. with it, it doesn't.

I want my form to add the autonumber automatically when the form is loaded with a new record...

this is what I have now


Private Sub Form_Load()
ControlNumber = DMax([ControlNumber], "LOG_tbl") + 1
End Sub


any suggestions

 

[alekkz]

Okay so it's not your IsNull part that is the problem, it's the DMax.

Is ControlNumber definitely a number? Is the table it is in actually called "LOG_tbl"

What happens if you type

?DMax([ControlNumber], "LOG_tbl")

in the immediate window?

not ignoring your post, I cant find the "immediate window" I thought it was under "debug/windows"

 

[Minty]

edit - Immediate window - Press Ctrl G in the debug window. www.baldyweb.com/immediatewindow.htm

Personally, as you have an ID field, I would use that and avoid all the problems...

Why not set the default value of the control to DMax([ControlNumber], "LOG_tbl") + 1
Default values are only set on new records.

 

[alekkz]

edit - Immediate window - Press Ctrl G in the debug window. www.baldyweb.com/immediatewindow.htm

Personally, as you have an ID field, I would use that and avoid all the problems...

Why not set the default value of the control to DMax([ControlNumber], "LOG_tbl") + 1
Default values are only set on new records.

thanks minty. so I deleted the code I only left the gotorecord,,acnewrec part

I added the dmax to the default value and now the txtbox for the "Controlnumber" is displaying "#ERROR'

thanks

and sorry for the continuous questions and replies


and by the way

I don't know what was suppose to happen on the immediate window but nothing happen. hope that helps

 

[Minty]

So typing (with the question mark!)

?DMax([ControlNumber], "LOG_tbl")

And pressing return gives you nothing?
If that is the case you have either your table or field name wrong, or more likely something in your data is making the function return null.

 

[alekkz]

im sure all the names are ok... hopefully you can see the picture

 

[Minty]

I suggest you upload your database.
If ?DMax([ControlNumber], "LOG_tbl") doesn't return either 129 or an error as its answer in the immediate window , something strange is happening.

 

[alekkz]

thanks. minty I have no idea how to upload my whole database. It might not be possible since im doing this for work and the network here is pretty tight I wont be able to do it if I have to zip it..

however the immediate window is now giving me a "compile error: External name not defined"

hope this helps

 

[alekkz]

so I googled that error and I fixed it...

somehow the brackets were messing it up.. I switched the brackets for "" and it worked.... thanks Minty and Missinglinq...