Embeded Forms

[Ray C]

I have a situation where I want to display information that is held in two
different Tables in one Form. The Main Form has a record source linked to the
Customer Table and my Embeded Sub Form has a Query as it's data source and
the query is linked to the Stock Table (and a number of others). Both these
tables are linked by a Contract number. If the equipment is in stock, the
contract number will be "0" if it is booked out to a customer, it will have a
Contract Number recorded in the Table and the Customer will have the same
contract number held in the Customer Table.
Here is what I am trying to achieve.
I want to input the serial number of a piece of equipment and search for
that in the Stock Table (via the Form that is embeded in my Main Form that is
based on a Query of the Stock Table). If the equipment is found and it is
linked to a Contract Number, I want to display the Customer Details in the
Main Form that is Based on the Customer Table.
The code is
Dim strForm As Form
With strForm.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
strForm.Bookmark = .Bookmark
util_Populate = True
Else
util_Populate = False
End If
End With
This works fine when the Embedded Form is based on the Stock Table but does
not work when I change my Embeded Form to be based on a Query. The routine
still finds the item and returns True but the Embeded Form does not display
the correct record. (as though the Bookark has been placed in the wrong place)
Any help appreciated RayC

 

[ruralguy via AccessMonster.com]

Hi Ray,
I would suggest changing your code to the following:

With Me.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.util_Populate = True
Else
Me.util_Populate = False
End If
End With

I doubt it will correct your issue but this syntax is more reliable and solid.
AFAIK code can not tell the difference between a table and a query in this
case. I'm not sure what is causing your problem right now.

 

[Ray C]

Hi, Thanks for the input. I don't know if it helps but the code I use to call
the routine seems to be misbehaving and I am not sure if that could be part
of the problem. I call the "util_Poplulate" with :-
"If Not util_Populate(forms.frm_Sub2_Stock, FieldName, FieldInfo, 2) Then"
Where "Forms.frm_Sub2_Stock" is the name of the form. Your solution would
not workl as I would not be able to pass the Form Name to the routine.
"FildName" Is The Table Collumn Name, "FieldInfo" is the seral number to be
Found and 2 indicates that the information the is to make up the Criterion
String is a String and not Numeric.
The part that is playing up is that you will note that the Forms.frm_sub etc
acually is listed with a lower case "f" If I yype in "Forms.frm_sub etc, the
program changeds the Upper Case "F" to a Lower case "f" If you understand
what I am saying?

RayC

 

[ruralguy via AccessMonster.com]

It was not obvious to me from your post that this was a function in probably
a standard module rather than the class module of a form. Is frm_Sub2_Stock a
SubForm on another form? If so then its name will not be in the Forms
collection.

Hi, Thanks for the input. I don't know if it helps but the code I use to call
the routine seems to be misbehaving and I am not sure if that could be part
of the problem. I call the "util_Poplulate" with :-
"If Not util_Populate(forms.frm_Sub2_Stock, FieldName, FieldInfo, 2) Then"
Where "Forms.frm_Sub2_Stock" is the name of the form. Your solution would
not workl as I would not be able to pass the Form Name to the routine.
"FildName" Is The Table Collumn Name, "FieldInfo" is the seral number to be
Found and 2 indicates that the information the is to make up the Criterion
String is a String and not Numeric.
The part that is playing up is that you will note that the Forms.frm_sub etc
acually is listed with a lower case "f" If I yype in "Forms.frm_sub etc, the
program changeds the Upper Case "F" to a Lower case "f" If you understand
what I am saying?

RayC

Hi Ray,
I would suggest changing your code to the following:

[quoted text clipped - 43 lines]

 

[Ray C]

Hi RG
Sory, I don't quite understand your rply but yes. The Form "frm.Main" is my
main form that is "Bound" to the Cutomer Table and "frm_Sub2_Stock" Is a form
that is Embeded in that Main Form and is Bound to a Quert that takes it's
infoprmation mainly from the Stock Table but from a few others tables as
well. I thought that was how these things worked, is there anything I can do
to correct the problem?

RayC

It was not obvious to me from your post that this was a function in probably
a standard module rather than the class module of a form. Is frm_Sub2_Stock a
SubForm on another form? If so then its name will not be in the Forms
collection.

Hi, Thanks for the input. I don't know if it helps but the code I use to call
the routine seems to be misbehaving and I am not sure if that could be part
of the problem. I call the "util_Poplulate" with :-
"If Not util_Populate(forms.frm_Sub2_Stock, FieldName, FieldInfo, 2) Then"
Where "Forms.frm_Sub2_Stock" is the name of the form. Your solution would
not workl as I would not be able to pass the Form Name to the routine.
"FildName" Is The Table Collumn Name, "FieldInfo" is the seral number to be
Found and 2 indicates that the information the is to make up the Criterion
String is a String and not Numeric.
The part that is playing up is that you will note that the Forms.frm_sub etc
acually is listed with a lower case "f" If I yype in "Forms.frm_sub etc, the
program changeds the Upper Case "F" to a Lower case "f" If you understand
what I am saying?

RayC

Hi Ray,
I would suggest changing your code to the following:

[quoted text clipped - 43 lines]

the correct record. (as though the Bookark has been placed in the wrong place)
Any help appreciated RayC

 

[ruralguy via AccessMonster.com]

Hi Ray,
The point I was trying to make is that your 1st parameter (the form object)
for your function is Forms.frm_Sub2_Stock but there is *no* object in the
Forms collection named frm_Sub2_Stock because this is a SubForm and only the
MainForm is in the collection. If you are going to use this technique and
this function then you need to reference the frm_Sub2_Stock through the
MainForm.
Forms.frm_Main.frm_Sub2_Stock.Form

How about posting the entire function from Function to End Function so we can
see it. Is this function in a form or a stand alone module?

Hi RG
Sory, I don't quite understand your rply but yes. The Form "frm.Main" is my
main form that is "Bound" to the Cutomer Table and "frm_Sub2_Stock" Is a form
that is Embeded in that Main Form and is Bound to a Quert that takes it's
infoprmation mainly from the Stock Table but from a few others tables as
well. I thought that was how these things worked, is there anything I can do
to correct the problem?

RayC

It was not obvious to me from your post that this was a function in probably
a standard module rather than the class module of a form. Is frm_Sub2_Stock a

[quoted text clipped - 22 lines]

 

[Ray C]

Hi RG, thanks for your perseverance. The Form is Listed in the Forms
Collection. I have anothe Form that is in the Forms Collection, I use that in
a similar way and it works Fine.
Private Sub After_FindSerialMCHNo(FieldName As String, FieldInfo As Variant)
Dim reply As Integer
Debug.Print FieldName & " = " & FieldInfo
*****
If Not util_Populate(forms.frm_Sub2_Stock, FieldName, FieldInfo, 2) Then
*****
reply = MsgBox("This Stock Item does not Exist in the Table!" &
Chr(13) _
& Chr(13) & "do you want to generate a new record?",
vbYesNo)
If reply = vbNo Then Call FormStart ' Start a new record
search
If reply = vbYes Then Call NewStockItem ' Enter a New Stock
Item

Else 'Info Found so display it
Info_Tab.Visible = True: Info_Tab.SetFocus ' Display the
found Record

End If
End Sub

Info_Tab is the Tab that contains the offending Sub Form. The Field Name and
Field Info ar Pssed to the routine and are used to "LookUp" the record. The
Line the I have marked with ******* is the line that screws upo with a
warning "Run-Time Error '438' Object does not support this property or
Method."

The Utility Function is called util_Populate and seems to work well when
called from other parts of the program.

Public Function util_Populate(strForm As Form, strField As String, varInfo
As Variant, _
intType As Integer) As Boolean

'******************************************************
' Name: Util_Populate :- Utility Number
' Purpose: Finds a Record and populates the Form with the required Info
'
' Returns: True or False (Boolean)
'
' Author:
' Date: October 1st 2006
' Comment:
' This Function locates and syncronises records in a table. It needs the
following parameters
' passed to it:-
' strForm = The name of the Form to be referenced.
' strField = the Name of the field to be searched.
' varInfo = the information to be found.
' intType = The type of information contained in Varinfo to enable
Criterion to be set up
' 1 = Numeric Data
' 2 = String Data
' The function returns a Boolean value of True if record found, False if
Not Found.
'*****************************=***************************

If intType = 1 Then strCriteria = BuildCriteria(strField, dbInteger,
varInfo)
'If intType = 2 Then strCriteria = BuildCriteria(strField, dbText, "'" &
varInfo & "'")
If intType = 2 Then strCriteria = BuildCriteria(strField, dbText, " " &
varInfo)

With strForm.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
strForm.Bookmark = .Bookmark
util_Populate = True
Else
util_Populate = False
End If
End With
Debug.Print util_Populate

End Function

Hope that this helps
RayC

Hi Ray,
The point I was trying to make is that your 1st parameter (the form object)
for your function is Forms.frm_Sub2_Stock but there is *no* object in the
Forms collection named frm_Sub2_Stock because this is a SubForm and only the
MainForm is in the collection. If you are going to use this technique and
this function then you need to reference the frm_Sub2_Stock through the
MainForm.
Forms.frm_Main.frm_Sub2_Stock.Form

How about posting the entire function from Function to End Function so we can
see it. Is this function in a form or a stand alone module?

Hi RG
Sory, I don't quite understand your rply but yes. The Form "frm.Main" is my
main form that is "Bound" to the Cutomer Table and "frm_Sub2_Stock" Is a form
that is Embeded in that Main Form and is Bound to a Quert that takes it's
infoprmation mainly from the Stock Table but from a few others tables as
well. I thought that was how these things worked, is there anything I can do
to correct the problem?

RayC

It was not obvious to me from your post that this was a function in probably
a standard module rather than the class module of a form. Is frm_Sub2_Stock a

[quoted text clipped - 22 lines]

the correct record. (as though the Bookark has been placed in the wrong place)
Any help appreciated RayC

 

[ruralguy via AccessMonster.com]

From VBA Help:
The Forms collection contains all of the currently open forms in a Microsoft
Access database.

A SubForm is actually a control on another form and as such is not listed as
an open form in the Forms Collection. Are you sure your frm_Sub2_Stock form
is in the Forms Collection?

Hi RG, thanks for your perseverance. The Form is Listed in the Forms
Collection. I have anothe Form that is in the Forms Collection, I use that in
a similar way and it works Fine.
Private Sub After_FindSerialMCHNo(FieldName As String, FieldInfo As Variant)
Dim reply As Integer
Debug.Print FieldName & " = " & FieldInfo
*****
If Not util_Populate(forms.frm_Sub2_Stock, FieldName, FieldInfo, 2) Then
*****
reply = MsgBox("This Stock Item does not Exist in the Table!" &
Chr(13) _
& Chr(13) & "do you want to generate a new record?",
vbYesNo)
If reply = vbNo Then Call FormStart ' Start a new record
search
If reply = vbYes Then Call NewStockItem ' Enter a New Stock
Item

Else 'Info Found so display it
Info_Tab.Visible = True: Info_Tab.SetFocus ' Display the
found Record

End If
End Sub

Info_Tab is the Tab that contains the offending Sub Form. The Field Name and
Field Info ar Pssed to the routine and are used to "LookUp" the record. The
Line the I have marked with ******* is the line that screws upo with a
warning "Run-Time Error '438' Object does not support this property or
Method."

The Utility Function is called util_Populate and seems to work well when
called from other parts of the program.

Public Function util_Populate(strForm As Form, strField As String, varInfo
As Variant, _
intType As Integer) As Boolean

'******************************************************
' Name: Util_Populate :- Utility Number
' Purpose: Finds a Record and populates the Form with the required Info
'
' Returns: True or False (Boolean)
'
' Author:
' Date: October 1st 2006
' Comment:
' This Function locates and syncronises records in a table. It needs the
following parameters
' passed to it:-
' strForm = The name of the Form to be referenced.
' strField = the Name of the field to be searched.
' varInfo = the information to be found.
' intType = The type of information contained in Varinfo to enable
Criterion to be set up
' 1 = Numeric Data
' 2 = String Data
' The function returns a Boolean value of True if record found, False if
Not Found.
'*****************************=***************************

If intType = 1 Then strCriteria = BuildCriteria(strField, dbInteger,
varInfo)
'If intType = 2 Then strCriteria = BuildCriteria(strField, dbText, "'" &
varInfo & "'")
If intType = 2 Then strCriteria = BuildCriteria(strField, dbText, " " &
varInfo)

With strForm.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
strForm.Bookmark = .Bookmark
util_Populate = True
Else
util_Populate = False
End If
End With
Debug.Print util_Populate

End Function

Hope that this helps
RayC

Hi Ray,
The point I was trying to make is that your 1st parameter (the form object)

[quoted text clipped - 23 lines]

 

[Ray C]

Hi RG
I am not realy sure about anything at the moment. I have a button on my tool
bar which opens a "Database Window", this window lists "Tables", "Queries",
"Forms" and Reports. In the Forms area is listed all the Forms that I have in
my project, my "frm_Main", "mnu_Menu" etc. also listed in there is the form I
am referring to - "frm_Sub2_Stock". This Form is based on a query of the
Stock Table. Within the Tools, I have an option to insert a Text Box, Combo
Box etc and one of the optioms is to insert a SubForm / SubReport. If I click
on this I get a dialogue box asking if I want to use an existing Table /
Query or use an exiting Form. Out of the selection of Forms that the Dialogue
shows, I select the Form "frm_Sub2_Stock" and I am asked to name the subform.
Does this help?
Regards RayC

From VBA Help:
The Forms collection contains all of the currently open forms in a Microsoft
Access database.

A SubForm is actually a control on another form and as such is not listed as
an open form in the Forms Collection. Are you sure your frm_Sub2_Stock form
is in the Forms Collection?

Hi RG, thanks for your perseverance. The Form is Listed in the Forms
Collection. I have anothe Form that is in the Forms Collection, I use that in
a similar way and it works Fine.
Private Sub After_FindSerialMCHNo(FieldName As String, FieldInfo As Variant)
Dim reply As Integer
Debug.Print FieldName & " = " & FieldInfo
*****
If Not util_Populate(forms.frm_Sub2_Stock, FieldName, FieldInfo, 2) Then
*****
reply = MsgBox("This Stock Item does not Exist in the Table!" &
Chr(13) _
& Chr(13) & "do you want to generate a new record?",
vbYesNo)
If reply = vbNo Then Call FormStart ' Start a new record
search
If reply = vbYes Then Call NewStockItem ' Enter a New Stock
Item

Else 'Info Found so display it
Info_Tab.Visible = True: Info_Tab.SetFocus ' Display the
found Record

End If
End Sub

Info_Tab is the Tab that contains the offending Sub Form. The Field Name and
Field Info ar Pssed to the routine and are used to "LookUp" the record. The
Line the I have marked with ******* is the line that screws upo with a
warning "Run-Time Error '438' Object does not support this property or
Method."

The Utility Function is called util_Populate and seems to work well when
called from other parts of the program.

Public Function util_Populate(strForm As Form, strField As String, varInfo
As Variant, _
intType As Integer) As Boolean

'******************************************************
' Name: Util_Populate :- Utility Number
' Purpose: Finds a Record and populates the Form with the required Info
'
' Returns: True or False (Boolean)
'
' Author:
' Date: October 1st 2006
' Comment:
' This Function locates and syncronises records in a table. It needs the
following parameters
' passed to it:-
' strForm = The name of the Form to be referenced.
' strField = the Name of the field to be searched.
' varInfo = the information to be found.
' intType = The type of information contained in Varinfo to enable
Criterion to be set up
' 1 = Numeric Data
' 2 = String Data
' The function returns a Boolean value of True if record found, False if
Not Found.
'*****************************=***************************

If intType = 1 Then strCriteria = BuildCriteria(strField, dbInteger,
varInfo)
'If intType = 2 Then strCriteria = BuildCriteria(strField, dbText, "'" &
varInfo & "'")
If intType = 2 Then strCriteria = BuildCriteria(strField, dbText, " " &
varInfo)

With strForm.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
strForm.Bookmark = .Bookmark
util_Populate = True
Else
util_Populate = False
End If
End With
Debug.Print util_Populate

End Function

Hope that this helps
RayC

Hi Ray,
The point I was trying to make is that your 1st parameter (the form object)

[quoted text clipped - 23 lines]

the correct record. (as though the Bookark has been placed in the wrong place)
Any help appreciated RayC

 

[Ray C]

OK, I think I understand now. If I Open the Form "frm_Sub2_Stock" it appears
on my desk top (which I don't want) and the routne runs without a hitch. The
opened form displays the record that I have asked for but the embeded form
does not, it displays the first record in the Table or query. I thought that
the Embeded form was a "reflection" of the Form that it was based on but that
would appear not to be the case.
How would I go about displaying the information from the "Stock Table" in a
Form that is based on the "Customer Table" when I hve no linlkng fields to
put in the "Parent / Child" boxes in the Embedded Form?
RayC

Hi RG
I am not realy sure about anything at the moment. I have a button on my tool
bar which opens a "Database Window", this window lists "Tables", "Queries",
"Forms" and Reports. In the Forms area is listed all the Forms that I have in
my project, my "frm_Main", "mnu_Menu" etc. also listed in there is the form I
am referring to - "frm_Sub2_Stock". This Form is based on a query of the
Stock Table. Within the Tools, I have an option to insert a Text Box, Combo
Box etc and one of the optioms is to insert a SubForm / SubReport. If I click
on this I get a dialogue box asking if I want to use an existing Table /
Query or use an exiting Form. Out of the selection of Forms that the Dialogue
shows, I select the Form "frm_Sub2_Stock" and I am asked to name the subform.
Does this help?
Regards RayC

From VBA Help:
The Forms collection contains all of the currently open forms in a Microsoft
Access database.

A SubForm is actually a control on another form and as such is not listed as
an open form in the Forms Collection. Are you sure your frm_Sub2_Stock form
is in the Forms Collection?

Hi RG, thanks for your perseverance. The Form is Listed in the Forms
Collection. I have anothe Form that is in the Forms Collection, I use that in
a similar way and it works Fine.
Private Sub After_FindSerialMCHNo(FieldName As String, FieldInfo As Variant)
Dim reply As Integer
Debug.Print FieldName & " = " & FieldInfo
*****
If Not util_Populate(forms.frm_Sub2_Stock, FieldName, FieldInfo, 2) Then
*****
reply = MsgBox("This Stock Item does not Exist in the Table!" &
Chr(13) _
& Chr(13) & "do you want to generate a new record?",
vbYesNo)
If reply = vbNo Then Call FormStart ' Start a new record
search
If reply = vbYes Then Call NewStockItem ' Enter a New Stock
Item

Else 'Info Found so display it
Info_Tab.Visible = True: Info_Tab.SetFocus ' Display the
found Record

End If
End Sub

Info_Tab is the Tab that contains the offending Sub Form. The Field Name and
Field Info ar Pssed to the routine and are used to "LookUp" the record. The
Line the I have marked with ******* is the line that screws upo with a
warning "Run-Time Error '438' Object does not support this property or
Method."

The Utility Function is called util_Populate and seems to work well when
called from other parts of the program.

Public Function util_Populate(strForm As Form, strField As String, varInfo
As Variant, _
intType As Integer) As Boolean

'******************************************************
' Name: Util_Populate :- Utility Number
' Purpose: Finds a Record and populates the Form with the required Info
'
' Returns: True or False (Boolean)
'
' Author:
' Date: October 1st 2006
' Comment:
' This Function locates and syncronises records in a table. It needs the
following parameters
' passed to it:-
' strForm = The name of the Form to be referenced.
' strField = the Name of the field to be searched.
' varInfo = the information to be found.
' intType = The type of information contained in Varinfo to enable
Criterion to be set up
' 1 = Numeric Data
' 2 = String Data
' The function returns a Boolean value of True if record found, False if
Not Found.
'*****************************=***************************

If intType = 1 Then strCriteria = BuildCriteria(strField, dbInteger,
varInfo)
'If intType = 2 Then strCriteria = BuildCriteria(strField, dbText, "'" &
varInfo & "'")
If intType = 2 Then strCriteria = BuildCriteria(strField, dbText, " " &
varInfo)

With strForm.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
strForm.Bookmark = .Bookmark
util_Populate = True
Else
util_Populate = False
End If
End With
Debug.Print util_Populate

End Function

Hope that this helps
RayC

Hi Ray,
The point I was trying to make is that your 1st parameter (the form object)
[quoted text clipped - 23 lines]
the correct record. (as though the Bookark has been placed in the wrong place)
Any help appreciated RayC

 

[ruralguy via AccessMonster.com]

I'll be honest, I do not understand what you just asked. Here's a link that
explains how to reference a SubForm and its controls.
http://www.mvps.org/access/forms/frm0031.htm

OK, I think I understand now. If I Open the Form "frm_Sub2_Stock" it appears
on my desk top (which I don't want) and the routne runs without a hitch. The
opened form displays the record that I have asked for but the embeded form
does not, it displays the first record in the Table or query. I thought that
the Embeded form was a "reflection" of the Form that it was based on but that
would appear not to be the case.
How would I go about displaying the information from the "Stock Table" in a
Form that is based on the "Customer Table" when I hve no linlkng fields to
put in the "Parent / Child" boxes in the Embedded Form?
RayC

Hi RG
I am not realy sure about anything at the moment. I have a button on my tool

[quoted text clipped - 107 lines]

 

[Ray C]

Many thanks for yout kind help.
Regards RayC

I'll be honest, I do not understand what you just asked. Here's a link that
explains how to reference a SubForm and its controls.
http://www.mvps.org/access/forms/frm0031.htm

OK, I think I understand now. If I Open the Form "frm_Sub2_Stock" it appears
on my desk top (which I don't want) and the routne runs without a hitch. The
opened form displays the record that I have asked for but the embeded form
does not, it displays the first record in the Table or query. I thought that
the Embeded form was a "reflection" of the Form that it was based on but that
would appear not to be the case.
How would I go about displaying the information from the "Stock Table" in a
Form that is based on the "Customer Table" when I hve no linlkng fields to
put in the "Parent / Child" boxes in the Embedded Form?
RayC

Hi RG
I am not realy sure about anything at the moment. I have a button on my tool

[quoted text clipped - 107 lines]

the correct record. (as though the Bookark has been placed in the wrong place)
Any help appreciated RayC