Form with two nested subforms.

[Tyler]

I am setting up a database with three main forms. The parent form is called
[Orders]. The first subform is called [People]. the Second subform is called
[records]. Each order can have multiple people in it. Each person can have
multiple records. To keep all of the data on one page, I nested two
different subforms. The problem is that I am having problems running simple
functions on my second subform [records]. The first problem is using the
findrecord operation to find a value (MaxID) in the ID field. Here is what I
have:

Forms![Orders]![People].Form![Records].SetFocus
Forms![Orders]![People].Form![Records].Form![ID].SetFocus
DoCmd.FindRecord MaxID

The first two lines work (the ID field ends up having the focus), but the
last line gives me a runtime error. If I run this code slightly modified
with only one subform, it works fine.

The second problem is with an on_open event. When the entire form is loaded
I need to filter a field in the second nested form called [hide]. If [hide]
is true, I want that record filtered. I don't want to do it in the query,
because I want the user to have the oportunity to see all of the records.
Here is what I have:

Forms![Orders]![People].Form![Records].Filter = "[hide] = 0"
Forms![Orders]![People].Form![Records].FilterOn = True

If anyone knows what I am doing wrong I would appreciate it. Thanks for
your help.

 

[Marshall Barton]

I am setting up a database with three main forms. The parent form is called
[Orders]. The first subform is called [People]. the Second subform is called
[records]. Each order can have multiple people in it. Each person can have
multiple records. To keep all of the data on one page, I nested two
different subforms. The problem is that I am having problems running simple
functions on my second subform [records]. The first problem is using the
findrecord operation to find a value (MaxID) in the ID field. Here is what I
have:

Forms![Orders]![People].Form![Records].SetFocus
Forms![Orders]![People].Form![Records].Form![ID].SetFocus
DoCmd.FindRecord MaxID

The first two lines work (the ID field ends up having the focus), but the
last line gives me a runtime error. If I run this code slightly modified
with only one subform, it works fine.

The second problem is with an on_open event. When the entire form is loaded
I need to filter a field in the second nested form called [hide]. If [hide]
is true, I want that record filtered. I don't want to do it in the query,
because I want the user to have the oportunity to see all of the records.
Here is what I have:

Forms![Orders]![People].Form![Records].Filter = "[hide] = 0"
Forms![Orders]![People].Form![Records].FilterOn = True

If anyone knows what I am doing wrong I would appreciate it. Thanks for
your help.

I suspecty that MaxID is not the name of a field, control or
variable in the records subform. Perhaps you forgot to
change it when you copied from the other form or maybe you
haven't calculated it yet??

The filter code should be:

Forms![Orders]![People].Form![Records].FORM.Filter = "[hide]
= 0"
Forms![Orders]![People].Form![Records].FORM.FilterOn = True

 

[Tyler]

For the filter item. Including the .FORM. portion seems to do the trick.
However, when I orginally had it in the subform On_Open event, it kept
crashing my system. I moved it to the master form and it works like a charm.

For the MaxID field, when the VBA code crashes, I hover my mouse over the
MAXID text it flashes up the number that I needed it to look for, so I don't
think it is a problem with that field.

The other thing I thought might be a problem is that as part of the code I
insert a new record into the second subform and then immediately after that I
want to make that new record the current one in the second subform to allow
input (so the user doesn't need to look for the new record). I thought that
it might be that the record wasn't actually written to the database yet, but
when I take this code from a form that has two nested subforms to a form that
just has one (I eliminate the Orders portion of the form) it works fine.
That is why I think that there is something wrong with how I am referencing
the second subform.

Thanks again for your help. The one that you already helped me on was the
big problem, hopefully you have some insight on the other.

I am setting up a database with three main forms. The parent form is called
[Orders]. The first subform is called [People]. the Second subform is called
[records]. Each order can have multiple people in it. Each person can have
multiple records. To keep all of the data on one page, I nested two
different subforms. The problem is that I am having problems running simple
functions on my second subform [records]. The first problem is using the
findrecord operation to find a value (MaxID) in the ID field. Here is what I
have:

Forms![Orders]![People].Form![Records].SetFocus
Forms![Orders]![People].Form![Records].Form![ID].SetFocus
DoCmd.FindRecord MaxID

The first two lines work (the ID field ends up having the focus), but the
last line gives me a runtime error. If I run this code slightly modified
with only one subform, it works fine.

The second problem is with an on_open event. When the entire form is loaded
I need to filter a field in the second nested form called [hide]. If [hide]
is true, I want that record filtered. I don't want to do it in the query,
because I want the user to have the oportunity to see all of the records.
Here is what I have:

Forms![Orders]![People].Form![Records].Filter = "[hide] = 0"
Forms![Orders]![People].Form![Records].FilterOn = True

If anyone knows what I am doing wrong I would appreciate it. Thanks for
your help.

I suspecty that MaxID is not the name of a field, control or
variable in the records subform. Perhaps you forgot to
change it when you copied from the other form or maybe you
haven't calculated it yet??

The filter code should be:

Forms![Orders]![People].Form![Records].FORM.Filter = "[hide]
= 0"
Forms![Orders]![People].Form![Records].FORM.FilterOn = True

 

[Marshall Barton]

The other thing I thought might be a problem is that as part of the code I
insert a new record into the second subform and then immediately after that I
want to make that new record the current one in the second subform to allow
input (so the user doesn't need to look for the new record). I thought that
it might be that the record wasn't actually written to the database yet, but
when I take this code from a form that has two nested subforms to a form that
just has one (I eliminate the Orders portion of the form) it works fine.
That is why I think that there is something wrong with how I am referencing
the second subform.

I have no way of knowing what might be wrong until you post
whatever code you are using along with a brief explanation
of which form has the code and which form is being
manipulated.

The fact that it works as a main form but not as a subform
might be the same kind of problem as before or it might be
something else. It's not even clear why you are inserting a
new record in a table instead of just navigating to a new
record.

 

[Tyler]

The button is in the first subform (the 'People' subform). It adds a new
record into the second subform and fills out the linked field to match the
first subform. In the end, the only reason I don't use a acnewrec command is
so I can clean up my form. I think it looks cleaner without having the blank
records in the second subform. It is a preference thing, but I realize that
I am using a Rube Goldberg task to do something simple (although, I am not
sure how to go to a new record in a second subform). I have a feeling that
these are all linked. My problem is I am not sure if I need to supplement my
docmd's if they need to operate/manipulate data in a subform.

Private Sub cmdAddRecord_Click()

Dim SQLStmt As String
Dim Maxid As Long

' This inserts te new record in the record form
SQLStmt = "INSERT INTO [tbl-Data-Records] ( [People ID]) SELECT " &
Me![People ID]
DoCmd.RunSQL SQLStmt

Me.Refresh

' This finds the ID value that was assigned to the new record by the
autonumber property
Maxid = DMax("[Record ID]", "[tbl-Data-Records]")

Forms![Orders]![People].Form![Records].Requery

' Makes the form I want to input into the current form
Forms![Orders]![People].Form![Records].SetFocus

' Makes the field I want to search through the current form
Forms![Orders]![People].Form![Records].Form![Record ID].SetFocus

' Find the record.
DoCmd.FindRecord MaxID

End Sub

 

[Marshall Barton]

The button is in the first subform (the 'People' subform). It adds a new
record into the second subform and fills out the linked field to match the
first subform. In the end, the only reason I don't use a acnewrec command is
so I can clean up my form. I think it looks cleaner without having the blank
records in the second subform. It is a preference thing, but I realize that
I am using a Rube Goldberg task to do something simple (although, I am not
sure how to go to a new record in a second subform). I have a feeling that
these are all linked. My problem is I am not sure if I need to supplement my
docmd's if they need to operate/manipulate data in a subform.

Private Sub cmdAddRecord_Click()

Dim SQLStmt As String
Dim Maxid As Long

' This inserts te new record in the record form
SQLStmt = "INSERT INTO [tbl-Data-Records] ( [People ID]) SELECT " &
Me![People ID]
DoCmd.RunSQL SQLStmt

Me.Refresh

' This finds the ID value that was assigned to the new record by the
autonumber property
Maxid = DMax("[Record ID]", "[tbl-Data-Records]")

Forms![Orders]![People].Form![Records].Requery

' Makes the form I want to input into the current form
Forms![Orders]![People].Form![Records].SetFocus

' Makes the field I want to search through the current form
Forms![Orders]![People].Form![Records].Form![Record ID].SetFocus

' Find the record.
DoCmd.FindRecord MaxID

End Sub

Where is this code? It kind of looks like it's in the
people subform and you want to add a record to the records
subsubform. If I have that right, I think you are
refreshing the wrong form. Shouldn't it be

Forms![Orders]![People].Form![Records].Form.Refresh

But I believe the later Requery would take care of it, so
that is probably irrelevant.

I may have a blind spot, but the only thing that I can see
after staring at it all day is that the newly added record's
autonumber is not guaranteed to be the greatest value, so
it's conceivable that DMax is not finding the new record,
but that is so unlikely as to be another irrelevant shot in
the dark.

Since I abhor Docmd, I would not approach things the way you
have and I can't really say if there is something missing.
I guess the proof is in what happens, does the code do what
you want or is something going off the rails?

 

[Tyler]

Yes, you are correct, the button is in the people subform. When the code
crashes and it takes me to the debugger and I hove the mouse over the "MaxID"
text, the control tip that pops up gives me the value that I am looking for,
it just seems that I have coded something wrong with the docmd. Most of the
problems I run into, is that I I don't reference subforms correctly. So I am
leaning towards the problem being that I need to do something else to allow
the docmd to run in the second subform. For example, if I were to simplify
my code by taking out the "Insert Into" statement, and do it with the
acNewRec code I would think the following code would work, but it doesn't.

Forms![Orders]![People].Form![Records].SetFocus
DoCmd.GoToRecord , , acNewRec

This just adds a new record in the [People] form, not the [Records] form.
This is about as minimalized of code as I can get so I know that the error is
in the DoCmd code, but I'm not sure how to fix it. I appreciate the time you
are spending trying to help me out, any help you can give me I appreciate it.

 

[Marshall Barton]

Yes, you are correct, the button is in the people subform. When the code
crashes and it takes me to the debugger and I hove the mouse over the "MaxID"
text, the control tip that pops up gives me the value that I am looking for,
it just seems that I have coded something wrong with the docmd. Most of the
problems I run into, is that I I don't reference subforms correctly. So I am
leaning towards the problem being that I need to do something else to allow
the docmd to run in the second subform. For example, if I were to simplify
my code by taking out the "Insert Into" statement, and do it with the
acNewRec code I would think the following code would work, but it doesn't.

Forms![Orders]![People].Form![Records].SetFocus
DoCmd.GoToRecord , , acNewRec

This just adds a new record in the [People] form, not the [Records] form.
This is about as minimalized of code as I can get so I know that the error is
in the DoCmd code, but I'm not sure how to fix it. I appreciate the time you
are spending trying to help me out, any help you can give me I appreciate it.

That's why I avoid DoCmd if there is any other way. Many
DoCmd methods just don't have have a way for you to specify
which object (form/subform) you are trying to manipulate.

The way I usually approach this kind of thing is along the
lines of this air code, but I don't know what might happen
when AllowAdditions is set to No.

Private Sub cmdAddRecord_Click()
Dim SQLStmt As String
Dim Maxid As Long

' Save any changes to current people record
If Me.Dirty Then Me.Dirty = False

' This inserts a new record in the record form
With Me.Records.Form.RecordsetClone
.AddNew
![People ID] = Me.[People ID]
Maxid = ![Record ID] 'I doubt you need this
.Update
Me.Records.Form.Bookmark = .LastModified
End With

' Makes the form I want to input into the current form
Me.Records.SetFocus

' Makes the field I want to search through the current form
Me.Records.Form![Record ID].SetFocus

End Sub

 

[Tyler]

Your subroutine looks like it does the trick. I think if I play with turning

Yes, you are correct, the button is in the people subform. When the code
crashes and it takes me to the debugger and I hove the mouse over the "MaxID"
text, the control tip that pops up gives me the value that I am looking for,
it just seems that I have coded something wrong with the docmd. Most of the
problems I run into, is that I I don't reference subforms correctly. So I am
leaning towards the problem being that I need to do something else to allow
the docmd to run in the second subform. For example, if I were to simplify
my code by taking out the "Insert Into" statement, and do it with the
acNewRec code I would think the following code would work, but it doesn't.

Forms![Orders]![People].Form![Records].SetFocus
DoCmd.GoToRecord , , acNewRec

This just adds a new record in the [People] form, not the [Records] form.
This is about as minimalized of code as I can get so I know that the error is
in the DoCmd code, but I'm not sure how to fix it. I appreciate the time you
are spending trying to help me out, any help you can give me I appreciate it.

That's why I avoid DoCmd if there is any other way. Many
DoCmd methods just don't have have a way for you to specify
which object (form/subform) you are trying to manipulate.

The way I usually approach this kind of thing is along the
lines of this air code, but I don't know what might happen
when AllowAdditions is set to No.

Private Sub cmdAddRecord_Click()
Dim SQLStmt As String
Dim Maxid As Long

' Save any changes to current people record
If Me.Dirty Then Me.Dirty = False

' This inserts a new record in the record form
With Me.Records.Form.RecordsetClone
.AddNew
![People ID] = Me.[People ID]
Maxid = ![Record ID] 'I doubt you need this
.Update
Me.Records.Form.Bookmark = .LastModified
End With

' Makes the form I want to input into the current form
Me.Records.SetFocus

' Makes the field I want to search through the current form
Me.Records.Form![Record ID].SetFocus

End Sub