By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,247 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,247 IT Pros & Developers. It's quick & easy.

Procedure to insert new text from combo box into a table

P: 22
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:

Expand|Select|Wrap|Line Numbers
  1. Sub TableUpdate()
  2.  
  3.     If ArtistSource <> "" Then
  4.         DoCmd.RunSQL ("Insert into Artist (Artist) values ([artistsource])")
  5.     Else
  6.         MsgBox "You haven't selected an artist from the list or typed in a new one"
  7.     End If
  8.  
  9. End Sub
  10.  
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?
Jan 8 '08 #1
Share this Question
Share on Google+
2 Replies


Minion
Expert 100+
P: 108
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:

Expand|Select|Wrap|Line Numbers
  1. Sub TableUpdate()
  2.  
  3.     If ArtistSource <> "" Then
  4.         DoCmd.RunSQL ("Insert into Artist (Artist) values ([artistsource])")
  5.     Else
  6.         MsgBox "You haven't selected an artist from the list or typed in a new one"
  7.     End If
  8.  
  9. End Sub
  10.  
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.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings = False
  2.  
Hope this helps.

- Minion -
Jan 8 '08 #2

P: 22
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.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings = False
  2.  
Hope this helps.

- Minion -
Jan 10 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.