Invalid use of Null???? 3

[basco81]

Private Sub Form_Load()

Dim l_title As String
Dim l_title1 As String
Dim l_title2 As String
Dim l_title3 As String

**l_title = DLookup("Customer", "tblParameters")**
l_title1 = DLookup("Project", "tblParameters")
l_title2 = DLookup("Location", "tblParameters")
l_title3 = DLookup("TypeOfForm", "tblParameters")

Customer.Caption = l_title
Project.Caption = l_title1
Location.Caption = l_title2
TypeOfForm.Caption = l_title3


End Sub

What is wrong with this??? The debugger highlights the line that I have the ** around..

 

[CajunCenturion]

You cannot assign a null value to a string. The DLookup function may return a NULL is a qualifying value is not found. I would suggest that you do the following:

l_title1 = Nz(DLookup("Project", "tblParameters"), vbNullString)

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[basco81]

Thanks for the Info, but still having a small problem. The lines of code I have originally are accessing the table and then adding that info to the labels. I also have another set of code that allows you to change the labels and stores the data into tblparameters that way when you restart the database the labels will be the same as when you left. With the string of code you gave me the error has gone away but the labels are no longer showing up upon form load...any ideas??

 

[CajunCenturion]

You might try puttng the fieldname in square brackets.

l_title1 = Nz(DLookup("[Project]", "tblParameters"), vbNullString)

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[basco81]

nope...still doesn't work...thanks for the help though

 

[CajunCenturion]

Check your spelling. Check the table and ensure that it has values. How many records are in the table? Since you are not specifying a Criteria, DLookup may return any record from the table.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PHV]

And what about something like this ?
l_title1 = Nz(DLookup("Project", "tblParameters"), "No project")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[basco81]

Hey,
Sorry it has been a few days since I have been able to work in access. I have figured out why everything is not working just having a lil' troble fixing it. The data doesn't seem to be storing into my table, I have checked my code and can't seem to find any errors. Please browse over the code or offer some new ideas for me.
The command button that allows the user to add a new title to the form and also stores the data into the tblparameters as it is entered:

Private Sub Command30_Click()
    
    Dim l_title As String
    Dim l_title1 As String
    Dim l_title2 As String
    Dim l_title3 As String
    
    l_title = InputBox("Please enter Customer")
    Customer.Caption = l_title
    DoCmd.RunSQL "UPDATE tblParameters SET Customer='" & l_title & "';"
  
    l_title1 = InputBox("Please enter Project")
    Project.Caption = l_title1
    DoCmd.RunSQL "UPDATE tblParameters SET Project='" & l_title1 & "';"

    l_title2 = InputBox("Please enter Location")
    Location.Caption = l_title2
    DoCmd.RunSQL "UPDATE tblParameters SET Location='" & l_title2 & "';"

    l_title3 = InputBox("Please enter Type Of Form")
    TypeOfForm.Caption = l_title3
    DoCmd.RunSQL "UPDATE tblParameters SET TypeOfForm='" & l_title3 & "';"

     
    


End Sub

Upon the loading of the form, the tblparameters is accessed and the info in the table is added to the form this way the user doesn't have to add his/her title every time he/she opens the form:

Private Sub Form_Load()

    Dim l_title As String
    Dim l_title1 As String
    Dim l_title2 As String
    Dim l_title3 As String

        l_title = Nz(DLookup("[Customer]", "tblParameters"), vbNullString)
        l_title1 = Nz(DLookup("[Project]", "tblParameters"), vbNullString)
        l_title2 = Nz(DLookup("[Location]", "tblParameters"), vbNullString)
        l_title3 = Nz(DLookup("[TypeOfForm]", "tblParameters"), vbNullString)
    
        Customer.Caption = l_title
        Project.Caption = l_title1
        Location.Caption = l_title2
        TypeOfForm.Caption = l_title3
    
        
End Sub

Thanks in advance

 

[basco81]

Anyone have any suggestions??

 

[BobJacksonNCI]

Update has to have a record - You may need to change the first RunSQL to APPEND



HTH,
Bob

 

[CajunCenturion]

That's a good point BobJacksonNCI as the there must be a record to update, but changing the first RunSQL to APPEND may not be the right answer, because, if I understand correctly, you only want 1 append, and only on the first time. From then one, UPDATES are all that you need.

It may be best to manually add the first record to the parameters table.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[basco81]

WOW...thanks guys, never knew it would be that easy, I have checked and rechecked code for hours, but it is always the simple things I forget, THANKS!!!