Problems adding new record from form / subform
in [Forms]
|
From: Irene_58 on 13 Apr 2010 06:34 I have a table (tPNos) containing Project info. The ProjectNo is a text field because we have sub-projects with descriptive suffixes (e.g. 1400, 1400-01, 1400os). The first 4 digits are always numeric and equate to a BasePno. I also have an auto-number PNumId field, that I use to link to other tables. I have a form based on tPNos, with a sub form on the left that has a list of all the ProjectNos & descriptions. As the user moves up & down the list, I use the OnCurrent event to filter to the appropriate record in the main form – so the form fields fill in correctly. I originally had the sub-form with AllowAdditions enabled, but the users aren't very used to Access and asked for buttons to add new records. 2 flavours - cmdNewPno to create a new record with the Pno based on the next free BaseNo and cmdAddStroke to create a new record based on the selected PNo. I disabled AllowAdditions on the sub-form and created buttons on the main form as requested. The OnClick event of the buttons simply did a Me.Recordset.AddNew and set the text boxes on the main form to the appropriate values. The Save button did a DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70. This seemed to work ok, and has been running like this for some months. However, I recently tried to modify this form so that under certain circumstances of adding a new Project it also creates a record in a separate (quote) table and pre-fills a field in that with the new PNumId. Which is when I realised that my approach isn't working properly. My auto-id is going up in steps of 2. The PNumId I have when moving through the form fields and creating the quote record is one greater than the last auto-num as I expect, but when the save is complete there is no record with this number, but a properly completed record but with an auto-num of plus 2. I did try changing the save to Me.Recordset.Update – but this only made it clearer that I'm dealing with 2 different records, as the update doesn't have the data on my form. Any help greatfully appreciated. Code Extracts: Private Sub cmdNewPno_Click() On Error GoTo ErrHandler Me.Recordset.AddNew txtProjectNo = GetLastPno() + 1 Exit Sub ErrHandler: MsgBox Err.Description End Sub Private Sub cmdSave_Click() On Error GoTo Err_cmdSave_Click DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 Me.sfPnos.SetFocus Exit Sub Err_cmdSave_Click: MsgBox Err.Description End Sub -- Irene
From: BruceM via AccessMonster.com on 13 Apr 2010 09:11 If the second chunk of code is just to force a save you can use: Me.Dirty = False It would help to know what GetLastPno() is. I expect it is a user-derfined function, but what is the actual code? Another thing is that it may be better rather than trying to increment the number in the Click event to use the Default Value property of txtProjectNo in the form's Current event: =GetLastPno() + 1 Without knowing more about GetLastPno there is no way of knowing why you are incrementing by two. Does it happen all the time, or just under the "certain circumstances" you mentioned? Irene_58 wrote: >I have a table (tPNos) containing Project info. The ProjectNo is a text >field because we have sub-projects with descriptive suffixes (e.g. 1400, >1400-01, 1400os). The first 4 digits are always numeric and equate to a >BasePno. I also have an auto-number PNumId field, that I use to link to >other tables. > >I have a form based on tPNos, with a sub form on the left that has a list of >all the ProjectNos & descriptions. As the user moves up & down the list, I >use the OnCurrent event to filter to the appropriate record in the main form >– so the form fields fill in correctly. > >I originally had the sub-form with AllowAdditions enabled, but the users >aren't very used to Access and asked for buttons to add new records. 2 >flavours - cmdNewPno to create a new record with the Pno based on the next >free BaseNo and cmdAddStroke to create a new record based on the selected PNo. > >I disabled AllowAdditions on the sub-form and created buttons on the main >form as requested. The OnClick event of the buttons simply did a >Me.Recordset.AddNew and set the text boxes on the main form to the >appropriate values. The Save button did a DoCmd.DoMenuItem acFormBar, >acRecordsMenu, acSaveRecord, , acMenuVer70. >This seemed to work ok, and has been running like this for some months. > >However, I recently tried to modify this form so that under certain >circumstances of adding a new Project it also creates a record in a separate >(quote) table and pre-fills a field in that with the new PNumId. Which is >when I realised that my approach isn't working properly. My auto-id is going >up in steps of 2. The PNumId I have when moving through the form fields and >creating the quote record is one greater than the last auto-num as I expect, >but when the save is complete there is no record with this number, but a >properly completed record but with an auto-num of plus 2. > >I did try changing the save to Me.Recordset.Update – but this only made it >clearer that I'm dealing with 2 different records, as the update doesn't have >the data on my form. > >Any help greatfully appreciated. > >Code Extracts: > >Private Sub cmdNewPno_Click() >On Error GoTo ErrHandler >Me.Recordset.AddNew >txtProjectNo = GetLastPno() + 1 >Exit Sub >ErrHandler: >MsgBox Err.Description >End Sub > >Private Sub cmdSave_Click() >On Error GoTo Err_cmdSave_Click >DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 >Me.sfPnos.SetFocus >Exit Sub >Err_cmdSave_Click: >MsgBox Err.Description >End Sub > -- Message posted via http://www.accessmonster.com
From: Daryl S on 13 Apr 2010 09:36 Irene - Is txtProjectNo your autonumber field? If so, do not assign it a value - it will be created automatically on the AddNew method. Then you are updating it by one more in your GetLastPno() + 1. -- Daryl S "Irene_58" wrote: > I have a table (tPNos) containing Project info. The ProjectNo is a text > field because we have sub-projects with descriptive suffixes (e.g. 1400, > 1400-01, 1400os). The first 4 digits are always numeric and equate to a > BasePno. I also have an auto-number PNumId field, that I use to link to > other tables. > > I have a form based on tPNos, with a sub form on the left that has a list of > all the ProjectNos & descriptions. As the user moves up & down the list, I > use the OnCurrent event to filter to the appropriate record in the main form > – so the form fields fill in correctly. > > I originally had the sub-form with AllowAdditions enabled, but the users > aren't very used to Access and asked for buttons to add new records. 2 > flavours - cmdNewPno to create a new record with the Pno based on the next > free BaseNo and cmdAddStroke to create a new record based on the selected PNo. > > I disabled AllowAdditions on the sub-form and created buttons on the main > form as requested. The OnClick event of the buttons simply did a > Me.Recordset.AddNew and set the text boxes on the main form to the > appropriate values. The Save button did a DoCmd.DoMenuItem acFormBar, > acRecordsMenu, acSaveRecord, , acMenuVer70. > This seemed to work ok, and has been running like this for some months. > > However, I recently tried to modify this form so that under certain > circumstances of adding a new Project it also creates a record in a separate > (quote) table and pre-fills a field in that with the new PNumId. Which is > when I realised that my approach isn't working properly. My auto-id is going > up in steps of 2. The PNumId I have when moving through the form fields and > creating the quote record is one greater than the last auto-num as I expect, > but when the save is complete there is no record with this number, but a > properly completed record but with an auto-num of plus 2. > > I did try changing the save to Me.Recordset.Update – but this only made it > clearer that I'm dealing with 2 different records, as the update doesn't have > the data on my form. > > Any help greatfully appreciated. > > Code Extracts: > > Private Sub cmdNewPno_Click() > On Error GoTo ErrHandler > Me.Recordset.AddNew > txtProjectNo = GetLastPno() + 1 > Exit Sub > ErrHandler: > MsgBox Err.Description > End Sub > > Private Sub cmdSave_Click() > On Error GoTo Err_cmdSave_Click > DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 > Me.sfPnos.SetFocus > Exit Sub > Err_cmdSave_Click: > MsgBox Err.Description > End Sub > > -- > Irene
From: Irene_58 on 13 Apr 2010 10:56 Thanks to you both for your interest: txtProjectNo is the name of a textbox on the main form linked to the ProjectNo field. My function GetLastPno() opens the tPnos table, finds the highest BaseProject (ignoring everything > 7000 which relates to internal charge codes), closes tPnos and returns the BaseProject - see below. This is then incremented to give the first free project num and set into the textbox. I don't do anything to the auto-num field (PNoId) - it's not displayed on the form or explicitly referenced in my code. All that happens between the AddNew and the Save is the user filling in the rest of the form fields. For info: Public Function GetLastPno() As String On Error GoTo ErrHandle Dim strSQL As String Dim recPno As Recordset strSQL = "SELECT * FROM tPnos WHERE ([Project No] < '7000') ORDER BY [Project No] DESC" Set recPno = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) GetLastPno = recPno![BaseProject] recPno.Close Exit Function ErrHandle: MsgBox Err.Description End Function -- Irene "BruceM via AccessMonster.com" wrote: > If the second chunk of code is just to force a save you can use: > Me.Dirty = False > > It would help to know what GetLastPno() is. I expect it is a user-derfined > function, but what is the actual code? > > Another thing is that it may be better rather than trying to increment the > number in the Click event to use the Default Value property of txtProjectNo > in the form's Current event: > > =GetLastPno() + 1 > > Without knowing more about GetLastPno there is no way of knowing why you are > incrementing by two. Does it happen all the time, or just under the "certain > circumstances" you mentioned? > > > Irene_58 wrote: > >I have a table (tPNos) containing Project info. The ProjectNo is a text > >field because we have sub-projects with descriptive suffixes (e.g. 1400, > >1400-01, 1400os). The first 4 digits are always numeric and equate to a > >BasePno. I also have an auto-number PNumId field, that I use to link to > >other tables. > > > >I have a form based on tPNos, with a sub form on the left that has a list of > >all the ProjectNos & descriptions. As the user moves up & down the list, I > >use the OnCurrent event to filter to the appropriate record in the main form > >– so the form fields fill in correctly. > > > >I originally had the sub-form with AllowAdditions enabled, but the users > >aren't very used to Access and asked for buttons to add new records. 2 > >flavours - cmdNewPno to create a new record with the Pno based on the next > >free BaseNo and cmdAddStroke to create a new record based on the selected PNo. > > > >I disabled AllowAdditions on the sub-form and created buttons on the main > >form as requested. The OnClick event of the buttons simply did a > >Me.Recordset.AddNew and set the text boxes on the main form to the > >appropriate values. The Save button did a DoCmd.DoMenuItem acFormBar, > >acRecordsMenu, acSaveRecord, , acMenuVer70. > >This seemed to work ok, and has been running like this for some months. > > > >However, I recently tried to modify this form so that under certain > >circumstances of adding a new Project it also creates a record in a separate > >(quote) table and pre-fills a field in that with the new PNumId. Which is > >when I realised that my approach isn't working properly. My auto-id is going > >up in steps of 2. The PNumId I have when moving through the form fields and > >creating the quote record is one greater than the last auto-num as I expect, > >but when the save is complete there is no record with this number, but a > >properly completed record but with an auto-num of plus 2. > > > >I did try changing the save to Me.Recordset.Update – but this only made it > >clearer that I'm dealing with 2 different records, as the update doesn't have > >the data on my form. > > > >Any help greatfully appreciated. > > > >Code Extracts: > > > >Private Sub cmdNewPno_Click() > >On Error GoTo ErrHandler > >Me.Recordset.AddNew > >txtProjectNo = GetLastPno() + 1 > >Exit Sub > >ErrHandler: > >MsgBox Err.Description > >End Sub > > > >Private Sub cmdSave_Click() > >On Error GoTo Err_cmdSave_Click > >DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 > >Me.sfPnos.SetFocus > >Exit Sub > >Err_cmdSave_Click: > >MsgBox Err.Description > >End Sub > > > > -- > Message posted via http://www.accessmonster.com > > . >
From: John W. Vinson on 13 Apr 2010 14:40 On Tue, 13 Apr 2010 03:34:01 -0700, Irene_58 <Irene58(a)discussions.microsoft.com> wrote: >I have a table (tPNos) containing Project info. The ProjectNo is a text >field because we have sub-projects with descriptive suffixes (e.g. 1400, >1400-01, 1400os). The first 4 digits are always numeric and equate to a >BasePno. I also have an auto-number PNumId field, that I use to link to >other tables. This whole exercise would be a lot easier if you had this field properly normalized. Fields should be "Atomic" - contain only one indivisible piece of information. Yours contains two - a BasePno and a suffix. Can you consider having a BasePno field and a Suffix field? That would let you have a table of BasePnos, which would be simple and numeric, related one to many to this table. You can always concatenate the BasePno field to the Suffix field to display the combination, and you can (and probably should) also define a unique two-field index on the combination. -- John W. Vinson [MVP]
|
Next
|
Last
Pages: 1 2 Prev: rename csv file by removing date/time stamp before importing Next: Tickbox Update |