Update Query?

[pclutts]

I have a text field (named ProjNo) in table tblBidTabs where project numbers now exist. I want to convert that field to a combo box where I look up the project number names in table tblProjects. However, I don't want to loose the Project Number data that already exists in tblBidTabs. Also, when I enter data in the ProjNo combo box on the form, I want to have the option of either selecting a Project Number from the pick list or entering a new number. If I enter a new number, it needs to become part of a permanent new record in tblProjects. Would an Update query be the way to go? Can someone give me detailed info on how to do solve my problem? Thanks in advance for any help.

 

[Lightning]

Here are two methods of adding to a lookup list directly from a form.

'====================================================================
'Contains two methods of updating a look-up list. Both for Access 97.
'The first method updates a look-up list with only one field,
'and works directly from the "Not In List" event itself.
'====================================================================
Private Sub Company_NotInList(NewData As String, Response As Integer)
'This event procedure is called from the
'ComboBox's NotInList event

'The procedure adds the Company Name to the
'underlying table upon confirmation from the user

'does user want to add new value to list?
If MsgBox("Add " & NewData & " to list?", 33, "Company Name&quot = 1 Then

'Inform Event Procedure we're handling the error
Response = acDataErrAdded

'Declare the database and Table
Dim NewCompany As String
Dim db As Database
Dim TB As Recordset
Set db = CurrentDb

NewCompany = NewData
'Open the required table:
Set TB = db.OpenRecordset("tblCompany", dbOpenTable)

'Prepare Table for new record being added
TB.AddNew

'Write Data to fields
TB("Company&quot = NewCompany
TB.Update
TB.Close

Else
'Cancel the event returning the user to
'the combo box
DoCmd.CancelEvent

'inform the event procedure to ignore
'errormessage
Response = acDataErrContinue
End If

End Sub
'========================================================================
'The second method is for lookup tables with more than one field. This
' method pops up a message box from the "Not In List" event advising the
' user to double click the combo box. The DoubleClick event then opens
' the lookup table to allow data to be entered in all fields.
'========================================================================
Private Sub RecordingArtistID_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub


Private Sub RecordingArtistID_DblClick(Cancel As Integer)
On Error GoTo Err_RecordingArtistID_DblClick
Dim lngRecordingArtistID As Long

If IsNull(Me![RecordingArtistID]) Then
Me![RecordingArtistID].Text = ""
Else
lngRecordingArtistID = Me![RecordingArtistID]
Me![RecordingArtistID] = Null
End If
DoCmd.OpenForm "Recording Artists", , , , , acDialog, "GotoNew"
Me![RecordingArtistID].Requery
If lngRecordingArtistID <> 0 Then Me![RecordingArtistID] = lngRecordingArtistID

Exit_RecordingArtistID_DblClick:
Exit Sub

Err_RecordingArtistID_DblClick:
Msgbox Err.Description
Resume Exit_RecordingArtistID_DblClick
End Sub

Converting your table field to a lookup should not affect the data already in your table, but make sure you back up your table first.

HTH
Lightning