data entry on subform from VBA - Access 97

[Guest]

I am working in Access 97. I have a form bound to an order table for sales samples, with a subform bound to the order details (sku #, qty, etc.) table. There is a command button to run code that creates orders based on a set of queries detailing various people's needs for sales samples. In my code, I loop through one recordset containing the names of various people, and create an order header on the main form. I am trying to create an embedded loop through another recordset containing the skus and quantities that each person needs, in order to create detail records on the subform / order details table for each order header.

I am having trouble referring to the textbox controls on the subform in my code. I seem to be able to create one detail record for each header, but can't create any additional records. I keep getting error messages saying that the (sub)form is not open, so I can't go to a new record, and so on. How do I refer to the subform and its controls so as to avoid this problem? Currently, my code is in a class module attached to the main form - does this make a difference? Should I be using a standard module? I can post my code, if that would be helpful

Thanks
John B.

 

[Dirk Goldgar]

I am working in Access 97. I have a form bound to an order table for
sales samples, with a subform bound to the order details (sku #, qty,
etc.) table. There is a command button to run code that creates
orders based on a set of queries detailing various people's needs for
sales samples. In my code, I loop through one recordset containing
the names of various people, and create an order header on the main
form. I am trying to create an embedded loop through another
recordset containing the skus and quantities that each person needs,
in order to create detail records on the subform / order details
table for each order header.

I am having trouble referring to the textbox controls on the subform
in my code. I seem to be able to create one detail record for each
header, but can't create any additional records. I keep getting
error messages saying that the (sub)form is not open, so I can't go
to a new record, and so on. How do I refer to the subform and its
controls so as to avoid this problem? Currently, my code is in a
class module attached to the main form - does this make a difference?
Should I be using a standard module? I can post my code, if that
would be helpful.

Thanks,
John B.

You'd have to post the code you're trying to use before anyone could
give you specific advice. Quite likely you're trying to refer to the
subform using the same syntax you'd use for a main form, but that
doesn't work for a subform -- all references must be made by way of the
subform control on the main form.

I also think, based admittedly on very little evidence, that you may be
going about this the wrong way. Remembering that forms don't actually
contain data, they are just windows onto the data that is stored in
tables, it may well be easiest to use append queries to insert the
appropriate records directly into the relevant tables, and then just
requery the form. But I think you'd better post your code so that we
can all see what you're trying to do.

 

[Dirk Goldgar]

Comments and corrections inline ...

Thanks for responding. It's quite possible I'm going about this the
wrong way - I've only been doing this for a couple of months,
self-taught. I hadn't thought of using append queries, but that
might be easier. This method seems to work fine for creating the
records in the header table, I'm just having problems with the detail
records. Here is the code I am using. It's in a class module
attached to the form, called from the click event of a command
button. It doesn't seem to recognize the textbox controls on the
subform, so maybe I'm referring to them incorrectly. I've tried a
couple of different ways.

I don't see a problem there, but we'll see.

I'm not sure I'm moving the focus from the
form to the subform and back again correctly, either.

That you weren't.

Also, the
subform control on the main form, and the form that it refers to have
the same name currently (fsubSampleOrderDtl) - would renaming one of
them help?

No, that's no problem, and in fact I usually set it up that way so I
don't get confused and inadvertently use the SourceObject name instead
of the control name.

Although I do think I'd use append queries instead of looping through
and manipulating the form and subform, I've noted some corrections to
the code below, inline, that should either make it work or at least
bring it a lot closer.

Private Sub CreateOrders()

Dim Db As Database
Dim strSeason As String
Dim strSQL As String
Dim rsReqs As Recordset
Dim rsSku As Recordset

**INSERT**
Dim qdfSku As QueryDef
**END INSERT**

'Set Database and current season.
Set Db = CurrentDb
strSeason = Me.cboOrderSeason

'Create recordset of Sample Requirements for current season.
strSQL = "SELECT tblSampleReq.* FROM tblSampleReq WHERE
(((tblSampleReq.Season)='" & strSeason & "'));" ** DELETE **
Set rsReqs = Db.CreateQueryDef("", strSQL).OpenRecordset

** END DELETE **
**INSERT**
Set rsReqs = Db.OpenRecordset(strSQL)
**END INSERT**

'Loop through Sample Requirements recordset.
Do While rsReqs.EOF = False

'Create order header.
**DELETE**
If Me.NewRecord = False Then DoCmd.GoToRecord acDataForm, Me,
acNewRec

**END DELETE**
**INSERT**
Me.txtOrderName.SetFocus
If Me.NewRecord = False Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If
**END INSERT**

Me.txtOrderName = rsReqs("Name")
Me.txtOrderSeason = strSeason
Me.txtOrderStage = rsReqs("Stage")
Me.txtOrderDate = Date
Me.txtOrderLocID = "XXX"

'Set parameters and open Sku list. **DELETE**
With Db.QueryDefs("qfrmCSOSkuSet")

**END DELETE**
**INSERT**
Set qdfSku = Db.QueryDefs("qfrmCSOSkuSet")
With qdfSku
**END INSERT**

.Parameters("SelSeason") = strSeason
.Parameters("SelChannelSet") = rsReqs("ChannelSet")
.Parameters("SelGenderSet") = rsReqs("GenderSet")
Set rsSku = .OpenRecordset
End With

**INSERT**
' Transfer focus to subform
Me.fsubSampleOrderDtl.SetFocus
**END INSERT**

'Loop through Sku list.
Do While rsSku.EOF = False

'Create detail records
With Me.fsubSampleOrderDtl.Form **DELETE**
If .NewRecord = False Then DoCmd.GoToRecord , ,
acNewRec

**END DELETE**
**INSERT**
DoCmd.GoToRecord , , acNewRec
**END INSERT**

.txtDtlOrderID = Me.txtOrderID
.txtDtlSku = rsSku("Sku")
.txtDtlQty = rsReqs("QtyLeft")
.txtDtlDimension = "L"
End With
**DELETE**
DoCmd.GoToRecord , , acNewRec **END DELETE**
rsSku.MoveNext
Loop

'Close sku list and go to next header record.
rsSku.Close
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
rsReqs.MoveNext

Loop

rsReqs.Close

**INSERT**
Set rsSku = Nothing
Set rsReqs = Nothing
Set qdfSku = Nothing
**END INSERT**

End Sub

I *think* those changes should make it work. If these lines:

.txtDtlOrderID = Me.txtOrderID
.txtDtlSku = rsSku("Sku")
.txtDtlQty = rsReqs("QtyLeft")
.txtDtlDimension = "L"

don't work, try replacing the dots (.) with bangs (!).

 

[Guest]

Thanks, Dirk! That seems to work. I will keep the append queries idea in mind, certainly for future projects and possibly I will update this one later. It's sort of obvious now, but I had seen a KB article for loading OLE files into a table that used a form like mine, and I understood how to do that (I thought), so I didn't even think of the other way.

John