Trying to make a button to create new record and increment field

[MaDmiX]

Hi everyone,

I have been searching around for a solution to this problem but can't seem to find one. I have a subform containing a field (called segment_#). I have created a command button to create a new record on this form and increment the segment_# field by one (actually it assigns a value to match the record number, which is ideal). When I click, however I am getting an error saying "Method or data member not found", and the "segment_#" in my code is highlighted. Could it be that the code is trying to assign the value to "segment_#" before the record is actually created? Here's my code:

Private Sub Add_Seg_Click()
DoCmd.GoToRecord , "", acNewRec
Me.Segment_# = CurrentRecord
End Sub

Any help is greatly appreciated.

Thanks,

Ken

 

[SBendBuckeye]

If your command button is on the main form, your code is trying to goto a new record on the main form, not the subform. Try specifying the first 2 parameters as

DoCmd.GoToRecord acDataForm, "YourSubformName", acNewRec

and see if that helps. Also, from the main form you reference the subform via the main form subform control and then its form object. It should be something like this:

Forms("MainName&quot.MainSubFormControlName.Form.segment_# =

This is off the top of my head so the syntax may need to be tweaked a little bit.

Good LucK!

 

[SBendBuckeye]

Check out this thread about subform naming also.

thread705-451364

Good Luck!

 

[MaDmiX]

Thank you SBendBuckeye,

I see where I went wrong and corrected the code. I got a call from one of the guys in the tech room. Seems that the segment_#'s are occasionally being all numbered "1". I had set the default value for this field to 1 so that the first segment would # properly. Subsequent segments should have been overridden with the = CurrentRecord value (or so I thought). Could this strange behavior have anything to do with the multiuser environment?

Ken

 

[SBendBuckeye]

Not sure about the multiuser part, but a form will automatically filter its subform based on the link field you provided. So each record in the main form will have a record 1 in the subform.

As an example, let's say your main form is an invoice header record and the subform contains invoice detail lines. Here is how some data would look if I am understanding you correctly based on previous posts. This is from the perspective of the form

Record 1
tblInvoice
key1 customer status
01 SM01 OK

tblInvoiceDetail
key2 key1 qty
01 01 5
02 01 7
03 01 8

Record 2
tblInvoice
key1 customer status
06 AB04 OK

tblInvoiceDetail
key2 key1 qty
01 06 2
02 06 1

So not if you look at the tables, you would have the following:

tblInvoice
key1 customer status
01 SM01 OK
06 AB04 OK

tblInvoiceDetail
key2 key1 qty
01 01 5
02 01 7
03 01 8
01 06 2
02 06 1

As you can see, you have multiple 01, 02 records in the key2 field in the invoice detail record. That is fine as long as you use them in conjunction with key1 from the invoice table. If you use them together, there will always be only one 01 per invoice, etc. If you don't use them together, then you have no easy way of differentiating them.

If this is beneath your knowledge level, I apologize. When I first started using Access sometimes I would be too close to the details to see what was actually happening.

Good Luck!