Updating a textbox

[marcfl]

Hi,

I have an orders form on which my company will be placing orders we recieve. We have two real life sites where these orders are placed: Gatwick and Woking.

I created a combo box called JobLocation which has these two locations as options. As we have two sites we have two different Job Number formats: WWO-0218 for Woking and GWO-0403 for Gatwick.
I want a selection in the JobLocation Combobox to find the last Job Number used and add 1 to the end. For example.

Lets say the last Order placed had a Job Number of GWO-0403 the next time an order was placed for Gatwick GWO-0404 would automatically appear in the JobNo text box.

Ive been trying to do this for the past week everyday at work but i cant find the solution. The furthest ive got is this:

Private Sub JobLocation_AfterUpdate()

If Me.JobLocation = "Gatwick" Then

Me.JobNo = "GWO-"
Else
Me.JobNo = "WWO-"
End If

End Sub

All i need now is to get the actual numbers to appear!

Any ideas?

Cheers.

 

[Remou]

You can use DMax, but there are dangers in this method of getting unique numbers in a multi-user system:

[tt]NewNo=Val(DMax("Mid(ID,Instr(id,"-")+1)","tblT","Left(ID,3)='" & LocationCode & "'"))+1[/tt]

The above is typed, not tested.

 

[marcfl]

i added it, but it didnt work :s seeing as my knowledge of vb is pretty bad ill try to find another way around it i guess.

Thanks for the quick reply

 

[Remou]

Here is a tested version:

[tt]NewNo=Val(DMax("Mid(JobNo,Instr(JobNo,'-')+1)","InsertNameOfTableHere","Left(JobNo,3)='" & Me.JobLocation & "'"))+1[/tt]

If it does not work, please say how, with error numbers and messages, if possible.

 

[marcfl]

I get an error message saying: compile error variable not defined, when i attempt to use it.

With " NewNo = " as the highlighted text.

 

[Remou]

Add Dim NewNo to the beginning of the procedure. This is something you will need to know for most VBA.

'This line means you must declare variables,
'it is important and will help you avoid mistakes.
'Access puts the line in by default, for the most part.
Option Explicit

Sub DoThis()
'Declare variable
Dim NewNo
'It is also best to say what type.
Dim AnotherNumber As Long 'Long integer

NewNo=21
AnotherNumber =123456

End Sub

 

[marcfl]

k usin this:

Private Sub JobLocation_AfterUpdate()

If Me.JobLocation = "Gatwick" Then

Me.JobNo = "GWO-"
Else
Me.JobNo = "WWO-"
End If

Dim NewNo

NewNo = Val(DMax("Mid(JobNo,Instr(JobNo,'-')+1)", "Orders", "Left(JobNo,3)='" & Me.JobLocation & "'")) + 1

End Sub

i get the following error:

run-time error '94'
invalid use of null
the NewNo line is highlighted in yellow.

Sorry bout my uselessness

 

[Remou]

It should look like this:

Private Sub JobLocation_AfterUpdate()
Dim NewNo As Long

'Left(JobNo,3) is looking for, say, GWO in the control 
'Me.JobLocation

   NewNo = Val(DMax("Mid(JobNo,Instr(JobNo,'-')+1)", _
   "Orders", "Left(JobNo,3)='" & Me.JobLocation & "'")) + 1

'Do your checking here, and if all is well ...

   Me.JobNo = NewNo

End Sub

Welcome to Tek-Tips. It is a good idea to read the FAQs.

 

[marcfl]

Nice one thanks it works! saved me ALOT of hassle Im slowly getting the hang of vba..