Solved Referencing a field within a RecordSet... (3 Viewers)

[DJL910]

Hi - could use some help on what is hopefully a straightforward nomenclature issue...

I have a button on form that when clicked, updates the form's data (4 fields from Tbl_BatchRecord) into multiple transactional records (Table: Tbl_LotN) via a record set. All works great to transfer this data. However, I would also like to populate a 5th field (LotN_QtyStart) by using the value from a field (LotN_QtyPlanned) within this recordset. I am struggling on how to reference that field appropriately....

In normal text, I want to update: ![LotN_QtyStart] with the existing value in [LotN_QtyPlanned]

'Set SKU-Lot default values
    Dim rs As DAO.Recordset, strsql3 As String
        strsql3 = "Select * From Tbl_LotN WHERE LotN_BatchIDREF = '" & Me.BR_BatchID & "'"
    Set rs = CurrentDb.OpenRecordset(strsql3)
    With rs
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
          While (Not .EOF)    'if true, we exit the while loop
          If .Updatable Then
              .Edit
              ![LotN_Lot] = xBR_BatchLot
              ![LotN_Expiry] = xExpiry
              ![LotN_BuildReason] = xBuildReason
              ![LotN_QtyStart] = Me.Recordset("LotN_QtyPlanned")  'HOW TO GET THIS??'
              .Update
          End If
          .MoveNext   'otherwise in a loop
          Wend
        End If
     .Close  'closes the recordset
    End With
    MsgBox "Lot Defaults Entered", vbOKOnly

ExitSub:
        Set rs = Nothing
        Exit Sub

ErrorHandler:
    Resume ExitSub

Note: Yes, I understand data structuring and how to not duplicate data, etc. I am doing this to pre-populate 'default' values that may be overwritten in specific transactional data sets.

 

[Pat Hartman]

Duplicating the data in two tables is jus plain wrong in the world of relational databases. Any time you want the data from the batch table, you would join the transaction table to the batch table on BatchID. As with ALL 1-many relationships, the only field you store in the many-side table is the Foreign Key.

Add the data fields to the batch table and prepopulate the defaults there. If the data can actually be different on different rows of the many-side table, then the fields do not belong in the parent table.

 

[The_Doc_Man]

I THINK what you want is just this:

![LotN_QtyStart] = Me![LotN_QtyPlanned]

In particular, if that is part of the recordset, you should be able to reference it as shown. This thread suggests it is that simple.

VBA Reference to field value in form recordset

Hi, I have a bound form which has a number of fields in it's recordset which are NOT bound to any controls on the form. In VBA, I need to get the value of one of these fields in the current record. However the field I need will change each time the procedure is run and is determined by the vba...

www.access-programmers.co.uk

If that does not work, then a quick-and-dirty trick is to create a control on the form that binds to [LotN_QtyPlanned] ... but if you don't want to see it, make the control with .Visible=No and .Enabled=No and then make sure to set it .TabStop=No as well. If the control is on the form, even disabled and invisible, VBA can see it. There are other ways but this one is simple. You can set it in form design mode and just leave it that way without ever touching it from the graphic interface.

 

[DJL910]

Doc Man, THANK YOU! That didn't work, but I removed the "me" and it did the trick. Effectively, just matching the format of the other recordset fields.

![LotN_QtyStart] = Me![LotN_QtyPlanned]

 

[DJL910]

Duplicating the data in two tables is jus plain wrong in the world of relational databases. Any time you want the data from the batch table, you would join the transaction table to the batch table on BatchID. As with ALL 1-many relationships, the only field you store in the many-side table is the Foreign Key.

Add the data fields to the batch table and prepopulate the defaults there. If the data can actually be different on different rows of the many-side table, then the fields do not belong in the parent table.

Pat: this response just isn't helpful at all - it doesn't provide a solution nor does it even address the problem of my post. Alas, I even tried to suppress your temptation to bloviate by including my "Note" at the end of the original post.

 

[The_Doc_Man]

Doc Man, THANK YOU! That didn't work, but I removed the "me" and it did the trick. Effectively, just matching the format of the other recordset fields.

![LotN_QtyStart] = Me![LotN_QtyPlanned]

If removing the Me! made this work, then I was confused about where the QtyPlanned item was located. On re-reading your description above, I see that the planned quantity came from that recordset too. The only issue then is why you are controlling that recordset which doesn't seem to have a clear link with the form. Are those "X" items that you also assign to recordset fields from the form, either as controls or variables?

 

[Pat Hartman]

Pat: this response just isn't helpful at all - it doesn't provide a solution nor does it even address the problem of my post. Alas, I even tried to suppress your temptation to bloviate by including my "Note" at the end of the original post.

I gave you a suggestion to solve the problem. You didn't notice because you didn't like the suggestion. Please reread the above. When the data can be different in the many-side table, then it doesn't belong in the batch table. It only belongs in the detail table. If the data is consistent for the batch, then it belongs in the batch table. It should not be in both.

If you intend to ignore the expert advice and keep the data in both places, there are better ways of accomplishing the goal. You need to populate each record as it is being created. I would use the subform's BeforeInsert event to copy the values from the batch header to the current detail record. An alternative would be to use the Current Event of the batch form to populate the Default values of the subform. You wouldn't update multiple records after the fact. If you have data to clean up, then you need a one time update query to transfer the missing values from one table to the other.

If you use the subform's BeforeInsert event method, you are "pulling" data from the parent record to the subform record. The BeforeInsert event runs as the first event after the user types the first character into any control and it obviously only runs for new records.
Me.SomeField = Me.Parent.SomeField

If you use the mainform's Current event method, you are ""pushing" data from the parent record to the defaultValue property of fields in the subform record.

Me.subformname.Form!SomeField.DefaultValue = Me.SomeField

My preference is always the "pull" method because if you are debugging the subform, you won't be expecting code in the main form to be altering it so if you forgot how you did this, it may take a while to recreate it. Obvious is always better than slick/elegant. This keeps all the code that affects the form, IN the form and not in some other form where you may not think to look.

Don't worry though. You won't hear from me again.

 

[DJL910]

If removing the Me! made this work, then I was confused about where the QtyPlanned item was located. On re-reading your description above, I see that the planned quantity came from that recordset too. The only issue then is why you are controlling that recordset which doesn't seem to have a clear link with the form. Are those "X" items that you also assign to recordset fields from the form, either as controls or variables?

I just removed "me" ... the explanation mark stays "![LotN_QtyPlanned]

Yes, the other "x" items are user-entered controls and variables from the form ...

 

[DJL910]

I gave you a suggestion to solve the problem. You didn't notice because you didn't like the suggestion. Please reread the above. When the data can be different in the many-side table, then it doesn't belong in the batch table. It only belongs in the detail table. If the data is consistent for the batch, then it belongs in the batch table. It should not be in both.

If you intend to ignore the expert advice and keep the data in both places, there are better ways of accomplishing the goal. You need to populate each record as it is being created. I would use the subform's BeforeInsert event to copy the values from the batch header to the current detail record. An alternative would be to use the Current Event of the batch form to populate the Default values of the subform. You wouldn't update multiple records after the fact. If you have data to clean up, then you need a one time update query to transfer the missing values from one table to the other.

If you use the subform's BeforeInsert event method, you are "pulling" data from the parent record to the subform record. The BeforeInsert event runs as the first event after the user types the first character into any control and it obviously only runs for new records.
Me.SomeField = Me.Parent.SomeField

If you use the mainform's Current event method, you are ""pushing" data from the parent record to the defaultValue property of fields in the subform record.

Me.subformname.Form!SomeField.DefaultValue = Me.SomeField

My preference is always the "pull" method because if you are debugging the subform, you won't be expecting code in the main form to be altering it so if you forgot how you did this, it may take a while to recreate it. Obvious is always better than slick/elegant. This keeps all the code that affects the form, IN the form and not in some other form where you may not think to look.

Don't worry though. You won't hear from me again.

You didn't provide a suggestion to "the problem" - you provided a suggestion to solve a problem you thought existed. I added my "Note" to avoid people immediately jumping in with the holier than thou data architecture arguments about 1-many relationships and how "keeping data in both places is evil" that nearly always occurs in response to any post remotely tangential to it. Sure, yes, all of the best-practice is true, it's just not always applicable to the post or the problem.

"When the data can be different in the many-side table, then it doesn't belong in the batch table. It only belongs in the detail table. If the data is consistent for the batch, then it belongs in the batch table. It should not be in both." No kidding - the whole point of the post was asking how to copy data from one field of the DETAIL table into a second field in the DETAIL table! Which again, likely re-triggers thoughts of how evil it is to have data in two places.

But, if you look at the described use-case scenario ('pre-populating default values) and review the field names, you see that I'm storing a "Planned" value and an actual "Start" value. So, you plan 5 and most times actually produce 5. When the user clicks the "receive" button, the "start" value is pre-populated with the "planned" value of 5 and the user only has to go to the sub-form to edit values if needed. The same applies to the other three 'parent' fields (Lot, Expiry and Build Reason), which are being written from the Batch table to the mutliple Detail table records. Because they are not always the same, they are stored distinctly.

All the other commentary on push v. pull, BeforeInsert v onCurrent, etc., obvious v slick/elegant is redundant.

These forums are super effective to support eachother and I'm grateful for them, but its utility quickly diminshes the more that off-topic comments are introduced into threads.

 

[The_Doc_Man]

OK, since some of the other data comes from the form, and since you are merely initializing something for later updating, I have enough of an idea to see what is happening.

Glad I could at least lead you in a good direction even if I wasn't perfectly clear on where you were going at first.

 

[Isaac]

Doc Man, THANK YOU! That didn't work, but I removed the "me" and it did the trick. Effectively, just matching the format of the other recordset fields.

![LotN_QtyStart] = Me![LotN_QtyPlanned]

You're making it visually and mentally harder than it needs to be with the extensive use of With blocks.

Just declare variables to the most granular level (i.e., Dim rs as dao.recordset), and then you can type rs. and begin to see the intellisense options.
Had you done that, I bet you would not have had to wonder about this question of how to reference it, as it would have been
more obvious - i.e. rs.fields("fieldname").value = rs.fields("fieldname").value

just my opinion

 

[Gasman]

Well, if you want one field to be set from another field in the same recordset, I would expect the syntax to be the same?
With or without with blocks.

 

[Isaac]

Well, if you want one field to be set from another field in the same recordset, I would expect the syntax to be the same?
With or without with blocks.

I just think beginners (and anyone roughly near there) overly confuse themselves with With blocks and also deprive of a more easy to understand intellisense which also teaches you more.

As for the same syntax, inside the with block it might be something like .fields("fieldname").value but outside the with block it might be something like variablerecordsetname.fields("fieldname").value

But changed syntax isn't really the point i was making

 

[The_Doc_Man]

I find that WITH blocks often are visually helpful in that you can immediately understand when someone is working on a bunch of stuff from the same recordset or structure. Nested WITH blocks... not so much. But single WITH blocks are pretty good indicators of localized work.

 

[June7]

Okay, in new record QtyStart is set with a default value derived from QtyPlanned.
It's not clear to me why or when QtyStart would be changed.

 

[cheekybuddha]

With blocks and also deprive of a more easy to understand intellisense

This is incorrect - intellisense works just fine in With ... End With blocks.

 

[Pat Hartman]

I know I said I wouldn't respond again but I did provide the exact answer you need. The "push" solution which sets the DefaultValue properties is commonly used to copy data from one detail to a next. I can't really help someone like you so this really is it. Argue with the others.