Solved

Unbound form with unbound subform.

Posted on 2007-04-11
33
598 Views
Last Modified: 2013-11-28
I have an unbound form which only displays certain data from a table depending on what data priviledges the user has. Now I wish to include a subform which will show data that matches a field on the unbound form.

The reason I need to do it this way is for data editing/entry purposes. I need to amend the data on the unbound form but not the subform.

The code i use on the On Load event of the unbound form called frmRelatedContactsAilsaForums is as follows:

Private Sub Form_Load()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
   
    cnn.ConnectionString = "DSN=HabiaLocal"
    cnn.Open
   
    With rst
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT tblContacts.contacts_ID, tblContacts.Post_Code, tblContacts.Real_name, tblContacts.Surname, tblContacts.title_ID, tblContacts.Occupation, tblContacts.Organisation, tblContacts.Tel_No, tblContacts.Tel_No2, tblContacts.Mobile, tblContacts.Fax_No, tblContacts.Author_email, tblContacts.Homepage, tblContacts.contact_type_ID FROM tblContacts WHERE (((tblContacts.contact_type_ID)=5 Or (tblContacts.contact_type_ID)=6 Or (tblContacts.contact_type_ID)=7 Or (tblContacts.contact_type_ID)=24))", cnn

    End With
   
    Set Me.Recordset = rst
   
    Set rst = Nothing
    Set cnn = Nothing
End Sub

The code i use on the On Load event of the unbound subform called frmRelatedContactsAilsaForumsSubAddress is as follows:

Private Sub Form_Load()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
   
    cnn.ConnectionString = "DSN=HabiaLocal"
    cnn.Open
   
    With rst
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT tblMember.Address_1, tblMember.Address_2, tblMember.Address_3, tblMember.City, tblMember.County, tblMember.Location, tblMember.Post_Code FROM tblMember WHERE tblMember.Post_Code='" & Forms![frmRelatedContactsAilsaForums]![Post_Code] & "'", cnn
    End With
   
    Set Me.Recordset = rst
   
    Set rst = Nothing
    Set cnn = Nothing
End Sub

I am having issue with the following syntax:

Forms![frmRelatedContactsAilsaForums]![Post_Code]

If I open the unbound form frmRelatedContactsAilsaForum on its own without a subform then open the subform frmRelatedContactsAilsaForumSubAddress it works, but if I put the subform back into the form frmRelatedContactsAilsaForum I get the following error:

Run-time error '2424':
The expression you entered as a field, control, or property name that Domestic - Habia can't find.

Any help would be much appreciated, NewAS.
0
Comment
Question by:NewAS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 15
33 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18890329
the problem you are having is due to the fact that the subform is loading first,
thus the reference to the Main form  {Forms![frmRelatedContactsAilsaForums]![Post_Code]} is raising the error
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18890347
try this
move your code from the subform's form_load into a public procedure within that form
then main form's form_load calls this public procedure

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18890358
you can move these codes

"SELECT tblMember.Address_1, tblMember.Address_2, tblMember.Address_3, tblMember.City, tblMember.County, tblMember.Location, tblMember.Post_Code FROM tblMember WHERE tblMember.Post_Code='" & Forms![frmRelatedContactsAilsaForums]![Post_Code] & "'", cnn

in the load event of the main form and set the recordsource of the subform
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:NewAS
ID: 18890657
I like the thought of the adding the subform code to the main form, could you look at my code and tell me the best way to have two database connections and how i reference the second to the subform?

Regards, NewAS.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18890721
try this codes in the load event of the main form

Private Sub Form_Load()

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
   
    cnn.ConnectionString = "DSN=HabiaLocal"
    cnn.Open
   
    With rst
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT tblContacts.contacts_ID, tblContacts.Post_Code, tblContacts.Real_name, tblContacts.Surname, tblContacts.title_ID, tblContacts.Occupation, tblContacts.Organisation, tblContacts.Tel_No, tblContacts.Tel_No2, tblContacts.Mobile, tblContacts.Fax_No, tblContacts.Author_email, tblContacts.Homepage, tblContacts.contact_type_ID FROM tblContacts WHERE (((tblContacts.contact_type_ID)=5 Or (tblContacts.contact_type_ID)=6 Or (tblContacts.contact_type_ID)=7 Or (tblContacts.contact_type_ID)=24))", cnn

    End With
   
    Set Me.Recordset = rst
   
    Set rst = Nothing
    Set cnn = Nothing



    Dim cnn2 As New ADODB.Connection
    Dim rst2 As New ADODB.Recordset
   
    cnn2.ConnectionString = "DSN=HabiaLocal"
    cnn2.Open
   
    With rst2
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT tblMember.Address_1, tblMember.Address_2, tblMember.Address_3, tblMember.City, tblMember.County, tblMember.Location, tblMember.Post_Code FROM tblMember WHERE tblMember.Post_Code='" & Forms![frmRelatedContactsAilsaForums]![Post_Code] & "'", cnn2
    End With

'try either of these two lines
    Set Me.frmRelatedContactsAilsaForumsSubAddress.Recordset = rst2    
   ' Set Me.frmRelatedContactsAilsaForumsSubAddress.Form.Recordset = rst2
   
    Set rst2 = Nothing
    Set cnn2 = Nothing

end sub
0
 

Author Comment

by:NewAS
ID: 18896301
Nearly there, but when I navigate to another record using the standard record navigation buttons, the subform record does not update, could you please advise?

The above code worked with Set Me.frmRelatedContactsAilsaForumsSubAddress.Form.Recordset = rst2

Regards, NewAS.

0
 

Author Comment

by:NewAS
ID: 18896903
Could someone please advise as to how to get the rst2 recordset to refresh when using the main form record navigation buttons.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18897034
did you set the links master/child fields ?
0
 

Author Comment

by:NewAS
ID: 18897226
I didnt think you could use master/child fields with unbound forms, am i wrong?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18897323
hmm??
can you zip and upload your db here
http://www.ee-stuff.com
remove sensitive data and workgroup if there are any.
0
 

Author Comment

by:NewAS
ID: 18897450
I would prefer not to, could you please advise around this?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18897573
try in the main form current event

Private Sub Form_Current()
    Me.frmRelatedContactsAilsaForumsSubAddress.requery
   ' Me.frmRelatedContactsAilsaForumsSubAddress.Form.requery
End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18897654
you can try this too

Set Me.frmRelatedContactsAilsaForumsSubAddress.Form.Recordset = rst2
    Me.frmRelatedContactsAilsaForumsSubAddress.LinkChildFields = "Post_Code"
    Me.frmRelatedContactsAilsaForumsSubAddress.LinkMasterFields = "Post_Code"
0
 

Author Comment

by:NewAS
ID: 18897677
Tried both with no joy. I have a button that runs a similar query to filter the main dataset even further:

Private Sub btnEmploymentLaw_Click()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    cnn.ConnectionString = "DSN=HabiaLocal"
    cnn.Open
   
    With rst
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT tblContacts.contacts_ID, tblContacts.Post_Code, tblContacts.Real_name, tblContacts.Surname, tblContacts.title_ID, tblContacts.Occupation, tblContacts.Organisation, tblContacts.Tel_No, tblContacts.Tel_No2, tblContacts.Mobile, tblContacts.Fax_No, tblContacts.Author_email, tblContacts.Homepage, tblContacts.contact_type_ID FROM tblContacts WHERE (((tblContacts.contact_type_ID)=24))", cnn
    End With
   
    Set Me.Recordset = rst
   
    Set rst = Nothing
    Set cnn = Nothing
   
    Dim cnn2 As New ADODB.Connection
    Dim rst2 As New ADODB.Recordset
   
    cnn2.ConnectionString = "DSN=HabiaLocal"
    cnn2.Open
   
    With rst2
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT tblMember.Address_1, tblMember.Address_2, tblMember.Address_3, tblMember.City, tblMember.County, tblMember.Location, tblMember.Post_Code FROM tblMember WHERE tblMember.Post_Code='" & Forms![frmRelatedContactsEmmaClubMembers]![Post_Code] & "'", cnn2
    End With

    Set Me.frmRelatedContactsEmmaClubMembersSubAddress.Form.Recordset = rst2
   
    Set rst2 = Nothing
    Set cnn2 = Nothing
End Sub

This works well for the first record, but as soon as you navigate to the next record the subform will not update, this is driving me mad.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18897741
in design view of the main form
select the outer box of the subform hit F4
In the Data tab , what is listed in the Source Object ?
is it the same name as the subform  { frmRelatedContactsAilsaForumsSubAddress } ?  
if not, post the name of the Source Object.

btw, do you have another subform in that main form {frmRelatedContactsEmmaClubMembersSubAddress } ?
0
 

Author Comment

by:NewAS
ID: 18897816
If i put Set Me.frmRelatedContactsEmmaClubMembersSubAddress.Form.Recordset = rst2 on the On Current event I get an Object Required error which I think is due to the On Load event which actually defines the recordset.

The dont work either:

    Me.frmRelatedContactsEmmaClubMembersSubAddress.LinkChildFields = "Post_Code"
    Me.frmRelatedContactsEmmaClubMembersSubAddress.LinkMasterFields = "Post_Code"

I get Run-time error '31':
Data provider could not be initialised.

Any thoughts?

Probably because Post_Code is not
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18897844
NewAS,
take a look at my last post http:#a18897741 
0
 

Author Comment

by:NewAS
ID: 18897855
I changed the name of the form frmRelatedContactsAilsaForumsSubAddress to frmRelatedContactsEmmaClubMembersSubAddress, sorry for the confusion.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18897866
what about the other questions?
0
 

Author Comment

by:NewAS
ID: 18897872
The source object is frmRelatedContactsEmmaClubMembersSubAddress.
0
 

Author Comment

by:NewAS
ID: 18897935
We seemed to cross over a little with our messages, have I answered your questions properly?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18897957
try this

    With rst2
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT tblMember.Address_1, tblMember.Address_2, tblMember.Address_3, tblMember.City, tblMember.County, tblMember.Location, tblMember.Post_Code FROM tblMember WHERE tblMember.Post_Code='" & Forms![frmRelatedContactsEmmaClubMembers]![Post_Code] & "'", cnn2
    End With

'insert the following remove later
       rst2.movelast
       msgbox rst2.recordcount
'-------


    Set Me.frmRelatedContactsEmmaClubMembersSubAddress.Form.Recordset = rst2

post here the content of the message box  
is it  1  ?

0
 

Author Comment

by:NewAS
ID: 18898047
Yes it is 1.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18898072
so, don't expect it to move to the next record, coz there is no next record.
0
 

Author Comment

by:NewAS
ID: 18898152
No what i mean is that the main form frmRelatedContactsEmmaClubMembers recordset rst has 665 records and i am navigating these records, I want the subform frmRelatedContactsEmmaClubMembersSubAddress to refresh everytime I move to another record on the form frmRelatedContactsEmmaClubMembers.

Sorry if I was not clear.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18898311
then  place this in the current event of the main form
or place these codes in a sub and call the sub in the current event of the main form

    Dim cnn2 As New ADODB.Connection
    Dim rst2 As New ADODB.Recordset
   
    cnn2.ConnectionString = "DSN=HabiaLocal"
    cnn2.Open
   
    With rst2
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT tblMember.Address_1, tblMember.Address_2, tblMember.Address_3, tblMember.City, tblMember.County, tblMember.Location, tblMember.Post_Code FROM tblMember WHERE tblMember.Post_Code='" & Forms![frmRelatedContactsEmmaClubMembers]![Post_Code] & "'", cnn2
    End With

    Set Me.frmRelatedContactsEmmaClubMembersSubAddress.Form.Recordset = rst2
   
    Set rst2 = Nothing
    Set cnn2 = Nothing
0
 

Author Comment

by:NewAS
ID: 18898657
Ok tried that and it just crashed my database. I also tried removing the above code from the On Load event in case or possible duplication of code and it still crashed, it doesnt like being in the Current event.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18898798
lets use the the codes in the load event of the main form,
but remove the Where Clause for rst2

let's try to filter the recordset in the subform in the current event of the main form
with


Me.frmRelatedContactsEmmaClubMembersSubAddress.Form.Filter="[Post_Code]='" & me.[Post_code] &"'
Me.frmRelatedContactsEmmaClubMembersSubAddress.Form.FilterOn=True
Me.frmRelatedContactsEmmaClubMembersSubAddress.Form.Refresh
0
 

Author Comment

by:NewAS
ID: 18931352
That sort of worked but I more often than not get a Run-time error '31' error Data Provider could not be initialized.

Any thoughts, NewAS.
0
 

Author Comment

by:NewAS
ID: 18932233
The code is having issue with:

Me.frmRelatedContactsEmmaClubMembersSubAddress.Form.Filter = "[Post_Code]='" & Me.[Post_Code] & "'"

Regards, NewAS.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 18932361
we might be encountering a timing issue here.
place this codes in a module

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'------------

Me.frmRelatedContactsEmmaClubMembersSubAddress.Form.Filter = "[Post_Code]='" & Me.[Post_Code] & "'"
Sleep 500
doevents

Me.frmRelatedContactsEmmaClubMembersSubAddress.Form.FilterOn=True
Me.frmRelatedContactsEmmaClubMembersSubAddress.Form.Refresh
0
 

Author Comment

by:NewAS
ID: 18956816
Genius, genius, you are my Hero, just kidding. Spot on, works a treat, thank you so much for your patience and knowledge.

Thank you, NewAS.
0
 

Author Comment

by:NewAS
ID: 18956832
Oops, spoke to soon.

I opened the form again, now its throwing up the same error. This is so weird.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question