Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 1st, 2007, 04:31 PM
Friend of Wrox
 
Join Date: Jan 2007
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default Formula

I am trying to Automate the creation of a quote number. I plan on making a form that contains a SalesID ComboBox and a Command button that opens a new form. On Click I want the new form to open a new form and insert the created quote#

Basically the number needs to consist of:

The letter "Q-", "Date", "SalesID" (selected from the drop down menu) and "-#" (sequenced number)

this should give me something like this:

Q-13107BOB-1
Q-13107BOB-2
Etc.

How can I do this?

Could someone provide a name for what I am trying to do so I can look it up on Google?

 
Old February 2nd, 2007, 08:36 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

These are called "Regular Expressions" and MSDN has a whole series of articles on them.

Basically what you do is create a public variable (As String) again in a Module.

Then concatenate the values you want in the string you are passing. So the On Click event of the button you use to open the new form would be this:

Dim dtDate As String
Dim sSalesID As String 'it looks like

dtDate = DatePart("d", Date) & DatePart("m", Date) & Right(DatePart("yyyy", Date), 2)

sSalesID = Me.SalesIDFieldName

'Assume Public variable name is pMyString

pMyString = "Q-" & dtDate & sSalesID & ...

I am not sure where you are getting your number sequence from. Where is that coming from? If you are generating it, what is it based on? Number of quotes that day, or number of quotes ever?

Anyway, now that you have the public variable value, close the first form and open the second.

The second should open to a new record. On the Second form's On Load event, put this:

Me.QUoteNumber = pMyString

HTH


mmcdonal
 
Old February 2nd, 2007, 08:40 AM
Friend of Wrox
 
Join Date: Jan 2007
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

The number sequence is going to based on the SalesID quote for that day.

I just want to make sure what I am looking for is "Regular Expressions" that is placed into a Module.

Correct?

 
Old February 19th, 2007, 12:12 PM
Friend of Wrox
 
Join Date: Jan 2007
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

mmcdonal,

I am reviving this post because I am stuck. I think I may very well have to use a module. I dont know why but I am almost scared to try and use them. In any event I was trying to experiment with another suggestion. I think as you go through this youll see I have no idea what I am doing. But you already knew that!



This is what I have done.

I made a Field on my "Quote" Table, Named

[Increment]

Lookup
=Nz(DMax("[Increment]","Quote","[SelectedDate] = #" & Date() & "#" AND [PKSalesID] = '" & Me.PKSalesID & "'" ),0)+1


I placed the field on my form (Hidden)

I placed an AfterUpdate on my ComboBox - PKSalesID

QuoteNumber = "Q-" & Me.PKSalesID & "-" & Format(Me.Date, "mm/dd/yyyy") & "(" & Me.Increment & ")"


Which Gives me the quote Format
Q-SalesID-Date(0)

Now here are the Problems:
My Increment is not working....
My SalesID is showing as the Autonumber rather than the SalesID

What should happen is if Bob does 2 quotes in the same day the first quote would be

Q-BOB-02/16/2007 (1) For the first Quote of the day for Bob
Q-BOB-02/16/2007 (2) For the second Quote of the day for Bob


Does this make sense? what other information do I need to provide?

More importantly should i just skip this method and use a Module?


 
Old February 20th, 2007, 08:58 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

A module is just the code packaged so that it can be reused easily. You can do this on the form if you want.

You may want to make part of this process manual, and then do some data validation to make sure the process was followed.

For example, it seems like the user will have to select their name from a combo box. You can fire an event when a selection is made, but I would be inclined to have a button called "Generate Quote Number" and have the user do this. I would have the button check for a value in the Quote field first, then generate the quote number. Make the Quote number required at the table level, and if the form is closed or the user tries to move to another record and there is no quote number, the table with throw an error.

In your table, put a DateCreated field as Date/Time, and the date format as mm/dd/yyyy (I think it is short date). Then on your form, on the Before Insert event, put this code:

Me.DateCreated = Date()

Assume the name combo box is cboName, and you have a PK autonumber field in the name table that the cbo is bound to, and Column 1 has the first name.

I am assuming that this is all happening in a table called tblQuote, and that there is a look up for the cboName field in the table called "Salesman."

On the OnClick event of the button, add this code:

If IsNull(Me.QuoteNumber) Or Me.QuoteNumber = "" Then

Dim sString, sSQL As String
Dim rs As ADODB.Recordset
Dim iName, i As Integer
Dim dtDate As Date

dtDate = Date()
If IsNull(Me.cboName) or Me.cboName = "" Then
   MsgBox "Please select a name.", vbCritical
   Exit Sub
Else
   iName = Me.cboName
End If

sSQL = "SELECT * FROM tblQuote WHERE [Salesman] = " & iName & _
       " AND [DateCreated] = #" & dtDate & "#"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

i = 1
If rs.Recordset <> 0 Then
   Do Until rs.EOF
     i = i + 1
   rs.MoveNext
   Loop
End If

rs.Close

sString = "Q-" & Me.cboName.Column(1) & "-" & CStr(dtDate) & " (" & i & ")"

Me.QuoteNumber = sString

Else
   MsgBox "There is already a quote number.", vbCritical
   Exit Sub
End If



This will not have leading 0 in a month like 02, it will be 2 instead. I hope that is okay.

Did that help?





mmcdonal
 
Old February 22nd, 2007, 10:37 AM
Friend of Wrox
 
Join Date: Jan 2007
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

I am trying your code and i am getting a debug error on

If rs.Recordset <> 0 Then

The error is "Compile error: Method or Data Mamber not Found"


What I placed before it is:

Private Sub cmdCreateQuote_Click()
If IsNull(Me.QuoteNumber) Or Me.QuoteNumber = "" Then
    Dim sString, sSql As String
    Dim rs As ADODB.Recordset
    Dim ipkSalesID, i As Integer
    Dim dtDate As Date

dtDate = Date
    If IsNull(QuoteNumber) Or Me.QuoteNumber = "" Then
    MsgBox "Please select your SalesID", vbCritical
    Exit Sub
Else
    ipkSalesID = Me.PKSalesID
End If

sSql = "Select * From tblQuote Where [PKSalesID] = " & ipkSalesID & _
    " AND [Date] = #" & dtDate & "#"
Set rs = New ADODB.Recordset
rs.Open sSql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

i = 1

 
Old February 22nd, 2007, 12:55 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Change this:

i = 1
If rs.Recordset <> 0 Then
   Do Until rs.EOF
     i = i + 1
   rs.MoveNext
   Loop
End If

To this:

i = 1

   Do Until rs.EOF
     i = i + 1
   rs.MoveNext
   Loop

Did that work?


mmcdonal
 
Old February 26th, 2007, 12:41 PM
Friend of Wrox
 
Join Date: Jan 2007
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

I am getting a Debug error again on

Else
   MsgBox "There is already a quote number.", vbCritical
   Exit Sub
End If


Compile Error
"Else without IF"


I also tried to remove it and run it on the form to see if it is working. For some reason even after I choose the PKSalesID and click Create Quote the msgbox for "Choose your SalesiD" is firing off. I think its because the SalesID is actually a FK but displayed as the first 2 letters of the first and last name, do I need to insert an sql statement to refer to the other table named SalesPeople?



 
Old February 26th, 2007, 01:23 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Yikes.

Find out where the If statement is that goes with this section:

Else
   MsgBox "There is already a quote number.", vbCritical
   Exit Sub
End If

Post all the code on the event that is causing the problem.

You may have to refer to Column(1) in your code if you want the displayed value, or make sure you are referring to the bound column properly.

HTH

mmcdonal
 
Old February 26th, 2007, 04:39 PM
Friend of Wrox
 
Join Date: Jan 2007
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

okay so back to the basics for me please. If the Else in the message

Else
MsgBox "There is already a quote number.", vbCritical
Exit Sub
End If

Is Highlighted yellow, how do I find where the IF statement is. I assume it is the statement preceeding it.

Is that correct?


Part2-
If the lookup source for the PKSalesID is:

SELECT [PKSalesID], [SalesID] FROM Salespersons ORDER BY [SalesID];

and the

Column count = 2
Bound Column =1
Column width is =0";2"

I would use the statement:

sSql = "Select * From tblQuote Where [SalesID] = " & ipkSalesID & _
    " AND [Date] = #" & dtDate & "#"
Set rs = New ADODB.Recordset

Does that seem Correct?

or do I need to include this

"SELECT * FROM Salespersons [PKSalesID], [SalesID] ORDER BY [SalesID] Where [PKSalesID] = " & ipkSalesID & _
    " AND [Date] = #" & dtDate & "#"





Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with formula Juan0214 Access 4 August 1st, 2008 07:25 AM
Help with formula 2 Juan0214 Access 3 July 10th, 2008 06:52 PM
formula sinha Crystal Reports 1 October 26th, 2005 12:53 PM
Help with a formula Corey Access 2 October 20th, 2005 10:48 AM
Formula Ned Pro VB 6 2 September 10th, 2003 10:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.