Adding a record to a different table

[Rhythmdvl]

Obviously a VBA amateur here-

I have a bit of code triggered by an event on a form. How do I insert a record into a table that is different from the table the form is based on? Is there a simple VBA statement I can insert into any code that pastes the data string into a different table? Something like

.[field] = NewData? Thanks.

Jeff

 

[Rich]

Either an append query/sql or update query/sql.
HTH

 

[Rhythmdvl]

Hi Rich,

Do I need to create an append query within the code every time I want to add a record to a different table? There is no way to refer to a different table without all of the steps involved in making the query, something like a variation on DLookUp? Here is a bit about what I am trying to do, if it helps.

I have a combo box that allows users to enter keywords from a list. If the keyword is not on the list yet, the notinlist event kicks in and allows the user to add it. This is straight out of the help files. But the code in the help file has me change the ctl.rowsource property. I'd like other forms based on other tables to be able to access the same keyword list. I can set the combobox's rowsource type to table/query and point the rowsource to the appropriate "dictionary" table, but what then do I do with the new keyword? This is why I'd like to be able from within the notinlist event paste the record into a field in a different table.

This type of situation arises in many other situations, and I usually find a workaround. But I can't help but feel there is a line or two of code out there that will allow me the freedom to paste whatever variable I have at the time into any table I want to. Thanks again for the start.

Jeff

 

[Rich]

I don't know what code you are using, but Solutions database has a an example of how to add a not in the list to another table and then requery the original without changing control source.
HTH

 

[BukHix]

You could use a recordset to open and add data to a table. Here is a thread with an example of what I am talking about.

Topic: Using RecordSet to update table?

I had a form that was bound to one form but I needed to update data in another table based on data in the first. In the example I posted, as Talismanic, I used static variables but in the actual database they are more dynamic.

 

[Rhythmdvl]

Hello again,

I really hate to be thick about this, and hope I am not wasting anyone’s time. I do thank everyone for the help that I’ve gotten so far, but I have some nagging questions that mean either a) I wasn’t clear enough in what I was trying to do, or b) there really is no simple way to do this.

If you would accommodate a bit of further explanation:

My basic question is this: What is the shortest way to write data to a different table? Keeping it as simple as possible, suppose I have a form with a button that brings up an input box. The value is returned with a message box. The Very Simple Code for the on-click event of the button is as follows:

[/quote]
Private Sub Command0_Click()
Dim tst As String
Dim tstinput As String

tstinput = InputBox("Enter a word", "Testing")
tst = tstinput
MsgBox "test word is " & tst

End Sub[/quote]

Rather than display the tst string in a message box, I want to write that value to a table. That is, I want to replace the line MsgBox “test word is “ & tst with something that pastes the record into a table. The table I want to write to does not have any connection with the form. Either there is a simple way of doing this (i.e. I simply replace the last line with something along the lines of [outside table].[outside table field] = tst ) or I need to go and learn all about recordsets and their manipulation.

So please, I ask this in all sincerity because intuitively I would think that referring to a table in the database is a simple thing. Am I wrong? Is there no simple anti-DlookUp function out there? Thanks,


Rhythmdvl

 

[Rich]

So why can't you create a custom popup form with a text box that is bound to the table you want to add the record to?

 

[BukHix]

I am not sure if you had a chance to look at that thread I put the link to but this is how it should look using your criteria:

Dim rsCOD As DAO.Recordset
Dim tst As String
Dim tstinput As String

tstinput = InputBox("Enter a word", "Testing")
tst = tstinput

MsgBox "test word is " & tst

Set rsCOD = CurrentDb.OpenRecordset("YourTableName")

rsCOD.AddNew
rsCOD!TestWord = tst ' Replace the TestWord with your field name.
rsCOD.Update

Set rsCOD = Nothing

Let me know if you have more questions.


[This message has been edited by BukHix (edited 09-26-2001).]