Updating table from textbox input

[presence76]

I have a form that has a textbox in it. I am using the information to update a table in ACCESS. I am getting "invalid use of null" on this line:

newmgmtcode = Forms![form1]![textmgmtcode]

I have also tried

newmgmtcode = Forms![form1]![textmgmtcode].Value

Either way, I get invalid use of null.

Here is the full code:

Function fcnaddmgmtcode()


Dim newmgmtcode As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb()

newmgmtcode = Forms![form1]![textmgmtcode]

Set rst = dbs.OpenRecordset("tblmgmtcodecurrent")
With rst
.AddNew
.Fields(1) = newmgmtcode
.Update
End With

End Function

The strange thing is that it was working, intermittently. The first time it failed, I walked it through the debugger and then it worked. Then, when I opened the DB again, it failed. Any ideas????

All help is greatly appreciated. Thanks.

 

[PHV]

You may try something like this:
Function fcnaddmgmtcode()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
If Not IsNull(Forms![form1]![textmgmtcode]) Then
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblmgmtcodecurrent")
With rst
.AddNew
.Fields(1) = Forms![form1]![textmgmtcode]
.Update
.Close
End With
Set rst = Nothing
Set dbs = Nothing
End If
End Function

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

 

[presence76]

You did a great job. Worked first time.

Thanks for all the help.

 

[presence76]

Woops. Did not work first time. The message told me the table was updated, but that was just because when the query is over. It is still getting hung up about the textbox being null.

Is there any setting in the properties of the textbox that could be preventing data from being passed?

Might I use another method to define the textbox? This is the only way I have ever develped with forms.

If anyone has ideas, all input will be appreciated. Thanks.

 

[presence76]

Took care of it. I created another form.


Thanks again for all your help.

 

[PHV]

Perhaps .Fields(0) instead of .Fields(1) ?

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

 

[Dryseals]

How are you trying to pass the data from the text box to the table, what initiates the transfer, a push button, afterupdate? Also are you actually viewing the form when you do this?

 

[presence76]

A push button on the form executes a query that executes the VBA code. Yes, I am viewing the form when I do this. I thought maybe because the textbox was in the Header section of the form - but I moved the textbox to Detail and nothing. When I creat a new textbox in the "bad" form, it does not work either. But, when I do it in the new form I created, it accepts the data. I doublechecked the spelling and all that.