please help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I havea form with two sub forms contained within it. neither the two subforms
are linked to the main form in any way

What I wish to do is on the first subform, highlight a record within that
sub form, then move to the other subform and highlight a record in that
subform. Double click that record (either the record or a field within that
record) and then have the value of a field in that record automaticlly
inserted into a field in the originally highlighted record in the first
subform.

is this possible?
all subforms are in datasheet mode
 
Yes, it's possible, but you may need to add a textbox to the main form to
act as a link. The record selectors on the left side of your subform will
trigger the form's click event, so your code to fill you new unbound textbox
may loook something like this:

Private Sub Form_Click()
Me.Parent.txtPlaceholder = Me.txtIDField
End Sub

Now the textbox will contain a value corresponding to the record ID of the
selected record in the first subform.

Now use the click event of the second subform to update the field in the
first subform, like this:

Private Sub Form_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From SourceTableOfFirstSubform Where ID
=" & Me.Parent.txtPlaceholder)

With rst
.Edit
!FieldToUpdate = Me.txtDataToUse
.Update
End With

Forms!FirstSubform.Form.Requery

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

I haven't tested this, but it should work. Of course you need to use your
own field and control names.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thankyou Arvin

a brilliant solution, I ahve no expanded this to keep adding other entrie to
it
eg, 1, 2, 4

so that the subform 1 field grows

On this How can I interogate the field that looks like "1, 2, 7, 10, 3, 6,
112" to retrive the individual numbers

--
Regards


Patrick Stubbin
 
On this How can I interogate the field that looks like "1, 2, 7, 10, 3, 6,
112" to retrive the individual numbers
Click to expand...

With considerable difficult. Is there ANY chance that you can change
your data structure so that this one-to-many relationship is properly
modeled as a one-to-many relationship (with two tables) rather than
storing multiple values in one field, in violation of database
normalization principles?

Take a look at the Split() function in the VBA help. You won't be able
to reliably do this with a query Where clause, I fear - or it will be
a monstrously complex one if you can.

John W. Vinson[MVP]
 
Thanks.

But managed to do it with a very small query (or in fact 3)
--
Regards


Patrick Stubbin
 
As John says, you really need to parse out your field's data into separate
records in their own table. Here's a parsing function that will let you do
it one at a time:

Function ParseIt(strIn As Variant, intCounter As Integer, strSep As String)
As String
Dim intPos As Integer
Dim intPos1 As Integer
Dim intCnt As Integer

intPos = 0
For intCnt = 0 To intCounter - 1
intPos1 = InStr(intPos + 1, strIn, strSep)
If intPos1 = 0 Then intPos1 = Len(strIn) + 1
If intCnt <> intCounter - 1 Then intPos = intPos1
Next intCnt

If intPos1 <> intPos Then
ParseIt = Mid(strIn, intPos + 1, intPos1 - intPos - 1)
Else
ParseIt = ""
End If

End Function

Use it like this:

?ParseIt("1, 2, 7, 10, 3, 6, 112",5,",")
3

With a bit of effort you could write a wrapper function that would feed the
intCounter variable within a loop, if you had to do this often. As far as I
am concerned, for a mere 7 times, I'd just keep changing it manually. You
can also run the function in a query:

Select ParseIt([YourField],1,",") As NewColumn From Your Table;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top