Procedure to insert new text from combo box into a table

[shredder249]

Hi, I have an "Add New Records To Table" form. In the header of the form there is a combo box (artistsource) which looks up values from a different table (Artist), the value selected by the user is set as the default value for one of the fields on the records. The user isn't restricted to the data in the artist table and may type in a new artist. What I am trying to code is that when the user closes the form, if the user typed in a new artist into the combo box it is added to the artist table so it will be there and can be selected next time. I have written a SQL procedure for this which technically works, although it has some problems:

Sub TableUpdate()
 
    If ArtistSource <> "" Then
        DoCmd.RunSQL ("Insert into Artist (Artist) values ([artistsource])")
    Else
        MsgBox "You haven't selected an artist from the list or typed in a new one"
    End If
 
End Sub
 

The main problem is that I wanted the procedure to first search the Artist table with the value from the artistsource combo box. If the search couldn't find a match then that must mean the user typed in a new artist. As a result of this the SQL code would run and add the new artist to the table. I couldn't work out how to write the searching procedure, so i just worked out the adding part. Do you have any hints/ideas on how to write the searching procedure? I would have used the findrecord method but the table to be searched is not referenced in the form.

The second problem (more an aesthetic one than a code one) is that when running the SQL code a big warning pops up saying: "You are about to append 1 row(s)". I do not want this warning because I know what it is about to do. Is there anyway of bypassing it?

[Minion]

Hi, I have an "Add New Records To Table" form. In the header of the form there is a combo box (artistsource) which looks up values from a different table (Artist), the value selected by the user is set as the default value for one of the fields on the records. The user isn't restricted to the data in the artist table and may type in a new artist. What I am trying to code is that when the user closes the form, if the user typed in a new artist into the combo box it is added to the artist table so it will be there and can be selected next time. I have written a SQL procedure for this which technically works, although it has some problems:

Sub TableUpdate()
 
    If ArtistSource <> "" Then
        DoCmd.RunSQL ("Insert into Artist (Artist) values ([artistsource])")
    Else
        MsgBox "You haven't selected an artist from the list or typed in a new one"
    End If
 
End Sub
 

The main problem is that I wanted the procedure to first search the Artist table with the value from the artistsource combo box. If the search couldn't find a match then that must mean the user typed in a new artist. As a result of this the SQL code would run and add the new artist to the table. I couldn't work out how to write the searching procedure, so i just worked out the adding part. Do you have any hints/ideas on how to write the searching procedure? I would have used the findrecord method but the table to be searched is not referenced in the form.

The second problem (more an aesthetic one than a code one) is that when running the SQL code a big warning pops up saying: "You are about to append 1 row(s)". I do not want this warning because I know what it is about to do. Is there anyway of bypassing it?

If I'm reading this correctly you're making the first part a lot harder than it really need to be. You should be able to set the combo box to retreive it's values from the artists table, but save the selected value to a field in your other table. Once this is set (easily done through the wizard for combo boxes) go into the properties for the combo box (select it and press alt+enter) and under the data tab set "Limit To List" to "NO". This should allow the user to type in a new value and it will save it to the field in your second table. However, if you want that to be added to your first table you'll need to append it (there's a not in list event for this).

As for your second question about the pop up you can disable that by using the following code before you execute the SQL.

DoCmd.SetWarnings = False
 

Hope this helps.

- Minion -

[shredder249]

Cheers =) all working out now

If I'm reading this correctly you're making the first part a lot harder than it really need to be. You should be able to set the combo box to retreive it's values from the artists table, but save the selected value to a field in your other table. Once this is set (easily done through the wizard for combo boxes) go into the properties for the combo box (select it and press alt+enter) and under the data tab set "Limit To List" to "NO". This should allow the user to type in a new value and it will save it to the field in your second table. However, if you want that to be added to your first table you'll need to append it (there's a not in list event for this).

As for your second question about the pop up you can disable that by using the following code before you execute the SQL.

DoCmd.SetWarnings = False
 

Hope this helps.

- Minion -