docmd.openform wont open to selected record - help please!

[AishlinnAnne]

I have created a form to search through company quotes (frmSearchJobs) and display results in a listbox (lstResults) on the same form.
In addition, I have created a second form (frmTabbed) that contains 3 tabs (tabDetails, tabOptions, tabContacts).

I would like frmTabbed to open to the selected record when double-clicking on a quote in lstResults on frmSearchJobs.
When frmTabbed opens, the first tab you see is tabDetails, which contains a subform subJobDetails.

I have finally gotten the code to a point where when I double-click on a record on frmSearchJobs.lstResults that frmTabbed will open with no error messages.


However, when this opens, my tabs or subform no longer show, so it is just a blank screen. I am trying to connect the records using txtDetQuote.value (Quote_ID)

Private Sub lstResults_DblClick(Cancel As Integer)    DoCmd.OpenForm "frmTabbed", acNormal, , "[txtDetQuote]" = " & Me.lstResults.Column(0) & "
    DoCmd.Close acForm, "frmsearchjobs", acSaveNo
    'DoCmd.OpenForm Form_frmTabbed, acFormEdit, , Form_frmTabbed!subJobDetails.Form!txtDetQuote = Me.lstResults.Value.Column(0)
End Sub

Additionally, I would like to get tabOptions and tabContacts to display the same record as tabDetails will display. (once that actually works)

Could one of you please take a look and tell me if you see what I am doing wrong here?
I have attached a copy (with edited data) for you to review.

Thank you for any help you can provide!!!ForumHelpUFP.zip

[ranman256]

The parameter to open the form is based on the table field ,and the form box.

docmd.openform "myForm",,,"[id]=" & me.txtID

[AishlinnAnne]

The parameter to open the form is based on the table field ,and the form box.

docmd.openform "myForm",,,"[id]=" & me.txtID

I tried changing it to [QuoteNumber] instead of [txtDetQuote] and that still doesnt seem to work .Also, wont I have to reference Column(0) so the database knows to use the [QuoteNumber] that was selected?

Thanks again.

[ranman256]

your text is still wrong

DoCmd.OpenForm Form_frmTabbed, acFormEdit, , "[field]= " & Me.lstResults

[AishlinnAnne]

your text is still wrong

DoCmd.OpenForm Form_frmTabbed, acFormEdit, , "[field]= " & Me.lstResults

I actually tried it that way the first few times and the only way I can get Access to recognize the operator is to type it as:

    DoCmd.OpenForm "frmTabbed", acNormal, , "[QuoteNumber] " = " & Me.lstResults.Column(0) & ""

Maybe its something to do with Access 2016? That is the version I am currently working in.

[AishlinnAnne]

Another problem I seem to be having is that my form (frmTabbed) has 3 tab controls (tabDetails, tabOptions, tabContacts), each has its own subform (subJobDetails, subProjectOptions, subProjectContacts) that I want to show different areas of details for 1 record. When I use the navigation bar on frmTabbed (main form), it only filters through on tabOptions & tabContacts. For some reason tabDetails is not affected by the navigation buttons on the main form, it just stays showing details for 1st record, while the others two tabs sort through simultaneously. Any idea why that may happen? I will post separately if not, just figured I'd put it out there.

Thanks again.

[aytee111]

Are your link master/child fields set up the same as the other subforms?

[AishlinnAnne]

Are your link master/child fields set up the same as the other subforms?

Thank you!! This resolves the tabbing issue, however.. I still cant seem to get the frmSearchJobs selection from listbox to open up a record on frmTabbed.

Any suggestions on that?


Thank you!!

[aytee111]

frmTabbed should have a record source which contain the master links to the subforms. When you open the form from frmSearch you add a filter to the open statement so that it opens to that one record. DoCmd.OpenForm "frmTabbed",,,"fieldname=" & Me!lstResults.Column(column number)
Add single quotes if it is a text field.

[AishlinnAnne]

frmTabbed should have a record source which contain the master links to the subforms. When you open the form from frmSearch you add a filter to the open statement so that it opens to that one record. DoCmd.OpenForm "frmTabbed",,,"fieldname=" & Me!lstResults.Column(column number)
Add single quotes if it is a text field.

I dont know if you had a chance to look at the code, but i think that is what i have in there already, and when frmTabbed opens, its not like i get a form with blank fields, the form opens and the whole screen is white, cant even see the textboxes or tabs.

[aytee111]

No I hadn't! Your syntax is incorrect. See my previous post.

Where are the links for the subform on frmTabbed? (edit: fixed by now, I presume)

[AishlinnAnne]

No I hadn't! Your syntax is incorrect. See my previous post.

Where are the links for the subform on frmTabbed? (edit: fixed by now, I presume)

by links, do you mean parent/child? all 3 tabs are set for QuoteNumber, which is what i have as the [fieldname] and it still isnt working..

    DoCmd.OpenForm "frmTabbed", acNormal, , "'[QuoteNumber] = ' & Me.lstResults.Column(0) & ''"

^^ this gives me an error saying "undefined function 'me.lstresults.column' in expression." same goes if i change the periods to exclamations.

[aytee111]

You are still not following my instructions above. You are creating a string expression here and you need to learn how to do that, it is used everywhere. When in doubt, use a query to do it for you - open the query that is the record source for you frmTabbed, in the criteria under QuoteNumber enter something, then go to SQL view and you will see how it looks.

"fieldname = 'xyz'"
"fieldname = '" & Me.... & "'"

(You don't need column(0), that is the default column number)

[AishlinnAnne]

You are still not following my instructions above. You are creating a string expression here and you need to learn how to do that, it is used everywhere. When in doubt, use a query to do it for you - open the query that is the record source for you frmTabbed, in the criteria under QuoteNumber enter something, then go to SQL view and you will see how it looks.

"fieldname = 'xyz'"
"fieldname = '" & Me.... & "'"

(You don't need column(0), that is the default column number)

Im sorry, I know it seems like I am repeating myself, but I swear I am trying to go through each motion youre telling me and I still just can not comprehend how I am doing this incorrectly.. I even tried creating a query.
Does this have anything to do with not referencing the tab or the subform on the tab? frmTabbed contains 3 tabs, each tab has its own subform all with QuoteNumber as the child/parent link.
I appreciate your help and again I am sorry for what seems like I am repeating myself, I just can not seem to wrap my head around this today.

DoCmd.OpenForm "frmTabbed", , , "qryProjectDetails.QuoteNumber = ' & Me!lstResults.Column() & ' "

the field I am trying to get matched is on frmtabbed > on tabDetails > on subJobDetails > txtDetQuote (control source = qryProjectDetails.QuoteNumber)

[aytee111]

Post the SQL for the query I spoke of in post #13.

The field you are trying to reference is on the main part of the form, not on the subform.

- open form
- filter on quote number (in the OpenForm statement)
- use quote number as link to subform
- subform will show records for that quote number

Tab pages have no part to play in this, you can leave that out for this issue.

In post #11 I presumed that the links had been fixed since the original posting of your db - is that a correct assumption?