Database Question

I have a database that tracks Quotes. On a userform (that reads/writes to
the database) i have a button that inserts the quote number.

if the user is quoting a new job, i search the 'Quote Numbe' column for the
max, then add 1 to it for the new job number. This works fine.

the problem is ifI the user is Re-Quoting a job, i just want to add 0.1 to
that Quote Number from the Originial time it was quoted.

Example:
if a job was quoted once, and received a Quote Number 5000. If the user
wants to quote that job again, i want the quote number to be 5000.1
then if he wanted to quote it a third time, it would be 5000.2

I just need a way to look for the project name (ComboBoxProjectName.Value)
in column A of the database, then add 0.1 to the largest number for that
project.

Hi Steve,

What type of database is it? Also, what is the name of the table and the
name/data type of the quote number column? Are you connecting through ADO
over OLEDB?

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

steve wrote:
> I have a database that tracks Quotes. On a userform (that
> reads/writes to the database) i have a button that inserts the quote
> number.
>
> if the user is quoting a new job, i search the 'Quote Numbe' column
> for the max, then add 1 to it for the new job number. This works
> fine.
>
> the problem is ifI the user is Re-Quoting a job, i just want to add
> 0.1 to that Quote Number from the Originial time it was quoted.
>
> Example:
> if a job was quoted once, and received a Quote Number 5000. If the
> user wants to quote that job again, i want the quote number to be
> 5000.1
> then if he wanted to quote it a third time, it would be 5000.2
>
> I just need a way to look for the project name
> (ComboBoxProjectName.Value) in column A of the database, then add 0.1
> to the largest number for that project.

actually, i created a database in excel. eventually, i want to use ADO, but
for now, i just open the database, when i need to read/write. it's a short
term solution


"Jake Marx" wrote:

> Hi Steve,
>
> What type of database is it? Also, what is the name of the table and the
> name/data type of the quote number column? Are you connecting through ADO
> over OLEDB?
>
> --
> Regards,
>
> Jake Marx
> www.longhead.com
>
>
> [please keep replies in the newsgroup - email address unmonitored]
>
> steve wrote:
> > I have a database that tracks Quotes. On a userform (that
> > reads/writes to the database) i have a button that inserts the quote
> > number.
> >
> > if the user is quoting a new job, i search the 'Quote Numbe' column
> > for the max, then add 1 to it for the new job number. This works
> > fine.
> >
> > the problem is ifI the user is Re-Quoting a job, i just want to add
> > 0.1 to that Quote Number from the Originial time it was quoted.
> >
> > Example:
> > if a job was quoted once, and received a Quote Number 5000. If the
> > user wants to quote that job again, i want the quote number to be
> > 5000.1
> > then if he wanted to quote it a third time, it would be 5000.2
> >
> > I just need a way to look for the project name
> > (ComboBoxProjectName.Value) in column A of the database, then add 0.1
> > to the largest number for that project.
>
>
>

Hi Steve,

You could use an array function to get this value pretty easily, but putting
it in VBA and making it robust would take some more time.

Here's some code that should help get you started. It looks at a table in
Sheet1 and assumes col A is the project name and col B is the quote number
(change sheet name and columns as needed). And the temporary working cell
for the array formula is J1, so you will want to change that to a cell that
won't have any data in it. Once you get it set up, try a few different
project names for sProjectName, and it should work as expected.

Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim sProjectName As String
Dim sngMax As Single
Dim sngNew As Single

sProjectName = "TestProject"

With Workheets("Sheet1")
Set rng1 = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
Set rng2 = rng1.Offset(0, 1)
.Cells(1, 10).FormulaArray = "=MAX((" & rng1.Address & _
"=""" & sProjectName & """)*(" & rng2.Address & "))"
sngMax = .Cells(1, 10).Value
If sngMax = 0 Then
sngNew = Application.Floor(Application.WorksheetFunction.Max( _
rng2), 1) + 1
Else
sngNew = sngMax + 0.1
End If
.Cells(1, 10).ClearContents
End With

MsgBox "New number for '" & sProjectName & "': " & CStr(sngNew)

Set rng1 = Nothing
Set rng2 = Nothing
End Sub

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]


steve wrote:
> actually, i created a database in excel. eventually, i want to use
> ADO, but for now, i just open the database, when i need to
> read/write. it's a short term solution
>
>
> "Jake Marx" wrote:
>
>> Hi Steve,
>>
>> What type of database is it? Also, what is the name of the table
>> and the name/data type of the quote number column? Are you
>> connecting through ADO over OLEDB?
>>
>> --
>> Regards,
>>
>> Jake Marx
>> www.longhead.com
>>
>>
>> [please keep replies in the newsgroup - email address unmonitored]
>>
>> steve wrote:
>>> I have a database that tracks Quotes. On a userform (that
>>> reads/writes to the database) i have a button that inserts the
>>> quote number.
>>>
>>> if the user is quoting a new job, i search the 'Quote Numbe' column
>>> for the max, then add 1 to it for the new job number. This works
>>> fine.
>>>
>>> the problem is ifI the user is Re-Quoting a job, i just want to add
>>> 0.1 to that Quote Number from the Originial time it was quoted.
>>>
>>> Example:
>>> if a job was quoted once, and received a Quote Number 5000. If the
>>> user wants to quote that job again, i want the quote number to be
>>> 5000.1
>>> then if he wanted to quote it a third time, it would be 5000.2
>>>
>>> I just need a way to look for the project name
>>> (ComboBoxProjectName.Value) in column A of the database, then add
>>> 0.1 to the largest number for that project.

Thanks for the code!

Jake, before I implement this, I am already seeing a problem. if it is a
new project, it will find the max quote number, then add 1 to it. if the max
quote number is say, 5009.3, then the new quote number would be 5010.3.

I have decided that this is probably not the best numbering scheme. for
that reason, i am going to keep the same quote number for the same job, but
use a seperate column as 'Rev Number'. i should be able to alter your code
to accomplish this.

Thanks,
Steve

"steve" wrote:

> I have a database that tracks Quotes. On a userform (that reads/writes to
> the database) i have a button that inserts the quote number.
>
> if the user is quoting a new job, i search the 'Quote Numbe' column for the
> max, then add 1 to it for the new job number. This works fine.
>
> the problem is ifI the user is Re-Quoting a job, i just want to add 0.1 to
> that Quote Number from the Originial time it was quoted.
>
> Example:
> if a job was quoted once, and received a Quote Number 5000. If the user
> wants to quote that job again, i want the quote number to be 5000.1
> then if he wanted to quote it a third time, it would be 5000.2
>
> I just need a way to look for the project name (ComboBoxProjectName.Value)
> in column A of the database, then add 0.1 to the largest number for that
> project.
>
>

Hi Steve,

steve wrote:
> Jake, before I implement this, I am already seeing a problem. if it
> is a new project, it will find the max quote number, then add 1 to
> it. if the max quote number is say, 5009.3, then the new quote
> number would be 5010.3.
>
> I have decided that this is probably not the best numbering scheme.
> for that reason, i am going to keep the same quote number for the
> same job, but use a seperate column as 'Rev Number'. i should be
> able to alter your code to accomplish this.

That's what I would recommend anyway. Keeps things separate and easier to
manage. Good luck!

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]