combo box on dbl click open form linked to combo box

[angie]

Hi I have tried several ways to get this to work and I don't understand what I am doing wrong.
I have a combo box on my main form "customer" the company box is "company owners name"
I have another form that I enter detail information into for the name selected in the combo box.
I would like to select the name in the combo box and than double click and have the detail form to open to the person selected in the combo box.
the information in the detail form that matches the combo box is " company or owner"

I have tired doing a embedded marcro, which will open when I double click the combo box but will not open to match the name selected in the combo box.
I have also tried the following :


Private Sub Company_Owners_Name_DblClick(Cancel As Integer)
On Error GoTo Err_Owners_Info_Button_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "company owner name details"

stLinkCriteria = "[company owners name]=" & "'" & Me![Company or owner] & "'"



DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Owners_Info_Button_Click:
Exit Sub
Err_Owners_Info_Button_Click:
MsgBox Err.Description
Resume Exit_Owners_Info_Button_Click
End Sub


Please help I know I should be able to do this but I just don't know what I am doing wrong... Thanks Angie

[pbaldy]

From the sound of it, I'd expect this:

Me![Company or owner]

to refer to the combo being clicked in. Is that not the case?

[June7]

Is the combobox multi-column? Is a company ID one of the columns? Is this the field combobox is bound to? If so, that is the value of the combobox, not company name. Use company ID as filter criteria.

Show the RowSource sql.

[Missinglinq]

Assuming that the [company owners name] Field is defined as Text, as the name suggests

stLinkCriteria = "[company owners name]=" & "'" & Me![Company or owner] & "'"

should be

stLinkCriteria = "[company owners name]= '" & Me![Company or owner] & "'"

Linq ;0)>

[pbaldy]

Assuming that the [company owners name] Field is defined as Text, as the name suggests

stLinkCriteria = "[company owners name]=" & "'" & Me![Company or owner] & "'"

should be

stLinkCriteria = "[company owners name]= '" & Me![Company or owner] & "'"

Linq ;0)>

What would be the difference in the resulting string?

[angie]

I am still having problems,, I have a table name company owner and a table named customers. I have a form names customers with a combo box name company or owner which is linked to the company owner table and you can select the field in company owner table (company owners name) this all works . but I have appended records from another database to this table company owner, the problem is when I click on the combo box after making my selection and the name appears it will open up the detail form but nothing is showing , I have used the code above and it still does not work. I know its not the code because I can enter new information and it works fine it just the records that I appended with a query that is not working. any guess what I have done? I hope you can understand what I am trying to say.

[pbaldy]

Can you attach the db here?

[angie]

Angie.zip
Angie.zip
Hi I have attached a smaller version of my db I am only sending you what you should need since it is so large. The problem is with in the Customer form , the company owner combo box and the contractor or dealer combo box. along with the box under each. The first records show where the combo box is not not working , the second records shows where it is working. If I were to enter a new record for either combo box it works fine its just the information I have appended from another database we use. If I need to send you anything else please let me know. This is the first time I have sent a bd as a attachment so I hope it works. Thanks Again Angie

[June7]

Why are you saving the company name and contractor name instead of the primary key ID of each into Customer table? This works:

1. add number fields for the ID values in Customer table named CompID, ContID and populate and bind comboboxes to those fields.

2. change combobox RowSource to include the ID field
ColumnCount: 6
ColumnWidths: 0";2.0";1.0";1.0";1.0";1.0"

3. change the expression in textbox to reference appropriate columns

Then use the ID value as criteria for opening filtered form.

Or you can change the primary key designation in the two tables after eliminating duplicate values.

Alison Mather has a space in front of name.

There is record in Contractors with no company name - Able Housing is in the Contact field.

[angie]

Hi June, Thanks I have everything working so far, The only problem I am having is the expression in the textbox I have working but it adds a line in between each line like this :

Angie

3333 Castle Dr

Tallahassee Fl 32455
I am using the following : =[CompID].COLUMN(1) & Chr(13) & Chr(10) & [CompID].COLUMN(2) & Chr(13) & Chr(10) & [CompID].COLUMN(3) & " " & [CompID].COLUMN(4) & " " & [CompID].column(5)

and also I have tried to write a code for on dbl click to open the form " company owner name details" for the name selected in the compID combo box.

Could you please help me with these two things. Thanks Again Angie

[June7]

The extra line is because there is a CrLf embedded in the address in the field. Click into the address field in Company table at end of the visible line, then press down arrow key. Sorry, I am not having any luck removing them with Find/Replace.

Two more things about the data: most of the state/province abbreviations are preceded with a space and city names are followed by a space (this latter is very odd) in the Company table.


DoCmd.OpenForm "COMPANY OWNER NAME DETAILS", , , "ID=" & Me.[Company Owners Name]


Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[angie]

I will try this today and let you know
i wonder if the problem with the spacing comes from where I had a text field with the owner name, address, city, state and zip was together. Than I used the following code in a query to get the first line left([company],instr([company],chr(10))). Maybe it left a space after each line? Should I have used a -1 to take the space off? Just a thought. Thanks Angie

[June7]

Yes, -1 probably would do that. Find/Replace doesn't work so try an UPDATE query to remove.

UPDATE CompanyOwnerDetails SET Address = Left([Address], InStr(Nz([Address], "X"), Chr(10)) - 1)

[angie]

Hi June7
Thank you so much I got everything but one thing working great. I have the not on list written as this : Private Sub CompID_NotInList(NewData As String, Response As Integer)
DoCmd.OpenForm "company owner NAME details", acNormal, , , acFormAdd, acDialog, NewData
Me.CompID = NewData
Response = acDataErrAdded


End Sub

But I need it to update the customer form (field : compID )
I have the field set to limit to list and also the above code works when I enter new data but it does not update the field on the customer form. could you please help me with last problem.. Thanks Again Angi

[June7]

The combobox must be requeried. See if these help
http://allenbrowne.com/ser-27.html
http://www.fontstuff.com/access/acctut20.htm