select id from combobox, create record if not in table...

[mycotropic]

HI;
OK I don't know why I am stymied here but;
I want to use a combo box on a form to select an id from one table(A), if that record exists in the forms' table(B) then go to that record. if it is NOT(and here is why I am writing), then create that record in table (B).

I can find the record I want alright - I cannot get it passed into a new record...

THanks for the help, I am currently using;

Sub visitnumberlookup_AfterUpdate()
Me.RecordSource = "SELECT TTobaccoAlcoholHX.* FROM TTobaccoAlcoholHX ORDER BY TTobaccoAlcoholHX.visitnumber;"
Me.RecordsetClone.FindFirst "[visitnumber]='" & Me![visitnumberlookup] & " '"
Me.Bookmark = Me.RecordsetClone.Bookmark
'Me!visitnumberlookup.Value = Null
DoCmd.
End Sub

to go to the chosen record...

greg kinney "Man is the cheapest computer we can put into a spacecraft and the only one we can mass-produce with unskilled labor." Werner von Braun

 

[gol4]

If I understand what you are trying to do you need to use the not in list event to trigger code that will allow you to add the record. The is an excellent posting in the FAQ under Access general that describes how to do this.

 

[mycotropic]

I followed the advice in the faq - i included the 'not in list' event and prompted the user if they want to add what they entered to the list. unfortunatly the combo box is filled with a query of the underlying table.

the rows in the table are one of three things;

already complete.
ID present but data not complete.
ID not present.

when the user enters an ID that is not on the list I want the underlying table to then include that new row so that the user can enter the data for that person.

what i need ot know is, how do i modify what is below to, not update the combobox list, but update the table and and let my user start updating the new ID:
(i really have tried but I know I am approaching this the wrong way!!!)

Private Sub visitnumberlookup_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_YourCombo_NotInList

Dim ctl As Control
Dim strSQL As String

' Return Control object that points to combo box.
Set ctl = Me!Status
' Prompt user to verify they wish to add new value.
If MsgBox("Item is not in list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to value list
Status.RowSource = Status.RowSource & ";" & NewData
ctl.Value = NewData
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

Exit_YourCombo_NotInList:
Exit Sub

Err_YourCombo_NotInList:
MsgBox Err.Description
Resume Exit_YourCombo_NotInList

End Sub


"Man is the cheapest computer we can put into a spacecraft and the only one we can mass-produce with unskilled labor." Werner von Braun

 

[JGillespie]

You need to open the recordset for the table you wish to add a record to and use the AddNew method to create a new record. Search through Access help for AddNew and you should find an example you can copy.

Whne this new record has been added you will then need to requery your form and move to the newly added record to allow your users to fill in the missing details.

If this is too brief let me know and I will post full code.
Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[mycotropic]

Unfortunatly the computer I found on my desk when I took this job ALWAYS slams Access shut when I look at the help menu. I went to the Tek-Tips and searched AddNew and found the post I originaly used to get THIS far. That post is great for addnew-ing a combo-box but not the underlying table.

Any help you can give will reduce my risk of stroke by a large amount......

And thank you for helping

Greg Kinney "Man is the cheapest computer we can put into a spacecraft and the only one we can mass-produce with unskilled labor." Werner von Braun

 

[gol4]

Here is how to add a record using DAO
dim rst as recordset
set rst = currentdb.openrecordset("youtablename&quot
rst.addnew
rst!field1name = "what ever value you want to add"
rst!field2name = " add here also"
' do like the above example for each of your fields
rst.update
rst.close

or USING SQL
INSERT INTO YOURTABLE( FIELD1NAME, FIELD2NAME)
SELECT "VALUE1" , "VALUE2"

use either method you prefer

As far as the help file I would try a reinstall of the help files they often prove invaluable.