Copy first record only, of a forms subform to a new forms feild

[Billp]

I want to be able to copy the first record only, of an existing forms subform
to a new form.
I have a form called workscard that has a subform that contains many records.
The first record of the subform has the key elements.
This first record I need to copy to a new form called production Notes.
I have the following

' Evaluate filter before it is passed to Dlookup function.

strWorksNumber = Me!Works_Number
strCustomer = Me!Company_Name
StrFilter1 = "Works_Number = """ & Me!Works_Number & """"
'as it is a text value


With Forms!frmProject_Notes_1
.cboWorks_Number = strWorksNumber
.Customer = strCustomer
' in here we must take the first record from the Inventory
Item
.First_Item = Nz(DLookup("[Description]",
"tblInvoiceDetails", StrFilter1))

End With

But it is not filtered to the first record so it doesn't work to well.

Thanks in advance for valued assistance.
Kind Regards
Bill

 

[Tom van Stiphout]

On Sun, 9 Aug 2009 21:41:01 -0700, Billp

I'm not following your code entirely, but to answer your question of
how to access the first record in a subform: use its recordsetclone:
dim rs as dao.recordset
set rs = Me.mySubformControl.Form.RecordsetClone
if rs.RecordCount > 0 then
rs.MoveFirst
'You are now at the first record in the subform. For fun we'll print
the values
dim fld as dao.field
for each fld in rs.Fields
debug.print fld.Name, fld.Value
next
end if
(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP

 

[Billp]

Thank you Tom,
Really appreciate the assisatance.

I have updated to the following

Dim db As DAO.Database
Dim rst As DAO.Recordset 'rsttblProjectNotes
Dim rs As DAO.Recordset 'rstsubformorders
Dim strWorksNumber As String
Dim strCustomer As String
Dim strInventory As String

'first check if this is a new or existing notes form
'enter a dialog box with yes no
'then check if a notes already exists
'open it up at that record
'or start a new record
If msgbox("Is This an Existing Notes Record?", vbYesNo, _
"Open Project Notes") = vbYes Then

Set db = CurrentDb
Set rst = db.OpenRecordset("tblProjectNotes", dbOpenDynaset)
rst.FindFirst "Works_Number = """ & Me!Works_Number & """"

If rst.NoMatch Then 'the workscard number is not in the list

msgbox "There is no matching Project Notes - therefore a new
Project Notes Record will be created"
' open form with new details merged into form
strWorksNumber = Me!Works_Number
strCustomer = Me!Company_Name

Call FlipEnabled(Forms!frmProject_Notes_1,
Forms!frmProject_Notes_1.cboWorks_Number)
Set rs = Me.fsubOrderDetails.Form.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveFirst 'first record of the subform
strInventory = rs.Value 'store this text value
End If


With Forms!frmProject_Notes_1
.cboWorks_Number = strWorksNumber
.Customer = strCustomer
' in here we must take the first record from the Inventory
Item
.First_Item = strInventory
End With
Else
DoCmd.OpenForm "frmProjectNotes_1", , , "[Works_Number] = '" &
Me!Works_Number & "'"
End If

Else
'its not
'transfer the details from the works card to a new notes record

With Forms!frmProject_Notes_1
.cboWorks_Number = strWorksNumber
.Customer = strCustomer
' in here we must take the first record from the Inventory
Item

End With

End If

How can I make the value of the first record cop (for want of a better word)
to strInventory?

Thank You
Best Regards
Bill

On Sun, 9 Aug 2009 21:41:01 -0700, Billp

I'm not following your code entirely, but to answer your question of
how to access the first record in a subform: use its recordsetclone:
dim rs as dao.recordset
set rs = Me.mySubformControl.Form.RecordsetClone
if rs.RecordCount > 0 then
rs.MoveFirst
'You are now at the first record in the subform. For fun we'll print
the values
dim fld as dao.field
for each fld in rs.Fields
debug.print fld.Name, fld.Value
next
end if
(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP

I want to be able to copy the first record only, of an existing forms subform
to a new form.
I have a form called workscard that has a subform that contains many records.
The first record of the subform has the key elements.
This first record I need to copy to a new form called production Notes.
I have the following

' Evaluate filter before it is passed to Dlookup function.

strWorksNumber = Me!Works_Number
strCustomer = Me!Company_Name
StrFilter1 = "Works_Number = """ & Me!Works_Number & """"
'as it is a text value


With Forms!frmProject_Notes_1
.cboWorks_Number = strWorksNumber
.Customer = strCustomer
' in here we must take the first record from the Inventory
Item
.First_Item = Nz(DLookup("[Description]",
"tblInvoiceDetails", StrFilter1))

End With

But it is not filtered to the first record so it doesn't work to well.

Thanks in advance for valued assistance.
Kind Regards
Bill