Help with creating a Custom Order Number 2

[bjt52]

I need help in creating a unique auto number that will look like this EN-0012345-001
The first component “EN” indicates if it is an Engineering or Sales Order. The second component is auto generated and the third component is based on what day of the week. Ex. 001=Monday, 002=Tuesday, ect.
In the employee table I have a field that indications the department so the EN or SL can be driven from that field. The last component comes from the day of the week and the time of day. If the order is created before 3:00pm on Monday it would be EN-012345-001, but if it is after 3:00pm it would be EN-0012345-002 is the something that can be achieved?
Thanks

 

[Remou]

Perhaps:

[tt]IIf(Time < #3:00:00 PM#, Format(Weekday(Now, vbMonday), "000"), Format(Weekday(Now, vbMonday) + 1, "000"))[/tt]

 

[bjt52]

Remou,
Thanks for the routine that is what I was needing.. I hope that you can offer a little more help.
I can get the 3 routines to work in the Immediate window but when I run the below code it only insert a blank row
any ideals?
Thanks
Dim strOrderID As String
Dim SQL As String
Dim strComp1 As String
Dim strComp2 As String
Dim strComp3 As String

strOrderID = strComp1 & "-" & strComp2 & "-" & strComp3

strComp1 = DLookup("[strEmpDept]", "contact")
strComp2 = DMax("orderid", "order") + 1
strComp3 = IIf(Time < #3:00:00 PM#, Format(Weekday(Now, vbMonday), "000"), Format(Weekday(Now, vbMonday) + 1, "000"))

SQL = "INSERT INTO [Order] ([OrderID]) VALUES ([strOrderID])"
DoCmd.RunSQL SQL

End Sub

 

[Remou]

You have a line misplaced, as far as I can see:

[tt]strComp1 = DLookup("[strEmpDept]", "contact")
strComp2 = DMax("orderid", "order") + 1
strComp3 = IIf(Time < #3:00:00 PM#, Format(Weekday(Now, vbMonday), "000"), Format(Weekday(Now, vbMonday) + 1, "000"))

strOrderID = strComp1 & "-" & strComp2 & "-" & strComp3[/tt]

 

[bjt52]

Ramou,
I am getting a Enter Parameter Value for StrOrderID. I have tried enclose it in "" and ' '.
Aslo I am trying to add Environ("UserName") to
DLookup("[strEmpDept]", "contact","Environ("UserName")") but can seem to get it to work is it even possible to us the Environ in a Dlookup?
Thanks

 

[Remou]

Yes, but not quite like that. Try:

[tt]DLookup("[strEmpDept]", "contact","[EmployeeNameField]='" & Environ("UserName") & "'")[/tt]

 

[bjt52]

THANKS! That did it, I would have never figured that one out. I have searched the web and this forum but had not see any examples like that for DLookup.
Any clue on what is wrong with my INSERT INTO statement or StrOrderID, it only insert a blank record. I have tried adding single quotes, double quotes and () but still no luck..
Thanks

 

[Remou]

Did you try the suggestion in my post dated 2 Nov 06 9:27?

 

[bjt52]

Yes, I place strOrderID = strComp1 & "-" & strComp2 & "-" & strComp3 after the individual string statement.
I still get the Enter parameter for strOrderID and when I change the [] in the INSERT INTO with single quotes in inserts “strOrderID” in the field and then I get an mismatch error..
Thanks

 

[Remou]

Sorry, I wasn't reading you. Try:

[tt]SQL = "INSERT INTO [Order] ([OrderID]) VALUES ( '" &[strOrderID] & "' )"
DoCmd.RunSQL SQL[/tt]

 

[bjt52]

Ok, now i am getting a type mismatch on strComp2 = DMax([orderid], "order") + 1 when it tries to INSERT the next ID.
I would also appreciate if you would explain what the & does in ( '" & [strOrderID] & "' ).
Thanks

 

[Remou]

You will need to convert OrderID to a number, Val should suit.

The & is for concatenating strings:
[tt]strText = "This is " & " a " & " line."[/tt]

You need to put the value of strOrderID into your SQL string, not the name of the variable. If you put
[tt]Debug.Print SQL[/tt]
Into your code after the creation of the SQL string, you will see the effect of both methods of writing the string.

 

[bjt52]

Remou,
This is what i did with the Val function
strComp2 = DMax(Val("orderid"), "order") + 1
I have gone from EN-600114 to EN-1-005 have i put the Val function in the correct place?
Thanks for the suggestion of using Debug.Print, i like that feature..
I am a novice at VBA and really appricate your help. I am taking a course in VB course in Jan so that i will be able to do more with less question.
Thanks

 

[PHV]

[tt]strComp2 = DMax("Val(Mid(orderid & '',4))", "order") + 1[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

I think I may have got that wrong. I think OrderID may contain a letter or such like, in which case Val may or may not work. Try:

[tt]strComp2 = DMax("orderid", "order")
MsgBox strComp[/tt]

The line MsgBox strComp will show you what is being returned by DMax.

 

[bjt52]

It returned nothing

 

[PHV]

bjt52, have you tried my suggestion ?

 

[bjt52]

PHV,
I did not see your suggestion, but it works..
Thank you..
Remou -
Thank you for you help the great tip for using Debug.print

 

[AccessGuruCarl]

Just a thought...

Change
Dim strComp2 As String
to
Dim strComp2 As Integer

Then remove the quotes for this varible in the remaining code.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.