ms access - Updating table with VBA -

ms access - Updating table with VBA -

i have been struggling getting code work few days. if offer solutions appreciate it.

private sub command0_click() if isnull(newsupbox.value) or isnull(newnumberbox.value) msgbox ("all fields must filled") goto errorexit end if if not isnull(dlookup("suppliernumber", "supgeninfo ", "supgeninfo.suppliernumber =" & newsupbox)) = msgbox ("this supplier number exists. can edit current record on edit supplier page.") goto errorexit end if dim db database dim rec recordset set db = currentdb set rec = db.openrecordset("select * supgeninfo") rec.addnew rec("suppliernumber") = me.newsupbox.value rec("suppliername") = me.newnamebox.value rec.update set rec = nil set db = nil msgbox "records added successfully." errorexit: end sub

edit: forgot mention not getting error message. command not add together new record table.

edit2: code above output msg "records added successfully" when remove next block of code.

dim db database dim rec recordset set db = currentdb set rec = db.openrecordset("supgeninfo") rec.addnew rec("suppliernumber") = me.newsupbox rec("suppliername") = me.newnamebox rec.update set rec = nil set db = nil

it when code included command click becomes unresponsive.

i believe, reading table (for display purposes) select * ... statement, you're adding new record list rather actual database. when open openrecordset, supply table name, not whole sql query shebang...

i created new table, edit code match parameters/values, otherwise has been tested work:

dim db database dim rec recordset set db = currentdb set rec = db.openrecordset("table1") rec.addnew rec("field1") = 1234 rec("field2") = "blah2" rec("field3") = "blah3" rec.update set rec = nil set db = nil

hope helps.

vba ms-access