Creating a regular expression

  • Thread starter Antonio Pruett-Saratan II
  • Start date
A

Antonio Pruett-Saratan II

I have been trying to create a Quote # based on 3 boxes on a form for a while
now and havent been able to make it work. This is mostly because I do not
have a foundation or understand some of the fundamentals of VBA.

Fields associated with the problem:

[QuoteID] - PK on the table
[QuoteNumber] - field that stores the quote number, is required,
[Date] - field that is set as Date()
[PKSalesID]- is the a FK on the table and is displayed as [SalesID] from
another table

These fields are all on the same form.

When I press a button called create a cmdCreateQuote it should Populate the
field [QuoteNumber] in the following format

"Q-", [SalesID], [Date], (1)

the (1) represents the salesperson's number of quotes for that day. so if
bob already had one quote the second quote of that day for bob would be (2)

Is that clear I hope so. Now I have been given 2 ways to approach this one
with a Module and one by using the dmax function. both of these ways do not
seem to be working.

I just need an easy way to do this any suggestions?



I am very new to Access and VBA. So I encounter a lot of problems and
rarely understand what I am being told so please explain simply to my
question. Thank you!
 
M

Marshall Barton

Antonio Pruett-Saratan II <Antonio Pruett-Saratan
I have been trying to create a Quote # based on 3 boxes on a form for a while
now and havent been able to make it work. This is mostly because I do not
have a foundation or understand some of the fundamentals of VBA.

Fields associated with the problem:

[QuoteID] - PK on the table
[QuoteNumber] - field that stores the quote number, is required,
[Date] - field that is set as Date()
[PKSalesID]- is the a FK on the table and is displayed as [SalesID] from
another table

These fields are all on the same form.

When I press a button called create a cmdCreateQuote it should Populate the
field [QuoteNumber] in the following format

"Q-", [SalesID], [Date], (1)

the (1) represents the salesperson's number of quotes for that day. so if
bob already had one quote the second quote of that day for bob would be (2)

Is that clear I hope so. Now I have been given 2 ways to approach this one
with a Module and one by using the dmax function. both of these ways do not
seem to be working.

I just need an easy way to do this any suggestions?
Click to expand...


The problem is that you are trying to solve the wrong
problem. The QuoteID field should not have a combination of
other fields AND you do need a DayQNum field for the
salesperson's number of quotes for each date.

With a normalized set of fields, you can easily display the
QuoteID any time you want by using a form or report text box
with an expression like:

="Q-," & SalesID & "," & SaleDate & ",(" & DayQNum & ")"

The DayQNum field can easily be calculated by using DMax in
the form's BeforeUpdate event.

Me.DayQNum = Nz(DMax("DayQNum ", "tblQuotes", _
"SalesID=" & Me.SalesID & " And " & _
"SaleDate=" & Format(Me.SaleDate,"\#m\/d\/yyyy\#)), 0)+1
 
A

Antonio Pruett-Saratan II

Maybe what I am not understanding is how would I search for the record ?

Let me if i understand this correctly because I am slow.
1. I can replace my Quotenumber field on my quote table and place a field
on the table named DayQNum - long integer index ?

2. make the textbox ( which used to be QuoteNumber,) control source
="Q-," & SalesID & "," & SaleDate & ",(" & DayQNum & ")"

3. on the forms BeforeUpdate place
Me.DayQNum = Nz(DMax("DayQNum ", "tblQuotes", _
"SalesID=" & Me.SalesID & " And " & _
"SaleDate=" & Format(Me.SaleDate,"\#m\/d\/yyyy\#)), 0)+1

Correct?


--
I am very new to Access and VBA. So I encounter a lot of problems and
rarely understand what I am being told so please explain simply to my
question. Thank you!


Marshall Barton said:
Antonio Pruett-Saratan II <Antonio Pruett-Saratan
I have been trying to create a Quote # based on 3 boxes on a form for a while
now and havent been able to make it work. This is mostly because I do not
have a foundation or understand some of the fundamentals of VBA.

Fields associated with the problem:

[QuoteID] - PK on the table
[QuoteNumber] - field that stores the quote number, is required,
[Date] - field that is set as Date()
[PKSalesID]- is the a FK on the table and is displayed as [SalesID] from
another table

These fields are all on the same form.

When I press a button called create a cmdCreateQuote it should Populate the
field [QuoteNumber] in the following format

"Q-", [SalesID], [Date], (1)

the (1) represents the salesperson's number of quotes for that day. so if
bob already had one quote the second quote of that day for bob would be (2)

Is that clear I hope so. Now I have been given 2 ways to approach this one
with a Module and one by using the dmax function. both of these ways do not
seem to be working.

I just need an easy way to do this any suggestions?
Click to expand...


The problem is that you are trying to solve the wrong
problem. The QuoteID field should not have a combination of
other fields AND you do need a DayQNum field for the
salesperson's number of quotes for each date.

With a normalized set of fields, you can easily display the
QuoteID any time you want by using a form or report text box
with an expression like:

="Q-," & SalesID & "," & SaleDate & ",(" & DayQNum & ")"

The DayQNum field can easily be calculated by using DMax in
the form's BeforeUpdate event.

Me.DayQNum = Nz(DMax("DayQNum ", "tblQuotes", _
"SalesID=" & Me.SalesID & " And " & _
"SaleDate=" & Format(Me.SaleDate,"\#m\/d\/yyyy\#)), 0)+1
Click to expand...
 
M

Marshall Barton

Antonio said:
Maybe what I am not understanding is how would I search for the record ?

Let me if i understand this correctly because I am slow.
1. I can replace my Quotenumber field on my quote table and place a field
on the table named DayQNum - long integer index ?

2. make the textbox ( which used to be QuoteNumber,) control source
="Q-," & SalesID & "," & SaleDate & ",(" & DayQNum & ")"

3. on the forms BeforeUpdate place
Me.DayQNum = Nz(DMax("DayQNum ", "tblQuotes", _
"SalesID=" & Me.SalesID & " And " & _
"SaleDate=" & Format(Me.SaleDate,"\#m\/d\/yyyy\#)), 0)+1
Click to expand...


You got it!

A long integer field is fine, but for efficiency, the
SalesID and SaleDate fields are the ones that should be
indexed.

Try it out by just adding the DayQNum field to the table.
If you already have data in your Quotenumber field, leave
alone until everything is working as planned.

The BeforeUpdate code goes in the event **procedure**.
Don't confuse that with the event property (that should have
[Event Procedure] in it).
 
A

Antonio Pruett-Saratan II

So this is a big jump for me how would I make it so that I can do a search by
quote number?

Do I just make an unbound form with salesID and date and DayQNum??

Or

I can have one field named [QuoteNum] and make a query that searches for
SalesId Date and DayQNum from table quotes?


I know I am making this more complicated than it really is but I will be
using the Quote# also for Job# that are actually a part of a quote.




--
I am very new to Access and VBA. So I encounter a lot of problems and
rarely understand what I am being told so please explain simply to my
question. Thank you!


Marshall Barton said:
Antonio said:
Maybe what I am not understanding is how would I search for the record ?

Let me if i understand this correctly because I am slow.
1. I can replace my Quotenumber field on my quote table and place a field
on the table named DayQNum - long integer index ?

2. make the textbox ( which used to be QuoteNumber,) control source
="Q-," & SalesID & "," & SaleDate & ",(" & DayQNum & ")"

3. on the forms BeforeUpdate place
Me.DayQNum = Nz(DMax("DayQNum ", "tblQuotes", _
"SalesID=" & Me.SalesID & " And " & _
"SaleDate=" & Format(Me.SaleDate,"\#m\/d\/yyyy\#)), 0)+1
Click to expand...


You got it!

A long integer field is fine, but for efficiency, the
SalesID and SaleDate fields are the ones that should be
indexed.

Try it out by just adding the DayQNum field to the table.
If you already have data in your Quotenumber field, leave
alone until everything is working as planned.

The BeforeUpdate code goes in the event **procedure**.
Don't confuse that with the event property (that should have
[Event Procedure] in it).
Click to expand...
 
M

Marshall Barton

Antonio said:
So this is a big jump for me how would I make it so that I can do a search by
quote number?

Do I just make an unbound form with salesID and date and DayQNum??

Or

I can have one field named [QuoteNum] and make a query that searches for
SalesId Date and DayQNum from table quotes?


I know I am making this more complicated than it really is but I will be
using the Quote# also for Job# that are actually a part of a quote.
Click to expand...


No, you are not making this more complicated than needed.
Data structure is not a simple topic. Take a little time to
do some homework so you are familiar with the fundamentals.
Here's a decent starting point:
http://support.microsoft.com/kb/283878/en-us
Just Google for "Database Normalization" for a million more
detailed explanations ;-)

In case it may help you. I superficially summarize
normalization with the question: When a value is
edited/added, how many things in the database need to be
changed? The answer should always be: one field in one row
in one table and zero forms, reports, or code modules.

The First Normal Form requires that your fields must be
atomic (have a single value). Your quote number (candidate
key) is comprised of three values: SalesId, Date and
DayQNum, so each of those three values needs to be in a
separate field. If you don't follow at least the first
three normal forms, you will end up with complications and
data inconsistencies that will tie your program in knots.
This is why I said that you need to split the quote number
data into separate fields, especially since it is trivial to
put it together for human consumption on a form or report.

As for creating a search capability, use a form with, at
least for now, three unbound text boxes that correspond to
the fields in the quote number.

Once you get that working, I think you will find that your
users will have no trouble using it. However, if you have
untrained folks off the street searching for quotes, then
you may want to let them enter the combined string. You
will then need to add code to parse it into its three
component values for the search operation. This kind of
parsing is relatively straightforward, but, as I said above,
probably unnecessary.
 
Top