VBA to split string then increment

[arnelgp]

try my function and see it is easier with 1 field instead of wasting storage space.

 

[pbaldy]

Since you brought up normalization maybe you should look at atomicity. 1st normal form requires that each cell contains only atomic(indivisible) values.

 

[arnelgp]

Chk your 2 field suggestion.

 

[Galaxiom]

try my function and see it is easier with 1 field instead of wasting storage space.

Consider the very likely requirement to report the most recent revision of all Quotes.
Your single field must first be parsed to extract the QuoteMaster. Group By that then use the Max of the QuoteRevision substring or QuoteDate.
Using your function to do the parsing, all the data must be hauled over to be processed in VBA. Slow and processor intensive.

Using two fields we find the latest set of quotes with Group By the QuoteMaster field and the Max of the QuoteRevision. With indexes and the whole job done in the engine, the results are virtually instant.

Performance is far more important than saving what would probably amount to fractions of a cent in storage cost.

You have always been excessively enamoured with user defined functions because they make the query look tidy. Tidy doesn't mean efficient. You should focus more on getting the engine to do the work..

 

[tmyers]

I stepped away for the evening an entire discussion happened.

First I will say I liked reading everyone's post, as they are informative and offer different views. I currently have two versions of my db. One is using the method of splitting the quote number and then incrementing, while the other broke the numbers into two separate fields. So far, the two separate fields is MUCH easier to work with. It also opened up the ability to possibly do what I was striving for in another thread, and that is to have something like a combo box with the revisions so the user can easily "flip" the form through each revision to view them.

I understand (somewhat) what arnelgp is saying, though the structure seems more complex to me. You were also correct in the assumption that I am a novice. Almost a complete one. I am learning as I go (hence my possibly annoying presence here nearly every day).

 

[tmyers]

I do have a question however regarding the combo box I am now trying to use.
I normally sort everything via JobID, as that is the quotes unique identifier. But now with how I am handling revision using a different field, how can I set the combo box to show all revisions available for a job? When one is made, it has a new JobID. I tried removing that and setting its primary field to QuoteNum since that will no longer change, but the query doesnt return anything.

 

[pbaldy]

I'm not sure I'm understanding, but it sounds like you'd want to filter for quote number:

SELECT Revision
FROM TableName
WHERE QuoteNum = Forms!FormName.QuoteNum

Adjusting for your actual names of course.

 

[tmyers]

I tried that, but it only returns a single revision number and doesnt show any quote number. The SQL that I tried was:
SELECT tblJobDetails.RevisionNum, tblJobDetails.QuoteNum
FROM tblJobDetails
WHERE (((tblJobDetails.QuoteNum)=[Forms]![JobQuote]![QuoteNum]));

Edit:
I went full stupid. My control source was set incorrectly.

 

[tmyers]

One final question then I think this whole debacle is solved.

I am familiar with opening a form to a specific record using vba. I typically use:

docmd.openform "formname", , , "[fieldname]='" & [fieldname] & '", acformedit

I know I can add a second criteria via AND with that code. Does that work with a combo however? Also the form is already open, so openform would be the incorrect command. Should probably be a go to record, which would make much more sense, but I am still unsure how to properly reference the value of the combo box.

Would I just add

AND [fieldname]= me.combobox.value

?

 

[theDBguy]

Also the form is already open, so openform would be the incorrect command.

Actually, you should still be able to use OpenForm to open an already opened form. If you use a criteria, it will apply the new criteria to the already open form. Give it a try...

 

[pbaldy]

Sure, but you'd have to concatenate:

...AND [fieldname]= " & me.combobox.value

You're almost sounding like a cascading combo situation, even if the quote is a textbox:

Cascading Combos

I'm not clear on how you're trying to do this, so if the above doesn't help perhaps you can attach a sample here that shows what you're trying to do?

 

[tmyers]

Concatenate. I always seem to fail at doing that properly.
I looked over that cascading combos and I dont think that is quite what I am after.

After concatenating correctly, it works to a fashion. It causes a parameter window to pop up for the first field (QuoteNum). I think it is due to how the code works. It opens the form again, so quotenum is blank.

 

[pbaldy]

The parameter prompt is Access telling you it can't find something (whatever is in the prompt). What's your actual code? Either something is spelled wrong or the field isn't in the form's source.

 

[tmyers]

DoCmd.OpenForm "JobQuote", , , "[QuoteNum]= '" & [QuoteNum] & "' AND [RevisionNum]= " & Me.RevisionCombobx.Value

 

[pbaldy]

If it works after the prompt, I suspect [QuoteNum] isn't working as you expect. Try

Me.QuoteNum

or whatever the name of the control is that contains the value.

DoCmd.OpenForm "JobQuote", , , "[QuoteNum]= '" & Me.QuoteNum & "' AND [RevisionNum]= " & Me.RevisionCombobx.Value

 

[tmyers]

Tried that and still got the parameter prompt. Funny enough, the prompt displays the correct quotenum, but even after i enter it, it still doesnt open correctly.

Looking at the debugging view, me.revision.combobx.value is reading the quotenum for some reason. That is interesting.

Edit:
Changed which column I was referencing and now it works wonderfully. Simple changed me.revisioncombobx.value to me.revision.combobx.column(1)

 

[pbaldy]

Glad you got it sorted!