 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / November 2006Check if current record on form has been saved
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" Here's hoping! >I am trying to increment a job number with the following code > [quoted text clipped - 27 lines] >"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.  SignatureMarsh MVP [MS Access] 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! > >I am trying to increment a job number with the following code > > [quoted text clipped - 48 lines] > would be a lot easier if the table only contained the > number. 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 [quoted text clipped - 67 lines] > > would be a lot easier if the table only contained the > > number. 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 [quoted text clipped - 83 lines] > > > would be a lot easier if the table only contained the > > > number. 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 [quoted text clipped - 87 lines] > > > > would be a lot easier if the table only contained the > > > > number. 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 > You will want the variable to be dimmed at the module level for this to work. > [quoted text clipped - 89 lines] > > > > > would be a lot easier if the table only contained the > > > > > number. 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. > 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 [quoted text clipped - 95 lines] > > > > > > would be a lot easier if the table only contained the > > > > > > number. Thank you very much for your help - that all makes sense to me now. > 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 [quoted text clipped - 122 lines] > > > > > > > would be a lot easier if the table only contained the > > > > > > > number. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.