Add New record using a listbox

[g28dman]

Add New record using a listbox/ Added Attachment

I have a mainform where TaxID is used in a combo box to add new records. The problem is not all of my paperwork will have either a SSN or EIN on it to filter the combo.

What I would like to do is use a searchform that has a listbox w/names and TaxID -->This works as I use it to search my records.

Using a copy of this search form I would like to add a new record using TaxId

The code I am trying is

DoCmd.OpenForm "frmPolicyEntry", , , "tblPolicy.[TaxID] = " & lstResults.Column(0), , acDialog

I have tried different variations of this, and can get a new form to load but the TaxId is not updated.

Can someone help me for min?

***EDIT***
I have added an attachment of s stripped down db. I am still having problems trying to understand this, maybe someone could look at help, maybe my form is set up wrong.

 

[wazz]

if i understand what you are asking... i think you have a couple of choices:

1. if your search form is still open:
- in the Before Update event on frmPolicyEntry:

If IsNull(Me.TaxID) Then
Me.TaxID = Forms!frmYourSearchForm!lstResults.Column(0)
End If

2. if you close your search form when opening frmPolicyEntry:
- add OpenArgs to your code shown above:

DoCmd.OpenForm "frmPolicyEntry", , , "tblPolicy.[TaxID] = " & lstResults.Column(0), , acDialog, Me!lstResults.Column(0)

- in the Before Update event on frmPolicyEntry:

If IsNull(Me.TaxID) Then
Me.TaxID = Me.OpenArgs
End If

i haven't tested this but hopefully it's in the right direction. hth.

 

[g28dman]

Thanks you understood correctly, I am trying the different methods, but am still ending up with same outcome - Open form acts as though being updated but no TaxID in the combobox for new record.

Thank you for your help - I will keep trying with the directions you provided, I knew I had to do something with open args but wasnt sure exactly what.

 

[g28dman]

I have added an attachment where someone could tell me if I have my form set up wrong or not or give me help on the above mentioned question.

 

[wazz]

your sample is confusing to me so i don't know if this will help but i'll add a few notes and maybe someone else can see what you're up to.
1. (slightly off-topic): if you can, change your PK (ID) field in tblcustomer to autonumber (CustomerID) and make the relationship with tblPolicy on CustomerID, not TaxID. tax policies and codes can change, let the db take care of IDs.
2. i think your main form's query is (perhaps) not what you want. it's confusing. remember that if you have two tables in your query (customers and policy in this case) you will only get records that have common data. so, if a customer doesn't have a policy yet, the customer won't be in the results of your query. (unless you change to an outer join).
3. the most confusing part is that you say you want to add a record, but you are searching on existing records. then, after the search you return to a form that only shows existing records. in other words, you search customers then return to a form that only shows customers who have a policy (see 2). i can't tell if your form is for customers or policies or both (your query implies both). can you make separate forms for customers and policies? i think that would help.

NEW: i think part of the problem is that you have turned to TaxID into a combobox which can't be updated directly (plus, you are using the foreign key TaxID). if you want to find a record based on an existing TaxID add an *unbound* combobox for quick searches on the form and leave the TaxID field as a textbox. hth.