Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Question
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Question
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Question
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.Ma x( _ 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Question
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Question
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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database Question? | Excel Discussion (Misc queries) | |||
Database question | Excel Programming | |||
Users database question | Excel Discussion (Misc queries) | |||
Database Question | Excel Programming | |||
Database question | Excel Programming |