Hi all - I've been working on this from various angles for days, and I'm pretty well stumped at this point. Hope someone has some ideas. Working in Access 2000...
I’m trying to write code to assign a 3-part identifier to each new record.
The 3 parts are:
BidNumPt1: two-digit current year (BidNumPt1 = Format(Date, “yy”))
BidNumPt2: three-digit incremental number based on the year (Pt1) and the salesperson (Pt3)
BidNumPt3: one-digit code for each salesperson
(In the end, the concatenated bid number looks like 08007.3, for instance.)
I’m trying to figure out how to get the database to insert the next number for BidNumPt2.
I have a form (frmNewBids) based on qryBids, in which the salesperson puts data including his/her initials (that field is cboSales). I have Select Case code that assigns BidNumPt3 based on those initials (AfterUpdate on cboSales).
I have a separate query (qryAssignBidNu m) that selects the maximum+1 value of tblBids.BidNumP t2 (a field I named Pt2Next) where BidNumPt1 = Format(Date(), “yy”), grouped by BidNumPt3.
First question: I can’t figure out how to call that field (qryAssignBidNu m.Pt2Next) into the code for my form. The closest I’ve gotten is:
I get an error msg “Variable not defined” on [Pt2Next] (line 9) -- or on [qryAssigBidNum] when I try:
Second question: I also need to add to line 9 the concept of “where qryAssignBidNum .BidNoPt3 = Me!txtBidNoPt3. ”
Or maybe I need to put a parameter in qryAssignBidNum : BidNoPt3 = Forms!frmNewBid s!txtBidNoPt3 ??
I doubt it will recognize the data in the new record before it’s saved, though…
This is all problem #1. Problem #2 will be how to assign BidNoPt2 when a) it’s the first bid of the year, or b) there’s a new salesperson (in other words, when BidNoPt3 has no corresponding data in qryAssignBidNum ). But one thing at a time.
Angi
I’m trying to write code to assign a 3-part identifier to each new record.
The 3 parts are:
BidNumPt1: two-digit current year (BidNumPt1 = Format(Date, “yy”))
BidNumPt2: three-digit incremental number based on the year (Pt1) and the salesperson (Pt3)
BidNumPt3: one-digit code for each salesperson
(In the end, the concatenated bid number looks like 08007.3, for instance.)
I’m trying to figure out how to get the database to insert the next number for BidNumPt2.
I have a form (frmNewBids) based on qryBids, in which the salesperson puts data including his/her initials (that field is cboSales). I have Select Case code that assigns BidNumPt3 based on those initials (AfterUpdate on cboSales).
I have a separate query (qryAssignBidNu m) that selects the maximum+1 value of tblBids.BidNumP t2 (a field I named Pt2Next) where BidNumPt1 = Format(Date(), “yy”), grouped by BidNumPt3.
First question: I can’t figure out how to call that field (qryAssignBidNu m.Pt2Next) into the code for my form. The closest I’ve gotten is:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Me![txtBidNoPt1] = 0 Then
Me![txtBidNoPt1] = CStr(Format(Date, "yy"))
End If
‘BidNoPt2 has to be 3 digits, so I’m converting it to a string, adding two zeros to the beginning, and then taking the rightmost 3 characters.
If Me![txtBidNoPt2] = 0 Then
Me![txtBidNoPt2] = Right(CStr("00" & [Pt2Next]), 3)
End If
End If
End Sub
Code:
Me![BidNoPt2] = Right(CStr("00" & [qryAssignBidNum].[Pt2Next]), 3)
Or maybe I need to put a parameter in qryAssignBidNum : BidNoPt3 = Forms!frmNewBid s!txtBidNoPt3 ??
I doubt it will recognize the data in the new record before it’s saved, though…
This is all problem #1. Problem #2 will be how to assign BidNoPt2 when a) it’s the first bid of the year, or b) there’s a new salesperson (in other words, when BidNoPt3 has no corresponding data in qryAssignBidNum ). But one thing at a time.
Angi
Comment