Unbound form with unbound subform.

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.
NewASAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
rockiroadsCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rey Obrero (Capricorn1)Commented:
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
 
NewASAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
NewASAuthor Commented:
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
 
NewASAuthor Commented:
Could someone please advise as to how to get the rst2 recordset to refresh when using the main form record navigation buttons.
0
 
Rey Obrero (Capricorn1)Commented:
did you set the links master/child fields ?
0
 
NewASAuthor Commented:
I didnt think you could use master/child fields with unbound forms, am i wrong?
0
 
Rey Obrero (Capricorn1)Commented:
hmm??
can you zip and upload your db here
http://www.ee-stuff.com
remove sensitive data and workgroup if there are any.
0
 
NewASAuthor Commented:
I would prefer not to, could you please advise around this?
0
 
Rey Obrero (Capricorn1)Commented:
try in the main form current event

Private Sub Form_Current()
    Me.frmRelatedContactsAilsaForumsSubAddress.requery
   ' Me.frmRelatedContactsAilsaForumsSubAddress.Form.requery
End Sub
0
 
Rey Obrero (Capricorn1)Commented:
you can try this too

Set Me.frmRelatedContactsAilsaForumsSubAddress.Form.Recordset = rst2
    Me.frmRelatedContactsAilsaForumsSubAddress.LinkChildFields = "Post_Code"
    Me.frmRelatedContactsAilsaForumsSubAddress.LinkMasterFields = "Post_Code"
0
 
NewASAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
NewASAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
NewAS,
take a look at my last post http:#a18897741 
0
 
NewASAuthor Commented:
I changed the name of the form frmRelatedContactsAilsaForumsSubAddress to frmRelatedContactsEmmaClubMembersSubAddress, sorry for the confusion.
0
 
Rey Obrero (Capricorn1)Commented:
what about the other questions?
0
 
NewASAuthor Commented:
The source object is frmRelatedContactsEmmaClubMembersSubAddress.
0
 
NewASAuthor Commented:
We seemed to cross over a little with our messages, have I answered your questions properly?
0
 
Rey Obrero (Capricorn1)Commented:
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
 
NewASAuthor Commented:
Yes it is 1.
0
 
Rey Obrero (Capricorn1)Commented:
so, don't expect it to move to the next record, coz there is no next record.
0
 
NewASAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
NewASAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
NewASAuthor Commented:
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
 
NewASAuthor Commented:
The code is having issue with:

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

Regards, NewAS.
0
 
NewASAuthor Commented:
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
 
NewASAuthor Commented:
Oops, spoke to soon.

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

From novice to tech pro — start learning today.