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


"sg" <sg@discussions.microsoft.com> wrote in message 
news:F94A8390-F80B-4552-B398-9A2891E140CB@microsoft.com...
>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! 

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" wrote:

> 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" <sg@discussions.microsoft.com> wrote in message 
> news:F94A8390-F80B-4552-B398-9A2891E140CB@microsoft.com...
> >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! 
> 
> 
> .
> 

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" wrote:

> 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" wrote:
> 
> > 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" <sg@discussions.microsoft.com> wrote in message 
> > news:F94A8390-F80B-4552-B398-9A2891E140CB@microsoft.com...
> > >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! 
> > 
> > 
> > .
> > 

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" <sg@discussions.microsoft.com> wrote in message 
news:5D1695F6-DB9C-4D5D-85B8-D41F8548AC1D@microsoft.com...
> 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" wrote:
>
>> 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" <sg@discussions.microsoft.com> wrote in message
>> news:F94A8390-F80B-4552-B398-9A2891E140CB@microsoft.com...
>> >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!
>>
>>
>> .
>> 

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" wrote:

> 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" <sg@discussions.microsoft.com> wrote in message 
> news:5D1695F6-DB9C-4D5D-85B8-D41F8548AC1D@microsoft.com...
> > 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" wrote:
> >
> >> 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" <sg@discussions.microsoft.com> wrote in message
> >> news:F94A8390-F80B-4552-B398-9A2891E140CB@microsoft.com...
> >> >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!
> >>
> >>
> >> .
> >> 
> 
> 
> .
>