How to grab Data being entered in a Subform of a Main Form??

[qmacg]

Hello:
Any help on some ideas would be greatly appreciated

I'm trying to reuse some code that I use on a form in regular "Form View", but have it read the current active Subform which is in Datasheet view. I tried inserting in the code below "Screen.ActiveDatasheet", but looks to convoluted trying to access the current record with x and y positions as if you're using a grid. I mean it's a form(subform) for crying out loud... Any way here's the standard issue code similar to what's the help screens...

Dim ctl As Control
Dim strFieldValues As String, strFieldNames As String

strFieldValues = ""
strFieldNames = ""

Select Case strFieldPrefix
Case "EditSheet"
For Each ctl In Screen.ActiveForm.Controls
If TypeOf ctl Is TextBox Then
If Mid$(ctl.Name, Len(ctl.Name) - 1) = "ID" Then
Else
If Not ctl.Value = "" Then
strFieldNames = strFieldNames & ctl.Name & "||"
strFieldValues = strFieldValues & ctl.Value & "||"
End If
End If
End If
Next

strFieldNames=strFieldNames& "<<"& vbLf& strFieldValues & "<<" & vbLf

 

[MajP]

I have little idea what your question is, and no idea what you are talking about with the "x and y positions if you are using a grid". Are you using a flexgrid or some kind of Active X control?


This code is poorly written. Here are some pointers
1)dim ctl as access.control
Be specific in declaration you will see there are actually two control objects. Access and MSForms. A little point but kills people with DAO and ADO recordsets
2) delete the following. Waste of code. What else could it equal?
strFieldValues = ""
strFieldNames = ""
3)Put this code in a standard module and pass in the frm
Now you can use it for any form you pass it.

public sub yourCode(frm as access.form)
your code
for each ctl in frm.controls

4)The If then construct is very sloppy. Would be a lot easier to put a value in the tag property such as a "?" then looking for "ID" in the name. Regardless simplify your If then program flow. Bad coding to have an if then else which does nothing in the true case. Simply

If ctl.controlType = acTextBox
If not ctl.tag = "?" and not ctl.value = "" then
strFieldNames = strFieldNames & ctl.Name & "||"
strFieldValues = strFieldValues & ctl.Value & "||"
End If
End If

 

[qmacg]

Sigh...
Ok, don't take offense to this, but this is just a piece of code I copied in here. I'm not worried about making it perfect and i'm not trying to compete with you in any way. But here's what i'm trying to do...
Iterate through the current form that has focus (the subform which is in datasheet view), grab values in each field as if the form were in regular view.
The statement "For Each ctl In Screen.ActiveForm.Controls"
and the statement "for each ctl in frm.controls" you have above is basically the same thing, just semantics in how i'm calling it. They are both dynamic. I am actually calling the procedure from the subform's "BeforeUpdate" event.
And the "ID" thing is just so I skip picking up the ID's textfield in my strFieldNames/strFieldValues .

But back to my original question which is how can I iterate through the Subform's datasheet (dynamically) and read the field names and values just like the normal Form View of a form? Thanks

 

[MajP]

The statement "For Each ctl In Screen.ActiveForm.Controls"
and the statement "for each ctl in frm.controls" you have above is basically the same thing, just semantics in how i'm calling it.

1) No it is not semantics it is fundmentally different. I added a parameter to the procedure so you can refer to any form not just the active form, and call it from anywhere. Using Screen.activeform is not a safe way to call code because you may find that it does not refer to the form you want. Instead explicitly call the procedure passing the form as a parameter.

private form_beforeUpdate(cancel as integer)
call yourCode(Me)
end sub

So this should solve your problem.

2) I understand the ID thing. Just saying this
not ctl.tag = "?"
is a lot less complicated then
Mid$(ctl.Name, Len(ctl.Name) - 1) = "ID"
and a contstruct of
if something
do nothing
else
do something
end if
if poor programming

So there should be no reason the routine does not work. Unless you want to repeat the field names for each record and the values for each record. If that is what you want the something like this.

Public Sub getNamesValues(frm As Access.Form)
  Dim strNames As String
  Dim strValues As String
  Dim ctl As Access.Control
  Dim rs As DAO.Recordset
  Set rs = frm.Recordset
  rs.MoveFirst
  Do While Not rs.EOF
    For Each ctl In frm.Controls
      If ctl.Tag = "?" Then
        strNames = strNames & "||" & ctl.name
        strValues = strValues & "||" & ctl.Value
      End If
    Next ctl
    rs.MoveNext
  Loop
  Debug.Print strNames
  Debug.Print strValues
End Sub

This gives me two long strings repeating the control names and values.
Your post was not very clear because it used the term "Normal View" which I think you meant "Single Form View".

 

[qmacg]

So have you tested calling this from a Subform's Before_Update Event? Does it work for you?

 

[MajP]

Yes. It works from any event.

 

[qmacg]

Thanks, but looks like I've figured out something better. It gathers the data from a single record the way I need it.
No need for the recordset object, Tag .etc Again this is triggered from the Subform's Before_Update Event. The key to what I was looking for to make this read the subform's Fields is the "Screen.ActiveControl.Parent", followed by the frmSubForm assignment to the right of the = sign. I've tested this and it works great.

Call it from the Form's (really the subform) event...

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call TheProc()
End Sub

And Here is the procedure sitting in a Modules Tab...:
sub TheProc()
Dim frmSubForm As SubForm
Dim ctl As Control
Dim theParentForm As Form
Dim theSelectedSubform As Form

Set theParentForm = Screen.ActiveForm
Set theSelectedSubform = Screen.ActiveControl.Parent
Set frmSubForm = Forms(theParentForm.Name).Controls(theSelectedSubform.Name)


For Each ctl In frmSubForm.Controls
If TypeOf ctl Is TextBox Then
If Mid$(ctl.Name, Len(ctl.Name) - 1) = "ID" Then
Else
If Not ctl.Value = "" Then
strFieldNames = strFieldNames & ctl.Name & "||"
strFieldValues = strFieldValues & ctl.Value & "||"
End If
End If
End If
Next
strFieldNames = strFieldNames & "<<" & vbLf & strFieldValues & "<<" & vbLf
debug.Print strFieldValues

End Sub

 

[TheAceMan1]

How are ya qmacg . . .

I've been following this thread looking for certain info and have to ask:

[blue]Whats the point of reading/copying an entire subform! ... [/blue]

In the logical world of Db of any kind, this isn't making much sense!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Good luck with that, but again it is bad design. Using active form and active control as a way to enforce program control is risky. Maybe for simple databases this is fine, but for a more complex application I would caution against it. You can reference Litwin and Getz Desktop Developers Handbook for more on the subject. As I said in my first post simply pass the form object.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call TheProc(Me)
End Sub

and then just simplify this to

sub TheProc(frm as access.form)
For Each ctl In frm.Controls
rest of your code
End Sub

Seems a whole lot simpler.

This is all just a waste and could all be removed.
Dim frmSubForm As SubForm
Dim ctl As Control
Dim theParentForm As Form
Dim theSelectedSubform As Form
Set theParentForm = Screen.ActiveForm
Set theSelectedSubform = Screen.ActiveControl.Parent
Set frmSubForm = Forms(theParentForm.Name).Controls(theSelectedSubform.Name)

 

[qmacg]

Hey guys, i'll respond to both of your posts in this one. First "TheAceMan1", how's it going buddy. Here's the reason behind all of this. The purpose in my code was to capture the record that has been edited/added within the active row of the subform. I did not want to capture all of the records of a subform. This was all ultimately to use on one form that had lets say 2 or 3 subforms on it. So each time I clicked on the record of a subform (that is in datasheet view) and added or changed information, I was going to combine the field/values and append them into a seperate procedure combine with other data to generate data in an Audit type table to keep track of certain changes.

Now i'm not trying to be funny here, by I'll ask you again MajP... Have you used your code on a database that you have which has a subform in datasheet view? Does it work?
Again as I stated before, I'm not trying to make this a work of art here. I tried something similar to your code but ran across grabbing data from the "MainForm". I read somewhere in the help screens when you try to access a subform within a MainForm, the Me and Screen.activeform object will grab the controls of the MainForm. But hey guys, I got it to work, that's what matters. Some say programming is an Art, and hey I'm not very good with art but i tend to get by with my limited knowledge. Thanks again for your Posts. The little guys appreciate your knowledge and guidance

 

[MajP]

I read somewhere in the help screens when you try to access a subform within a MainForm, the Me and Screen.activeform object will grab the controls of the MainForm.

Me refers to the current instantiated object of the class. Therefore if used in the main form, ME refers to the main form, if used in a subform Me refers to the subform. If used in a custom class, Me refers to the instantiated object of the custom class. And of course it works. I will normally state in my posts if the code is untested. I already stated that it works.