I posted this same question in a di 1

[Tofias1]

I posted this same question in a different forum yesterday thinking it was this one so if it looks familiar it is. So with that said here is my problem. I have a table that stores names. It is connected to other forms as a list box to choose the names from. What I would like to do is have a input box open up once a cmd button is clicked. The user will then enter a name within the box and it will post into the table once OK is clicked, allowing the user to select it from a list. Here is my code but nothing happens except for the input. I have no idea where the data is going. (Names is the table and because this is a testing mdb the field is also named Names) I am pretty sure it would work with a few chages in VB but I can not figure it out in VBA.

Private Sub Add_Data_Click()
Dim Varanswer As String
Dim rst As DAO.Recordset
Dim One As String

Varanswer = InputBox("Please Enter the Name which you would like to add.&quot
If Varanswer = "1" Then

Set rst = CurrentDb.OpenRecordset(Names, dbOpenDynaset)

One = Varanswer
With rst
.AddNew
!Names = Me!Names.Column(0, One)
.update

End With

End If

End Sub

Thanks a lot to anyone who has taken a look at this code.
Any help would be greatly appreciated.
Tofias1

 

[jebry]

Hi!

You can do it like this:

Private Sub Add_Data_Click()

Dim stranswer As String
Dim rst As DAO.Recordset

stranswer = InputBox("Please Enter the Name which you would like to add.&quot


Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)

With rst
.AddNew
!YourField = stranswer
.update
End With

End Sub

I noted that it seems you are using the same name for the table, the field and the list box which is usually confusing to Access. Using similar names like tblNames, fldNames and lstNames is better.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Tofias1]

Jeff,

I have a quick question for you. Should this run behind the form Names with a cmd button or any form? The reason that I ask is I have tested it on two forms and I get the same error on both. "The Microsoft Jet Database engine cannot find the input table or query 'Beliveau'." Beliveau is the first name that I have within the table Name.

If this sounds familiar I was hoping you could point me in the right direction.

Thanks again for your help.

Tofias1

 

[jebry]

Hi!

Post the code at you have it now that will help figure out what the problem is. If you hard coded the name of the table in the OpenRecordset statement, then you shouldn't be getting that error. There are better ways to add names to the table. For example, you can use the button click to open an entry form which is connected to the table.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Tofias1]

Jeff,

I figured out what I was doing wrong. Since I am working on a test mdb I named both the field and the tbl the same as you already know. Once I changed that it was ready to go. Thanks for your help it was greatly appreciated.

Tofias1.