The lower you dim your variables, the better. The problem with module level
or application level varialbes is that if you inadvertently use the same
variable name at a lower level, you may not be using the variable you think
you are. When you address as variable, VBA looks in the procedure variables
first, then the module varialbes, then the application variables. The only
reason to have a variable outside a procedure is if more than one procedure
needs access to that variable.
If you need to allow any procedure within your module access to a variable,
it needs to be dimmed at the module level. Note that all the code within a
form is in the form's module, so if you dim a variable at the top of the
module, just below the Option statements, it will be visible to all the
procedures in the module.
There are some risks as I have described previously, but if you are careful
about when and how you scope your variables, it should not be a real
problem. The reason I suggested the module level for storing the shop is
that if you confine it to a specific procedure, then the user would have to
enter the shop number every time they enter that procedure (this is not
entirely true, but it does get advanced, so we will ingore it for now - this
disclaimer is for those who will jump in to tell me I'm wrong about that).
I would recommend you dim the variable at the form module level and ask for
the shop code in the form load event. This way, the user will only have to
enter it one time and you can use it any where in the form as long as the
form is open.
PeterW said:
Is it a problem to dim the variable at module level - I thought I read
somewhere that this is not a good idea as it might cause problems in some
circumstances. Can I ask why this would be a problem if dimmed at procedure
level?
I really do appreciate your help - I have been struggling with this for ages
Klatuu said:
You will want the variable to be dimmed at the module level for this to work.
:
Thanks thats interesting - I think I could get the user to enter the prefix
of their shop with an input box and set a variableto the value - do you think
that will work?
:
To do this effectively, you will need to know which shop you want to get the
next number for. Since I don't know how you will determine that, I can only
dummy that part up and show you the way to create the next number:
Dim varNextJob As Variant
varNextJob = Nz(DMax("[JobNo]", "MyJobTable", "Left([JobNo],1) = '" &
strShop & "'"))
If IsNull(varNextJob) Then
varJobNo = strShop & "0001"
Else
varJobNo = Left(varJobNo,1) & Format(Clng(Mid(varJobNo,2)) + 1,
"0000")
End If
:
Thanks for the help - As I said I am new to programming so whilst I read a
lot - I tend to 'find' ways of doing things which may not be the best way. I
am still getting to grips with recordsets and presumed as I was incrementing
by one that the last record would be the highest No. It is good to know I
don't need to use a recordset to to do this.
The reason for the B is we identify different shops of ours by a letter so
we have job numbers for each shop such as A6105, B6105 what I was hoping to
do eventually is have a table that stores all the job no's and be able to
pull out just the highest no for a shop adding a job no and increment that by
one. I realise that it would be easier to have a seperate table for each shop
but would this not make it harder to pull all information regarding all jobs
together for management reporting?
Also would it be better in some ways not to do this in access and use visual
basic linked to the tables - does this ultimately give better control. I am
still at the level of designing how we do this so presume this is a good time
to make these decisions.
Grateful for any further advice - as long as it's not give up - your
obviously too stupid to do this!
:
PeterW wrote:
I am trying to increment a job number with the following code
Private Sub BtnAddJob_Click()
'Increment Job No By 1
Dim dbJobs As DAO.Database
Dim rsJobs As DAO.Recordset
Set dbJobs = CurrentDb
Set rsJobs = dbJobs.OpenRecordset("tblJob", dbOpenDynaset)
If rsJobs.RecordCount = 0 Then
Me.JobNo = "B1111"
Else
rsJobs.MoveLast
Dim strLastJobNo As String
strLastJobNo = rsJobs("JobNo")
Debug.Print strLastJobNo
DoCmd.GoToRecord , , acNewRec
Me.JobNo = "B" & Val(Mid(strLastJobNo, 2)) + 1
End If
Debug.Print Me.JobNo
rsJobs.Close
End Sub
The problem I encounter is that if the record has not been saved when the
add record button is pressed again a duplicate record is created. Is there a
way to check if the record has been saved to prompt the user. I am a novice
programmer and grateful for any help. Am I working on the right lines to
achieve this or is there a better way alround. Also I have had to start with
"B1111" as we use the four number format - if I use "B0001" the text box sets
to "B2"
You should be doing this kind of thing in the form's
BeforeInsert event, which only fires when a new record is
about to be saved. If you can not live with that, then
check the form's Dirty property to see if the record needs
to be saved. Set the Dirty propert to False to save the
record:
If Me.Dirty Then Me.Dirty = False
You can start with 0001 if you change the line to:
Me.JobNo = "B" & Format(Val(Mid(strLastJobNo,2))+1,"0000")
Your logic is not robust. The data in your recordset is not
ordered so the MoveLast can move to any record. In most
situations, you can do away with the recordset and just use
the one line:
strLastJobNo = Nz(Dmax("JobNo", "tblJob"), 1)
Why are you storing the "B" in the field? It seems like it
would be a lot easier if the table only contained the
number.
