Hi,

Im trying to get one of combo boxes to update a field automatically.. There are two choices in this combo box "Gatwick" and "Woking" what i need is when one is selected the JobNo field will update itself to GWO-(the first Unused JobNo) Ive used some VBA to make atleast the GWO- or WWO- appear and it works, but i have no clue as to how to get the next unused order number to appear after it.

This is what i have so far:

Private Sub JobLocation_AfterUpdate()

If Me.JobLocation = "Gatwick" Then

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

End Sub

An Example of what i want would be:

GWO-0406
GWO-0407
GWO-0408
and
WWO-0624
WWO-0625
WWO-0626

Any help would be greatly appreciated.

Cheers

Marc.

This is a dicey issue depending on how many users are in the application at one time. Normally you would do this:

Get the value first:

iJobNo = "SELECT Top 1 myTable.JobNo FROM myTable WHERE [JobLocation] = '" & Me.JobLocation & "' ORDER By JobNo DESC"

Me.JobNo = "GW0-" & iJobNo + 1

I might put this on the Before Insert event, but not on the combo's After Update event since if a user opens an existing record and flips the combo by mistake, then you will get a new job number. Unless you had a new job data entry form that had some other controls to prevent this from happening or being opened to view existing records. The Before Insert event happens once per record without controls, but the JobNo is not generally available until after the record is created and navigated from.

Did any of that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

Yeah thanks alot mate that sorted it out!

Cheers.

Oooh, also, you could put a button on the form that says to generate job no, but it is only enabled if the JobNo field is empty.

In any event, you should disabled the JobNo field so users can't chenge it or create their own.


mmcdonal

Look it up at: http://wrox.books24x7.com