asked on

Using Inpuit form and recordset to update Access table

Private Sub Command27_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_StaffEval", dbOpenTable)

rs.AddNew
rs.Fields("ProviderID") = Me.List33.ItemSelected.Column(0).Value

rs.Update

rs.Close

Set rs = Nothing

End Sub

I using the code above to take the input for a ListBox with 4 columns and updating a table within the database.

The user selects one record from the List Box and clicks a Command Button to add a new record to the table.

I am getting an error Object Undefined on the line:

rs.Fields("ProviderID") = Me.List33.ItemSelected.Column(0).Value

The first column of data in the List Box is the ProviderID.

Thanks

Glen

Rey Obrero (Capricorn1)

try changing this

rs.Fields("ProviderID") = Me.List33.ItemSelected.Column(0).Value

with

rs!ProviderID=me.list33

what is the rowsource of the listbox "list33" ?
what is the bound column of the listbox?

GPSPOW

ASKER

The "List33" list box row source is a table of physicians, "DMisProviders". I have only selected the ones that have an Active Flag of "Y" and I have selected 4 columns from the table to display in the ListBox:

ProviderID
Name
SpecialtyID
Specialty

The form user picks a line from the ListBox to be added to the table "tbl_StaffEval"

I will eventually bring in more fields, but I just want to test this on the first list box bound column (ProviderID)

Thanks

Glen

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GPSPOW

ASKER

Thanks

Dale Fye

GPSPOW,

I think mbizup forgot the

rs.AddNew 'Between lines 2 and 3

and

rs.Update 'Between lines 5 and 6

lines in that block of code.