Update Query????

[Brent113]

I have a lookup table which is used by 3 combo boxes on the same form. I want to be able to input a value that is not in any of the combo boxes and have it added to the table. How do I do this? I have created three update queries and here is the SQL for the first. The other two have the appropriate changes to the code, but are otherwise Identical.

The table's name is tblLookup-Medium, the boxes are Medium1 though Medium3

UPDATE [tblLookup-Medium] RIGHT JOIN tblNeeds ON [tblLookup-Medium].Medium = tblNeeds.Medium1 SET [tblLookup-Medium].Medium = tblneeds.medium1
WHERE ((([tblLookup-Medium].Medium) Is Not Null));

Any help is welcome. Thanks

____________________________________________________________________________
There are only 10 types of people in the world. Those that know binary and those that don't

Brent

 

[markgrizzle]

Just a quick suggestion. The combos have a not in list event. You can use it to run an append query to add the new value to the lookup table. Once that's done, requery the combo's rowsource to display the new value.

 

[swaybright]

Here is code I use in the notinlist event of a combobox. It will open the researcher data entry form and allow entry not just in combobox field but in all fields of the record. If you don't need to open an entry form delete out the open form statement but leave the response statement. Of course you can cut out the message box too if you want. The notinlist property will automatically add the newdata to the table when you use Response = DATA_ERRADDED

hope that helps

Shane

Private Sub Researcher_NotInList(NewData As String, Response As Integer)
Dim Message As Integer, MsgTitle As String, MsgDialog As Integer
Const MB_YESNO = 4
Const MB_ICONEXCLAIM = 48
Const MB_DEFBUTTON2 = 256, IDYES = 6, IDNO = 7
MsgTitle = "New Researcher"
MsgDialog = MB_YESNO + MB_ICONEXCLAIM + MB_DEFBUTTON2
Message = MsgBox("The researcher you chose is not in the list" _
& vbCrLf & "Do you want to add a new researcher?", _
MsgDialog, MsgTitle)
If Message = IDNO Then
Response = DATA_ERRCONTINUE
Else
DoCmd.OpenForm "Researcher", acNormal, , , acFormAdd, acDialog
Response = DATA_ERRADDED
End If


End Sub

 

[Brent113]

Ok, thanks guys, but I've run into a new problem. The only way I can get access to recognize it isn't in the list is to set the "limit to list" property to yes. But doing this causes it to run the code, then reject it. How do I get around this?

____________________________________________________________________________
There are only 10 types of people in the world. Those that know binary and those that don't

Brent

 

[markgrizzle]

That's strange, it should work. Are you inputting the new value into the combos? Whichever is getting the new value should have swaybright's code in its not in list event.

 

[Brent113]

so do I have the limit to list property set to no?

____________________________________________________________________________
There are only 10 types of people in the world. Those that know binary and those that don't

Brent

 

[swaybright]

The limit to list property must be set to yes in order to run code from the notinlist event. What do you mean by reject it. Does the value not get added to the list?

 

[Brent113]

yep. that's what I mean. what happens is a pop up says "The data you entered is not in the list" or something similar and it then doesn't run the code, or at least doesn't add it.

____________________________________________________________________________
There are only 10 types of people in the world. Those that know binary and those that don't

Brent

 

[swaybright]

Hmmm. interesting. Could you post the code you're using?

 

[Brent113]

i can, but there is no point to it. I have just adapted the above code to my database. actually, I haven't even changed any messages yet.

____________________________________________________________________________
There are only 10 types of people in the world. Those that know binary and those that don't

Brent

 

[Brent113]

everything is the same except I documented out the DoCmd.openform line. could this be the problem? how do I get it to not open a new form, just add it into the table. also, then after the msgbox is clicked, access says "the item is not in the list. . . . . ." and it doesn't get added

____________________________________________________________________________
There are only 10 types of people in the world. Those that know binary and those that don't

Brent

 

[swaybright]

Oops, my bad. I just got off my lazy bum and recreated your error. I was wrong in assuming that simply deleting the open form command would work. You need to run an append sql statement to add the newdata. See thread705-592094 for the way to do this.
Sorry for misleading you. I always use forms for adding data to a combo box, so I didn't think the problem through before mouthing off.

 

[Brent113]

thank you. I don't have time now, but I will do it in the near future. Thanks again, brent

____________________________________________________________________________
There are only 10 types of people in the world. Those that know binary and those that don't

Brent