Access VBA Error Help

[ksmith247]

I am very new to VBA and pretty new to Access also. I found this VBA code online and had it working, but unfortunately did something to it and now it is generating an error "Object variable or With block variable not set". I have an access database with a userform that data is input into textboxes then a button to submit those fields to a table "tblRecordsStorage". If there is more information needed than I have provided I can certainly post it. Since I am new to VBA an Access, I am not sure what is needed.


Below is the code I have in my database to perform the submission.


Private Sub btnUpdate_Click()
'On Error GoTo EmptyField
Dim db As Database
Dim rec As Recordset
Dim ctl As Control
DoCmd.GoToRecord , , acNewRec
rec.AddNew
rec("Company") = Me.Company
rec("Location") = Me.Location
rec("Accounting_Unit") = Me.Accounting_Unit
rec("Series Number") = Me.Series_Number
rec("Box Number") = Me.Box_Number
rec("Range") = Me.Range
rec("Row") = Me.Shelf
rec("Shelf") = Me.Detailed_Description
rec("Disposal Date") = Me.Disposal_Date
rec("Detailed Description") = Me.Detailed_Description
rec.Update
On Error Resume Next
For Each ctl In Me.Controls
ctl.Value = ctl.DefaultValue
Next
Set ctl = Nothing
Set rec = Nothing
Set db = Nothing
'EmptyField:
' MsgBox "**Please fill in all fields to input a new record.**"
End Sub

[ranman256]

1 you didn't show the spot where the code broke (besides the code is doing 2 things.)

2. you don't need ANY code to do this, just run an append query.
check to see if user filled in all the fields, then
docmd.openquery "qaAddNewRecData" (put your text boxes in to add to the table)

[ksmith247]

It broke at the "rec.AddNew" area. I generally just Google an idea, and a similar code came up that I could modify. I am also trying to learn VBA little by little. I had not thought about using an append query either, so that is definitely worth looking into.

[jonh]

Database and recordset need SET statements, because they are objects.
ctl is also an object, but since it's used in a for next loop SET isn't required.

i.e.

you SET the objects to nothing in your code, but you aren't setting them to something..

[ksmith247]

Database and recordset need SET statements, because they are objects.
ctl is also an object, but since it's used in a for next loop SET isn't required.

i.e.

you SET the objects to nothing in your code, but you aren't setting them to something..

Can you give me an example of what you mean when you say they need SET statements? I tried Google to see if I could figure it out on my own, but that was a no-go. I believe I understand what you mean by setting them to something, but I am not for sure how to write it. I am as new to VBA as you can get. I can somewhat understand what it does, but actually writing it is a whole new ballgame. Thank you for everyone's help.

[jonh]

Using Dim states what kind of thing something is but it doesn't say what it is.
You've told the code db is a database and rs is a recordset, but not which database and what recordset.

dim db as dao.database
dim rs as dao.recordset
dim f as dao.field

set db = currentdb
set rs = db.openrecordset("select * from table1 where id=1")

'print values
for each f in rs.fields
    debug.print f.value
next

'tidy up
rs.close
set rs = nothing
set db = nothing

But as ranman said you don't need to open any recordset because you can use an update query

Private Sub btnUpdate_Click()


    Dim sql As String
    sql = "insert into mytable "
    sql = sql & "(Company,Location,Accounting_Unit,[Series Number],[Box Number],[Range],[Row],Shelf,[Disposal Date],[Detailed Description])"
    sql = sql & " values ("
    sql = sql & "'" & Me.Company & "',"
    sql = sql & "'" & Me.Location & "',"
    sql = sql & "'" & Me.Accounting_Unit & "',"
    sql = sql & "'" & Me.Series_Number & "',"
    sql = sql & "'" & Me.Box_Number & "',"
    sql = sql & "'" & Me.Range & "',"
    sql = sql & "'" & Me.row & "',"
    sql = sql & "'" & Me.Shelf & "',"
    sql = sql & "'" & Me.Disposal_Date & "',"
    sql = sql & "'" & Me.Detailed_Description & "')"
    CurrentDb.Execute sql
    
End Sub

Of course you could just use bound controls on your form then you don't need any code at all.

[ksmith247]

The sql worked great. Thank you both for all your help. One more question, what is the best way to clear the form text boxes after the sql runs?

[jonh]

http://www.access-programmers.co.uk/...96&postcount=3

[ksmith247]

I was able to get it working. Thanks again for all your help.