lookup field in table

[gisel]

Hi I have a question regarding a table that I have of
agency names, called [agency profile]. I have another
table that stores the addresses (for the specific agency
called [workinfo]. In the [workinfo] table I want the
field agencyname to "lookup" to the "agencyname" field in
the [agency profile] table and whenusers are typing in
this field from the form view they are able to choose an
item from the list or if it's not in the list type it in
and add it to the [agency profile] table. any suggestions
on how I can do this?!

Right now I have the "agencyname" field from the workinfo
table (which looks-up the value from the [agency profile]
table)in the workinfo form and it pulls the list correctly
but when we add an item that's not in [agency profile]
table it adds it to the [workinfo] table, not the [agency
profile] table therefor when your entering new information
and go to the "agencyname" field, the new item added does
not appear in the [agency profile] table. I know there's a
step i'm missing in between these actions but can't figure
out what I need to do next! Can anyone help me?!

gisel
(e-mail address removed)

 

[Jeff Boyce]

Gisel

Check Access HELP on the NotInList event -- there's an example of the code
you'd use to add a new record to your [agency profile] table.

By the way, I'm not clear from your description whether you've used the
"lookup" data type within your [workinfo] table. If so, please review the
tablesdbdesign newsgroup for posts related to the "lookup" data type -- it
causes many headaches and much confusion!

 

[gisel]

Hi Jeff, I looked at the help section and it's very
hyelpful but I can't seem to find exactly what the code
behind the even procedure needs to be to make it add new
item to the "agencyname" field on the [agencyprofile]
table. This is what i inserted in the "onnotinlist" event
box: event procedure and the code is, but it doesn't
work. what should the exact code be for it to add the
item if it's not in the list? thanks sooo much!!!

Private Sub Combo3_NotInList(NewData As String, Response
As Integer)

Button1.OnClick = "[Event Procedure]"

End Sub

 

[Jeff Boyce]

Gisel

I don't recognize the code snippet you provided.

When I check Access HELP for NotInList, I get:

<Microsoft Access VB HELP>
Private Sub Colors_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Colors
' Prompt user to verify they wish to add new value.
If MsgBox("Value 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 row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End SubNote The above example adds an item to an unbound combo box. When
you add an item to a bound combo box, you add a value to a field in the
underlying data source. In most cases you can't simply add one field in a
new record - depending on the structure of data in the table, you probably
will need to add one or more fields to fulfill data requirements. For
instance, a new record must include values for any fields comprising the
primary key. If you need to add items to a bound combo box dynamically, you
must prompt the user to enter data for all required fields, save the new
record, and then requery the combo box to display the new value.

<Microsoft Access VB HELP>

Of course, you'll need to modify this to fit your situation