Create Reference Number

[systemx]

Hi all,

I'm hoping someone can point me in the right direction with a query. I am currently developing a database to capture customer complaints. At the moment - the complaint is assigned an autonumber - which works well enough but I was wandering if it is possible to change this.

I would like to assign a reference number based on the following -

1. Selected field in a drop down box on the form (this would be the channel, eg mail, email, phone, etc)
2. 4 Digit Code associated with the employee (this is stored in a table with the employees name).
3. Date & Time as "ddmmyyhhmmss"

This would generate a reference number something like -

EM1399200406122236

On the form I have cboChannel (part 1)

lstEmployee (listbox) - contains the employee name. the pin code is in tblEmployees, field is "Login_Code" - I guess I would use a DLOOKUP function to return the employee login code based on the name selected in the list box (part 2).

When a button is clicked...I would like to MsgBox the result, write the result to a text box and also create a new record in tblEnquiry with the Primary Key field ("Reference_No") containing the value.

Could anyone offer any advice to start me off?

Thanks in advance

Rob

 

[systemx]

Quite pleased with myself.....I have managed to figure it out...well almost.

At this stage I have access returning a message box value (have not worked on creating a record, writing values to forms, etc - but should be easy enough).

Here is my rather simple code...

Private Sub cmd_Generate_Click()

Dim EmpValue As String
Dim ChValue As String
Dim DtValue As String
Dim TmValue As String
Dim Ans As String

EmpValue = Me.cboConsultant.Value

If Me.cboChannel.Value = "Mail" Then
ChValue = "ML"

'will add code for the other channels here.....

End If

DtValue = Date
TmValue = Time

Ans = ChValue & EmpValue & DtValue & TmValue

MsgBox (Ans)

End Sub

__________

This works to some extent.....could anyone advise how I might be able to tidy it up to remove "/" characters from the date and ":" from the time?

Thank you

Rob

 

[pbaldy]

Look at the Format() function.

 

[systemx]

Thanks Paul.

I have actually taken this on a bit of a tangent having found a more suitable option.

I now only need to return the autonumber for the relevant row as my complaints reference number.

I'm hoping someone can help me with the code below -

Private Sub cmd_Generate_Click()

Dim ChValue As String
Dim RecID As Long
Dim Ans As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= G:\.......\complaints.mdb;"
rs.Open "tblEnquiries", , adOpenKeyset, adLockOptimistic, adCmdTable

With rs
.AddNew
.Fields("Channel") = Me.cboChannel.Value
.Update
End With

If Me.cboChannel.Value = "Mail" Then
ChValue = "ML"
End If

If Me.cboChannel.Value = "Email" Then
ChValue = "EM"
End If

If Me.cboChannel.Value = "Telephone" Then
ChValue = "PH"
End If

If Me.cboChannel.Value = "Fax" Then
ChValue = "FX"
End If

'This is where I'm stuck!
RecID = DLookup("ID", "tblEnquiries", ????)

Ans = ChValue & RecID

Me.txtAns.Value = Ans

rs.Close
Set rs = Nothing

MsgBox "Your complaint number is " & Ans, vbOKOnly, "Record Created"

End Sub

I'm not sure what I need to do once I create a new record to get the autonumber out of the 'ID' field. Can anyone help please?

Cheers

 

[systemx]

Hi,

I am using the following -

ChkDgt = DMax("ID", "tblEnquiries")

To return the most current record...as it is an autonumber I figure this should always work. I'm sure this is'nt the best way though...so if someone can suggest another method I would be greatful.

Thanks

Rob

 

[pbaldy]

Try:

With rs
  .AddNew
  .Fields("Channel") = Me.cboChannel.Value
  ChkDgt = .Fields("ID")
  .Update
End With

 

[Fear Naught]

Why not use a "SELECT CASE" statement instead of multiple "IFs". i am sure that will be more efficient.

To capture the employee ID I would think you can do that by including it in the list box albeit hidden.

HTH

 

[systemx]

Thank you all for the help! I used the function and it worked perfectly for me.

I realise I still have one heck of a lot to learn - in terms of making coding tidier. I still do not have my head around using SQL in vba - I have not got much further than using a DLOOKUP function in my login script - but will get there in time I am sure.

Thanks again for the tips

Rob