Error when changing 'Link Child Fields' property

JackNimble · October 22nd, 2004, 05:30 PM

Hi everyone,

I am getting a really strange error when I try to change the 'Link Child Fields' or 'Link Master Fields' property of a subform. It states:

Quote:

quote:Object variable or With block variable not set

I have 2 subforms, each nested within the form above it. The top form is frmVendor, the first nested subform is sfrmVenAddr, and the second nested subform is sfrmVenContact.

The forms work together like this: The user chooses a vendor from a combo box [cboSelectVendor]in the top form. This causes the sfrmVenAddr subform to populate with all address locations for that vendor, via a query matched to the [cboSelectVendor].

The user then selects the correct address from another combo box [cboSelectAddr]in the header of sfrmVenAddr, which causes the sfrmVenContact subform to populate will all contacts for that specific address, via another query matched to [cboSelectAddr].

Finally, the user can select a specific contact from a third combo box [cboSelectContact] in the header of sfrmVenContactto, display all of the contact information for that person.

My problem is this. I have a listbox control in sfrmVenContact that is supposed to display all phone numbers for the selected contact, via a query matched to [cboSelectContact]. However, the query doesn't return anything, even though I've modeled it on the two other queries, which both work.

After doing some research, I decided to double-check that all of my subforms were linked correctly via their Link Master / Child fields, but when I clicked the [...] Build button, I got the error message above.

Does anyone have any advice or suggestions? I'm tearing my hair out here...

Here is the query for the phone number listbox.

Code:

SELECT tblVEN_CNTCT_PHN.VEN_CNTCT_PHN_UID,
       tblVEN_CNTCT_PHN.VEN_CNTCT_PHN_NUM,
       tblVEN_CNTCT_PHN.VEN_CNTCT_PHN_TYPE

FROM tblVEN_CNTCT
      INNER JOIN (tblVEN_CNTCT_PHN
                   INNER JOIN tblVEN_CNTCT_PHN_LOC_REL
                   ON tblVEN_CNTCT_PHN.VEN_CNTCT_PHN_UID = tblVEN_CNTCT_PHN_LOC_REL.VEN_CNTCT_PHN_UID)
      ON tblVEN_CNTCT.VEN_CNTCT_UID = tblVEN_CNTCT_PHN_LOC_REL.VEN_CNTCT_UID

WHERE (((tblVEN_CNTCT_PHN_LOC_REL.VEN_CNTCT_UID)=[Forms]![frmVendor]![sfrmVenAddr].[Form]![sfrmVenCntct].[Form]![cboSelect_Contact]));

And here are some screen captures of the database itself...

http://home.mn.rr.com/jbarker2/screenshot1.gif
http://home.mn.rr.com/jbarker2/screenshot2.gif
http://home.mn.rr.com/jbarker2/screenshot3.gif
http://home.mn.rr.com/jbarker2/screenshot4.gif
http://home.mn.rr.com/jbarker2/screenshot5.gif

Thanks everyone.... PLEASE HELP!!!

JackNimble

JLovell · October 22nd, 2004, 05:49 PM

Jack-

Does your project compile? It's possible the builder is failing because of an error in your project. I can't say I've ever used that builder myself, but I just tried it in one of my databases, and appears to work just fine.

Have you tried adding:

Me.lstPhones.Requery

... in the AfterUpdate event of cboSelectContact? (Where lstPhones is the name of the listbox.) You could also try setting the RowSource property of the list box to the SQL you need -- including the filter on the selected contact embedded directly.

John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

JackNimble · October 25th, 2004, 02:10 PM

Hi John,

Thanks for the suggestion... The Me!lstPhnNums.Requery did the trick... My phone numbers are now showing up in the list box... HOwever, I am still getting that error message when I try to change the Link Master / Child field values... Not sure what it means, but at the moment I guess I can ignore it...

Btw, would you happen to know how I can tell the combo boxes in my embedded subforms to automatically select the first list item whenever the main form [cboSelectVendor] changes?

Right now, when I change vendors, the selection combo-boxes for my sub forms still show the data from the last vendor selected, even though the data on the rest of the subform has changed to show the correct information.

Thanks!

JLovell · October 25th, 2004, 03:21 PM

Well, we got one problem solved. It's still not clear why you're attempting to change the Link properties. Can you post the code that's running when you get the error and explain what it's trying to do?

As for setting the value, you can either set the combo box to Null when you requery it or - to set it to the first item on the list - set it to ItemData(0).

Me.lstPhones = Me.lstPhones.ItemData(0)

John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"