showing a subform based on the main form

  • Thread starter Thread starter sg
  • Start date Start date
S

sg

I have a main form named frm Item PT. It has a subform that shows as a
datasheet. The subform is named Fail. On the main form, when a combo box
shows "Unnacceptable", I want to show the subform related to that record to
enter information about why it is unacceptable. The forms are linked
together using the PT ID field.

Right now I have the following code:

Me.[fail].Visible = (Me.[accept/not accept] = "Not acceptable")
in the after update event

Although it doesn't show the subform like I would like it to (as if I had
clicked on the plus sign to show the subform), it does open the subform so I
can enter a new record. The problem is when the form opens, it does not
inherit the main forms PT ID. When I enter data into the subform that opens,
it will not save the record.

Two questions here:

1. Is there a way to get it to really show the subform in the same window
as the main form (as if I clicked on the plus sign)

2. If I have to open it as a separate form, how do I get it to pull the PT
ID in so I can enter a new record?

Thanks in advance for any help!
 
Here is a link to the code needed on the after update of the combo box
http://allenbrowne.com/ser-03.html

before you put the line
Me.[fail].Visible = (Me.[accept/not accept] = "Not acceptable")

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Thanks for your quick reply, Jeanette.

I am still having issues...

Now my code in the after update property of the combo box is:

Private Sub status_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Status) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PT ID] = " & Me.Status
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
Me.[fail].Visible = (Me.[Status] = "Not acceptable")

End Sub

Note: I changed the field name of the combo box to Status - it seemed that
the former name may have been causing problems because I had a "/" in the
field name.

Anyway, when I change the status to "Unacceptable", I get the following error:

Run-time error '3070':
The Microsoft Office Access database engine does not recognize 'Acceptable'
as a valid field name or expression.

When I click on Debug, the following line of code is highlighted:

rs.FindFirst "[PT ID] = " & Me.Status

I saw on the link you sent me that if the field is text you have to write
this line differently, but it is an autonumber field so I thought I was ok.

The other thing that I'm wondering is whether my combo box is causing
problems. This was actually set up as a lookup field in the underlying table
so I'm not sure if that is causing a problem. We do need to store this data
in the table...do I need to create an actual combo box on the form?

I don't know what the error I am getting means - I would appreciate your
feedback!

Jeanette Cunningham said:
Here is a link to the code needed on the after update of the combo box
http://allenbrowne.com/ser-03.html

before you put the line
Me.[fail].Visible = (Me.[accept/not accept] = "Not acceptable")

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


sg said:
I have a main form named frm Item PT. It has a subform that shows as a
datasheet. The subform is named Fail. On the main form, when a combo box
shows "Unnacceptable", I want to show the subform related to that record
to
enter information about why it is unacceptable. The forms are linked
together using the PT ID field.

Right now I have the following code:

Me.[fail].Visible = (Me.[accept/not accept] = "Not acceptable")
in the after update event

Although it doesn't show the subform like I would like it to (as if I had
clicked on the plus sign to show the subform), it does open the subform so
I
can enter a new record. The problem is when the form opens, it does not
inherit the main forms PT ID. When I enter data into the subform that
opens,
it will not save the record.

Two questions here:

1. Is there a way to get it to really show the subform in the same window
as the main form (as if I clicked on the plus sign)

2. If I have to open it as a separate form, how do I get it to pull the
PT
ID in so I can enter a new record?

Thanks in advance for any help!
Click to expand...


.
Click to expand...
 
I should also tell you that after I get out of the debugger, the subform does
open as a separate form, but it is still not populating the PT ID field in
the subform so I am still unable to save the data.

sg said:
Thanks for your quick reply, Jeanette.

I am still having issues...

Now my code in the after update property of the combo box is:

Private Sub status_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Status) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PT ID] = " & Me.Status
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
Me.[fail].Visible = (Me.[Status] = "Not acceptable")

End Sub

Note: I changed the field name of the combo box to Status - it seemed that
the former name may have been causing problems because I had a "/" in the
field name.

Anyway, when I change the status to "Unacceptable", I get the following error:

Run-time error '3070':
The Microsoft Office Access database engine does not recognize 'Acceptable'
as a valid field name or expression.

When I click on Debug, the following line of code is highlighted:

rs.FindFirst "[PT ID] = " & Me.Status

I saw on the link you sent me that if the field is text you have to write
this line differently, but it is an autonumber field so I thought I was ok.

The other thing that I'm wondering is whether my combo box is causing
problems. This was actually set up as a lookup field in the underlying table
so I'm not sure if that is causing a problem. We do need to store this data
in the table...do I need to create an actual combo box on the form?

I don't know what the error I am getting means - I would appreciate your
feedback!

Jeanette Cunningham said:
Here is a link to the code needed on the after update of the combo box
http://allenbrowne.com/ser-03.html

before you put the line
Me.[fail].Visible = (Me.[accept/not accept] = "Not acceptable")

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


sg said:
I have a main form named frm Item PT. It has a subform that shows as a
datasheet. The subform is named Fail. On the main form, when a combo box
shows "Unnacceptable", I want to show the subform related to that record
to
enter information about why it is unacceptable. The forms are linked
together using the PT ID field.

Right now I have the following code:

Me.[fail].Visible = (Me.[accept/not accept] = "Not acceptable")
in the after update event

Although it doesn't show the subform like I would like it to (as if I had
clicked on the plus sign to show the subform), it does open the subform so
I
can enter a new record. The problem is when the form opens, it does not
inherit the main forms PT ID. When I enter data into the subform that
opens,
it will not save the record.

Two questions here:

1. Is there a way to get it to really show the subform in the same window
as the main form (as if I clicked on the plus sign)

2. If I have to open it as a separate form, how do I get it to pull the
PT
ID in so I can enter a new record?

Thanks in advance for any help!
Click to expand...


.
Click to expand...
Click to expand...
 
1. " I changed the field name of the combo box to Status - it seemed that
the former name may have been causing problems because I had a "/" in the
field name."

Field names are best with just letters of the alphabet from a - z and you
can use numbers from 0 - 9 in conjunction with letters of the alphabet.
Special characters such as / and full stop and comma and quotes and most
other special characters have particular meanings in the internal parts of
access and cause problems if used in names for fields, queries, forms etc.

2. Yes, the lookup field in the table will be causing the problem with the
combo.
Instead of a lookup field, create a table for status.
You can have 2 fields in the table, StatusID - autonumber and StatusName - a
text field.

Let's know how you get on.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


sg said:
Thanks for your quick reply, Jeanette.

I am still having issues...

Now my code in the after update property of the combo box is:

Private Sub status_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Status) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PT ID] = " & Me.Status
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
Me.[fail].Visible = (Me.[Status] = "Not acceptable")

End Sub

Note: I changed the field name of the combo box to Status - it seemed
that
the former name may have been causing problems because I had a "/" in the
field name.

Anyway, when I change the status to "Unacceptable", I get the following
error:

Run-time error '3070':
The Microsoft Office Access database engine does not recognize
'Acceptable'
as a valid field name or expression.

When I click on Debug, the following line of code is highlighted:

rs.FindFirst "[PT ID] = " & Me.Status

I saw on the link you sent me that if the field is text you have to write
this line differently, but it is an autonumber field so I thought I was
ok.

The other thing that I'm wondering is whether my combo box is causing
problems. This was actually set up as a lookup field in the underlying
table
so I'm not sure if that is causing a problem. We do need to store this
data
in the table...do I need to create an actual combo box on the form?

I don't know what the error I am getting means - I would appreciate your
feedback!

Jeanette Cunningham said:
Here is a link to the code needed on the after update of the combo box
http://allenbrowne.com/ser-03.html

before you put the line
Me.[fail].Visible = (Me.[accept/not accept] = "Not acceptable")

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


sg said:
I have a main form named frm Item PT. It has a subform that shows as a
datasheet. The subform is named Fail. On the main form, when a combo
box
shows "Unnacceptable", I want to show the subform related to that
record
to
enter information about why it is unacceptable. The forms are linked
together using the PT ID field.

Right now I have the following code:

Me.[fail].Visible = (Me.[accept/not accept] = "Not acceptable")
in the after update event

Although it doesn't show the subform like I would like it to (as if I
had
clicked on the plus sign to show the subform), it does open the subform
so
I
can enter a new record. The problem is when the form opens, it does
not
inherit the main forms PT ID. When I enter data into the subform that
opens,
it will not save the record.

Two questions here:

1. Is there a way to get it to really show the subform in the same
window
as the main form (as if I clicked on the plus sign)

2. If I have to open it as a separate form, how do I get it to pull
the
PT
ID in so I can enter a new record?

Thanks in advance for any help!
Click to expand...


.
Click to expand...
Click to expand...
 
Ok - I got the combo box put in and now I get the message, "Not Found,
filtered?" which I see is in the code, but I don't know why what I am doing
isn't working. This comes up no matter what I choose from the combo box.
any suggestions?

Jeanette Cunningham said:
1. " I changed the field name of the combo box to Status - it seemed that
the former name may have been causing problems because I had a "/" in the
field name."

Field names are best with just letters of the alphabet from a - z and you
can use numbers from 0 - 9 in conjunction with letters of the alphabet.
Special characters such as / and full stop and comma and quotes and most
other special characters have particular meanings in the internal parts of
access and cause problems if used in names for fields, queries, forms etc.

2. Yes, the lookup field in the table will be causing the problem with the
combo.
Instead of a lookup field, create a table for status.
You can have 2 fields in the table, StatusID - autonumber and StatusName - a
text field.

Let's know how you get on.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


sg said:
Thanks for your quick reply, Jeanette.

I am still having issues...

Now my code in the after update property of the combo box is:

Private Sub status_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Status) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PT ID] = " & Me.Status
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
Me.[fail].Visible = (Me.[Status] = "Not acceptable")

End Sub

Note: I changed the field name of the combo box to Status - it seemed
that
the former name may have been causing problems because I had a "/" in the
field name.

Anyway, when I change the status to "Unacceptable", I get the following
error:

Run-time error '3070':
The Microsoft Office Access database engine does not recognize
'Acceptable'
as a valid field name or expression.

When I click on Debug, the following line of code is highlighted:

rs.FindFirst "[PT ID] = " & Me.Status

I saw on the link you sent me that if the field is text you have to write
this line differently, but it is an autonumber field so I thought I was
ok.

The other thing that I'm wondering is whether my combo box is causing
problems. This was actually set up as a lookup field in the underlying
table
so I'm not sure if that is causing a problem. We do need to store this
data
in the table...do I need to create an actual combo box on the form?

I don't know what the error I am getting means - I would appreciate your
feedback!

Jeanette Cunningham said:
Here is a link to the code needed on the after update of the combo box
http://allenbrowne.com/ser-03.html

before you put the line
Me.[fail].Visible = (Me.[accept/not accept] = "Not acceptable")

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I have a main form named frm Item PT. It has a subform that shows as a
datasheet. The subform is named Fail. On the main form, when a combo
box
shows "Unnacceptable", I want to show the subform related to that
record
to
enter information about why it is unacceptable. The forms are linked
together using the PT ID field.

Right now I have the following code:

Me.[fail].Visible = (Me.[accept/not accept] = "Not acceptable")
in the after update event

Although it doesn't show the subform like I would like it to (as if I
had
clicked on the plus sign to show the subform), it does open the subform
so
I
can enter a new record. The problem is when the form opens, it does
not
inherit the main forms PT ID. When I enter data into the subform that
opens,
it will not save the record.

Two questions here:

1. Is there a way to get it to really show the subform in the same
window
as the main form (as if I clicked on the plus sign)

2. If I have to open it as a separate form, how do I get it to pull
the
PT
ID in so I can enter a new record?

Thanks in advance for any help!


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top