Referencing a query in VB

[angi35]

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 (qryAssignBidNum) that selects the maximum+1 value of tblBids.BidNumPt2 (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 (qryAssignBidNum.Pt2Next) into the code for my form. The closest I’ve gotten is:

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

I get an error msg “Variable not defined” on [Pt2Next] (line 9) -- or on [qryAssigBidNum] when I try:

Me![BidNoPt2] = Right(CStr("00" & [qryAssignBidNum].[Pt2Next]), 3)

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!frmNewBids!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

[ADezii]

How about showing some sample data and corresponding Fields and Tables, and what the actual results would look like. It would then be much easier to assist you in arriving at a solution.

[angi35]

Certainly. Let's see if this helps:

In tblBids:
[Sales] [BidNumPt1] [BidNumPt2] [BidNumPt3]
AB 08 001 1
DG 08 001 2
NM 08 001 3
AB 08 002 1
DG 08 002 2
NM 08 002 3
DG 08 003 2
DG 08 004 2
NM 08 003 3

(sorry - I don't know if there's a way to get the columns to line up with their headers here, but I think you can get the idea anyway)

[Sales] = salesperson’s initials
[BidNumPt1] = 2-digit year of record creation; set as text field so that the 0 will appear at the beginning.
[BidNumPt2] = 3-digit increment; also set as text field because of the 0s on the left.
[BidNumPt3] = 1-digit code for the salesperson handling the bid; set as numeric field because I saw no reason to make it a text field.

The BidNum fields get concatenated in a view in forms and reports, such as: 08001.1; 08001.2; 08004.2

So each salesperson has his/her own series of numbers, distinguished by [BidNumPt3].

[BidNumPt2] starts over at 001 every year (for each salesperson/[BidNumPt3]).

When creating a new record in frmNewBids, the user selects/enters their initials in cboSales. This generates [BidNumPt3]. So, for instance, salesperson Doogie Goodman goes to a new record and inputs his initials DG, and then (as if by magic) [BidNumPt3] becomes "2".

[BidNumPt1] always equals the current two-digit year, so that's easy enough to set as a default value. (I've just figured this out and took out the If/Then code for control [txtBidNoPt1] on If Me.New Record... that you see in my earlier post.)

I've set the default value of [BidNumPt2] as 0 (it wouldn't accept 000) so that I can say
If Me.NewRecord Then
If Me![txtBidNoPt2] = 0 Then
...

I hope that helps. What more can I add?

Angi

[ADezii]

Certainly. Let's see if this helps:

In tblBids:
[Sales] [BidNumPt1] [BidNumPt2] [BidNumPt3]
AB 08 001 1
DG 08 001 2
NM 08 001 3
AB 08 002 1
DG 08 002 2
NM 08 002 3
DG 08 003 2
DG 08 004 2
NM 08 003 3

(sorry - I don't know if there's a way to get the columns to line up with their headers here, but I think you can get the idea anyway)

[Sales] = salesperson’s initials
[BidNumPt1] = 2-digit year of record creation; set as text field so that the 0 will appear at the beginning.
[BidNumPt2] = 3-digit increment; also set as text field because of the 0s on the left.
[BidNumPt3] = 1-digit code for the salesperson handling the bid; set as numeric field because I saw no reason to make it a text field.

The BidNum fields get concatenated in a view in forms and reports, such as: 08001.1; 08001.2; 08004.2

So each salesperson has his/her own series of numbers, distinguished by [BidNumPt3].

[BidNumPt2] starts over at 001 every year (for each salesperson/[BidNumPt3]).

When creating a new record in frmNewBids, the user selects/enters their initials in cboSales. This generates [BidNumPt3]. So, for instance, salesperson Doogie Goodman goes to a new record and inputs his initials DG, and then (as if by magic) [BidNumPt3] becomes "2".

[BidNumPt1] always equals the current two-digit year, so that's easy enough to set as a default value. (I've just figured this out and took out the If/Then code for control [txtBidNoPt1] on If Me.New Record... that you see in my earlier post.)

I've set the default value of [BidNumPt2] as 0 (it wouldn't accept 000) so that I can say
If Me.NewRecord Then
If Me![txtBidNoPt2] = 0 Then
...

I hope that helps. What more can I add?

Angi

Hello Angi, download my Test Database that I created for this Thread, and hopefully it will point you in the right direction. To post the code as well as relevant info would be confusing to say the least. Good luck and let me know how you make out.

[angi35]

Hello Angi, download my Test Database that I created for this Thread, and hopefully it will point you in the right direction. To post the code as well as relevant info would be confusing to say the least. Good luck and let me know how you make out.

Thanks ADezii! This is really helpful.

Angi

[ADezii]

Thanks ADezii! This is really helpful.

Angi

Glad we could help ya!

[angi35]

I have some follow-up questions and I want to attach the test database again, but I can't figure out how. Do I need some kind of permission from someone to attach a file?

[ADezii]

I have some follow-up questions and I want to attach the test database again, but I can't figure out how. Do I need some kind of permission from someone to attach a file?

Hello angi35. I'm going to send you my Private E-Mail Address in a Private Message. Send me an E-Mail with the Database in question attached, as well as any questions you may have. Please be as specific as possible as far as the questions and desired results are concerned. I'll get to the DB as soon as I get a chance.

[ADezii]

I have some follow-up questions and I want to attach the test database again, but I can't figure out how. Do I need some kind of permission from someone to attach a file?

Hello Angi, I'll be very busy for the next couple of days, but I did manage to make some changes to your DB. Download the Attachment, have a look, then get back to me. From now on in, let's keep all discussions in the Access Forum. Thanks.