Changing Subform Detail Rows when Form "master" item changes

G

Guest

I have a form/subform that I have created using the forms wizard. The main
form contains unique Asset Name. The Subform displays associated, multiple
data records that are linked to that master. That works just fine, and I
right click on the "asset" field in the form, set a filter for, say,
"XYZAsset" and Access brings up the associated ZXYAsset detail rows. Fine.

But, what I would RATHER do, rather than having to keep changing the filter,
is have a dropdown/Combo box that displays all assets in a list I can choose
from, and then, have the associated detail records display.

Any ideas on a simply (simplest) way of accomplishing this? (I am not super
strong on VBA coding, but can muddle my way through it, if needed).

Thanks!

PatK
 
G

Guest

Here is code that is the common way to look up a value in a field, and have
the form position on that record. Where this code looks for Activity, you
will want to change to use your Asset Name. It goes in the After Update
event of the combo box.

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtMactivity = Me.cboActivity
Me.frmSubAttributeTable!txtMactivity = Me.cboActivity
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End If
 
G

Guest

Maybe said:
If Not IsNull(Me.cboActivity) Then
Click to expand...

Where is Me.cboActivity coming from? Is that the name of the cbo box
control????? What does "Me." mean? (method? just a convention? SOrry..I
see this so much in these newsgroups).

Thanks...Think I can take a shot at it from there!
Pat

Klatuu said:
Here is code that is the common way to look up a value in a field, and have
the form position on that record. Where this code looks for Activity, you
will want to change to use your Asset Name. It goes in the After Update
event of the combo box.

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtMactivity = Me.cboActivity
Me.frmSubAttributeTable!txtMactivity = Me.cboActivity
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End If


PatK said:
I have a form/subform that I have created using the forms wizard. The main
form contains unique Asset Name. The Subform displays associated, multiple
data records that are linked to that master. That works just fine, and I
right click on the "asset" field in the form, set a filter for, say,
"XYZAsset" and Access brings up the associated ZXYAsset detail rows. Fine.

But, what I would RATHER do, rather than having to keep changing the filter,
is have a dropdown/Combo box that displays all assets in a list I can choose
from, and then, have the associated detail records display.

Any ideas on a simply (simplest) way of accomplishing this? (I am not super
strong on VBA coding, but can muddle my way through it, if needed).

Thanks!

PatK
Click to expand...
Click to expand...
 
G

Guest

Me. is a short cut for the name of the form you are currently in. It would
be the same as Forms!MyFormName! You will see it a couple of ways Me. and
Me! Either is correct. The difference between . and !, in most cases is
that . means a property or method of an object, and the ! means a user
defined object in the collection. the Me. works for either.
cboActivity is the name of the combo box on the form I took the example
from.
So, in my code I use Me.cboActivity which returns the current value of the
control. It could be written Forms!frmAttribute!cboActitivy



PatK said:
Maybe said:
If Not IsNull(Me.cboActivity) Then
Click to expand...

Where is Me.cboActivity coming from? Is that the name of the cbo box
control????? What does "Me." mean? (method? just a convention? SOrry..I
see this so much in these newsgroups).

Thanks...Think I can take a shot at it from there!
Pat

Klatuu said:
Here is code that is the common way to look up a value in a field, and have
the form position on that record. Where this code looks for Activity, you
will want to change to use your Asset Name. It goes in the After Update
event of the combo box.

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtMactivity = Me.cboActivity
Me.frmSubAttributeTable!txtMactivity = Me.cboActivity
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End If


PatK said:
I have a form/subform that I have created using the forms wizard. The main
form contains unique Asset Name. The Subform displays associated, multiple
data records that are linked to that master. That works just fine, and I
right click on the "asset" field in the form, set a filter for, say,
"XYZAsset" and Access brings up the associated ZXYAsset detail rows. Fine.

But, what I would RATHER do, rather than having to keep changing the filter,
is have a dropdown/Combo box that displays all assets in a list I can choose
from, and then, have the associated detail records display.

Any ideas on a simply (simplest) way of accomplishing this? (I am not super
strong on VBA coding, but can muddle my way through it, if needed).

Thanks!

PatK
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi: At this point, I am getting an error telling me that rst is undefined.
Here is my "total" code, so my apologies if you may have assumed that I may
have included certain things to get to this. This is literally my second
attempt at anything like this, and my "books" are not really helping me with
this. Here is my code. Ideas as to why rst is undefined? My form name is
"frm RMA Checklist" and the input combo box control is named cboAsset. What
am I missing (A lot... I am embarrassed to say, I'm sure)....

Option Compare Database
Option Explicit
Private Sub cboAsset_AfterUpdate()

If Not IsNull(Me.cboAsset) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Asset] = '" & Me.cboAsset & "'"
If rst.NoMatch Then
If MsgBox("Add Asset Number " & Me.cboAsset & " To the Attribute
Table", vbYesNo + vbQuestion + vbDefaultButton2, "Asset Not Found") = vbYes
Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtAsset = Me.cboAsset
Me.txtMasset = Me.cboAsset
Me.frmSubAttributeTable!txtMasset = Me.cboAsset
Else
Me.cboAsset = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End If
End Sub
 
G

Guest

Sorry, I missed a line when I was copying and pasting. It goes right after
the Sub statement:
Private Sub cboAsset_AfterUpdate()
Dim rst As Recordset

The reason that happened is you have Option Explicit in your declarations.
That is a very good thing. What it means is you have to Dim every variable
before you can use it.
 
G

Guest

I did add that Dim statement (Thanks), but I am having all kinds of compile
errors on all of the Me. variables. HEre is my code, now...(only the one new
line). This is, literally, my code. Was I supposed to change all the Me.
prefixes to somethign else? how do you DIM one of the variables? Thanks

Option Compare Database
Option Explicit
Private Sub cboAsset_AfterUpdate()
Dim rst As Recordset

MsgBox Me.cboAsset
If Not IsNull(Me.cboAsset) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Asset] = '" & Me.cboAsset & "'"
If rst.NoMatch Then
If MsgBox("Add Asset Number " & Me.cboAsset & " To the Attribute
Table", vbYesNo + vbQuestion + vbDefaultButton2, "Asset Not Found") = vbYes
Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtAsset = Me.cboAsset
Me.txtMasset = Me.cboAsset
Me.frmSubAttributeTable!txtMasset = Me.cboAsset
Else
Me.cboAsset = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End If
End Sub


Klatuu said:
Sorry, I missed a line when I was copying and pasting. It goes right after
the Sub statement:
Private Sub cboAsset_AfterUpdate()
Dim rst As Recordset

The reason that happened is you have Option Explicit in your declarations.
That is a very good thing. What it means is you have to Dim every variable
before you can use it.

PatK said:
Hi: At this point, I am getting an error telling me that rst is undefined.
Here is my "total" code, so my apologies if you may have assumed that I may
have included certain things to get to this. This is literally my second
attempt at anything like this, and my "books" are not really helping me with
this. Here is my code. Ideas as to why rst is undefined? My form name is
"frm RMA Checklist" and the input combo box control is named cboAsset. What
am I missing (A lot... I am embarrassed to say, I'm sure)....

Option Compare Database
Option Explicit
Private Sub cboAsset_AfterUpdate()

If Not IsNull(Me.cboAsset) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Asset] = '" & Me.cboAsset & "'"
If rst.NoMatch Then
If MsgBox("Add Asset Number " & Me.cboAsset & " To the Attribute
Table", vbYesNo + vbQuestion + vbDefaultButton2, "Asset Not Found") = vbYes
Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtAsset = Me.cboAsset
Me.txtMasset = Me.cboAsset
Me.frmSubAttributeTable!txtMasset = Me.cboAsset
Else
Me.cboAsset = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End If
End Sub
Click to expand...
Click to expand...
 
G

Guest

Me. refers to the current form, so this code should be in the After Update
event of your combo box. Those controls you are calling variables are not
variables. They are controls on your form. Be sure they are the names of
controls.
For example, Me.txtAsset refers to a control named txtAsset on your form.
Verify that the controls exist and are named correctly. In this case you
may not have nor need a control named txtAsset. In my original code, I have
two controls for Activity (Assest). cboActivty which is a combo box I use to
do the searching, but it is not the control bound to the recordsource.
txtActivity is the control bound to the recordsource. So this line:
Me.txtActivity = Me.cboActivity
is assigning a new Activity number to the bound control.

PatK said:
I did add that Dim statement (Thanks), but I am having all kinds of compile
errors on all of the Me. variables. HEre is my code, now...(only the one new
line). This is, literally, my code. Was I supposed to change all the Me.
prefixes to somethign else? how do you DIM one of the variables? Thanks

Option Compare Database
Option Explicit
Private Sub cboAsset_AfterUpdate()
Dim rst As Recordset

MsgBox Me.cboAsset
If Not IsNull(Me.cboAsset) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Asset] = '" & Me.cboAsset & "'"
If rst.NoMatch Then
If MsgBox("Add Asset Number " & Me.cboAsset & " To the Attribute
Table", vbYesNo + vbQuestion + vbDefaultButton2, "Asset Not Found") = vbYes
Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtAsset = Me.cboAsset
Me.txtMasset = Me.cboAsset
Me.frmSubAttributeTable!txtMasset = Me.cboAsset
Else
Me.cboAsset = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End If
End Sub


Klatuu said:
Sorry, I missed a line when I was copying and pasting. It goes right after
the Sub statement:
Private Sub cboAsset_AfterUpdate()
Dim rst As Recordset

The reason that happened is you have Option Explicit in your declarations.
That is a very good thing. What it means is you have to Dim every variable
before you can use it.

PatK said:
Hi: At this point, I am getting an error telling me that rst is undefined.
Here is my "total" code, so my apologies if you may have assumed that I may
have included certain things to get to this. This is literally my second
attempt at anything like this, and my "books" are not really helping me with
this. Here is my code. Ideas as to why rst is undefined? My form name is
"frm RMA Checklist" and the input combo box control is named cboAsset. What
am I missing (A lot... I am embarrassed to say, I'm sure)....

Option Compare Database
Option Explicit
Private Sub cboAsset_AfterUpdate()

If Not IsNull(Me.cboAsset) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Asset] = '" & Me.cboAsset & "'"
If rst.NoMatch Then
If MsgBox("Add Asset Number " & Me.cboAsset & " To the Attribute
Table", vbYesNo + vbQuestion + vbDefaultButton2, "Asset Not Found") = vbYes
Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtAsset = Me.cboAsset
Me.txtMasset = Me.cboAsset
Me.frmSubAttributeTable!txtMasset = Me.cboAsset
Else
Me.cboAsset = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform in subform 2
subform list/ subform detail double click 1
Link Child/Master Fields from Three Combo Boxes in Subform 2
Master/Detail Form 4
Group update via a subform 1
detail formatted in columns 1
Repost - Subform Problem 6
Filter eliminating records without and detail records 2

Top