Solved

Unbound form with unbound subform.

Posted on 2007-04-11
33
590 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
  • 17
  • 15
33 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

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

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

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now