If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Remember I am very much the amateur.
I followed last and made changes. Does the bolAdd module name matter? It fails to compile, variable not defined at "bolAdd=true". Make sure I followed you on new code residence: The following should be in the NotInListCode of [frmEmployeeSetupNewID] ? (the form I had only alluded to earlier and not named for you; sole purpose is house cbo and get new ID.) "So, for the combo box we got: Dim rst As DAO.Recordset Dim lngNewID As Long If MsgBox("Do you want to add this value to the list?", _ vbYesNo) = vbYes Then Set rst = CurrentDb.OpenRecordset("contacts") rst.AddNew rst!LastName = NewData lngNewID = rst!ContactID rst.Update rst.Close Set rst = Nothing DoCmd.OpenForm "ContactsE", , , "ContactID = " & lngNewID, , acDialog Response = acDataErrAdded bolAdd = True Else Response = acDataErrContinue End If" The following should be in the AfterUpdate event of that same combo box? The only other combo box for employee ID is on [frmEmployee] and has code in its after update for selection of record to view. There is no combo box on [frmEmployeeSetup] as that's a data entry form only and the ID was assigned by [frmEmployeeSetupNewID]. At least it was a week ago and as I am presently attempting to do. " If bolAdd = True Then bolAdd = False Me.Requery End If Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo39], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark" I generally follow the logic, but lack the knowledge to see it completely. Am I totally missing the boat here? -- Thanks for your help, Chris "Albert D.Kallal" wrote: But, now what do I do to get frmEmployeeSetup to test for a unique EmployeeID and add the record? Well, we need to get the combo box working for the above to work correctly. Remember, in those examples I gave you, can REMOVE ALL OF the code you had in the close event...you simply don't need it anymore... So, lets just use the 2nd example where we create the new record in code So, for the combo box we got: Dim rst As DAO.Recordset Dim lngNewID As Long If MsgBox("Do you want to add this value to the list?", _ vbYesNo) = vbYes Then Set rst = CurrentDb.OpenRecordset("contacts") rst.AddNew rst!LastName = NewData lngNewID = rst!ContactID rst.Update rst.Close Set rst = Nothing DoCmd.OpenForm "ContactsE", , , "ContactID = " & lngNewID, , acDialog Response = acDataErrAdded bolAdd = True Else Response = acDataErrContinue End If You can post for me what you got for the above.... And, for the combo box after update event that moves to the correct record, we got: If bolAdd = True Then bolAdd = False Me.Requery End If Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo39], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark You DO NOT need any more code in the frmEmployeeSetup You DO NOT need to pass any parmaters to frmEmployeeSetup You do NOT need any code in the close event of frmEmployeeSetup So, in fact, you can simply modify the combo box code to add the new reocrd, and grab the new id And, you simply add the "bolAdd" condtion code in the combo box after udpate event to requery the form if needed..... Try the above, and post your code for the combo box not in list event, and post the code you got for that combo box after udpate event... In my example above code, I am obviously using a auto for the key id. It is not clear if you are using a auotnumber, or in fact supply the contact id your self... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#12
|
|||
|
|||
And I did create the module "modBolAdd".
Option Compare Database Option Explicit Dim bolAdd As Boolean -- Thanks for your help, Chris "Albert D.Kallal" wrote: But, now what do I do to get frmEmployeeSetup to test for a unique EmployeeID and add the record? Well, we need to get the combo box working for the above to work correctly. Remember, in those examples I gave you, can REMOVE ALL OF the code you had in the close event...you simply don't need it anymore... So, lets just use the 2nd example where we create the new record in code So, for the combo box we got: Dim rst As DAO.Recordset Dim lngNewID As Long If MsgBox("Do you want to add this value to the list?", _ vbYesNo) = vbYes Then Set rst = CurrentDb.OpenRecordset("contacts") rst.AddNew rst!LastName = NewData lngNewID = rst!ContactID rst.Update rst.Close Set rst = Nothing DoCmd.OpenForm "ContactsE", , , "ContactID = " & lngNewID, , acDialog Response = acDataErrAdded bolAdd = True Else Response = acDataErrContinue End If You can post for me what you got for the above.... And, for the combo box after update event that moves to the correct record, we got: If bolAdd = True Then bolAdd = False Me.Requery End If Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo39], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark You DO NOT need any more code in the frmEmployeeSetup You DO NOT need to pass any parmaters to frmEmployeeSetup You do NOT need any code in the close event of frmEmployeeSetup So, in fact, you can simply modify the combo box code to add the new reocrd, and grab the new id And, you simply add the "bolAdd" condtion code in the combo box after udpate event to requery the form if needed..... Try the above, and post your code for the combo box not in list event, and post the code you got for that combo box after udpate event... In my example above code, I am obviously using a auto for the key id. It is not clear if you are using a auotnumber, or in fact supply the contact id your self... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#13
|
|||
|
|||
Does the bolAdd module name matter? It fails to compile, variable not
defined at "bolAdd=true". You don't need to define a new module. Since you are running the after update code in the combo box, then the obvious place to define this variable is in that forms module. So, simply define the bolAdd variable at the START of the forms module. There is not need to create a whole new module to "just" allow us to define that one variable. That variable is used by code in the ONE form, and thus it makes sense to define that variable in that forms module. The start of your forms module code should thus look like: Option Compare Database Option Explicit Dim bolAdd As Boolean If you were to scroll down further in the module, then you would start to see your not in list code, and also the comb's after update code. So, it kind makes sense to put (define) the variable in the same code module that it is going to be used. Note that *could* create a whole new separate module, but then that would imply a design where other forms, and other code would need to use that variable bolAdd. So, since we are using the variable in the code in the "form" module, then we simply define this variable in that forms code module. It is kind of nice feature that each new form you make also creates a "form" module for code from that form. This natural grouping of code means that all code (and our variables) is likely to be found in the one code module for that form. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#14
|
|||
|
|||
I think I'm on the road to recovery and some (minor) comprehension.
EmployeeID is a text field and I think that is why it now fails at " IngNewID = rst!EmployeeID". The user assigns the first three letters of the last name followed by three numeric, incrementing the numeric portion by one. Ex: SMI105, SMI106 and prior exist already so the next SMI would be assigned 107 by the user, inputting SMI107, which isn't in the list. I still appreciate you handholding. -- Thanks for your help, Chris "Albert D.Kallal" wrote: Does the bolAdd module name matter? It fails to compile, variable not defined at "bolAdd=true". You don't need to define a new module. Since you are running the after update code in the combo box, then the obvious place to define this variable is in that forms module. So, simply define the bolAdd variable at the START of the forms module. There is not need to create a whole new module to "just" allow us to define that one variable. That variable is used by code in the ONE form, and thus it makes sense to define that variable in that forms module. The start of your forms module code should thus look like: Option Compare Database Option Explicit Dim bolAdd As Boolean If you were to scroll down further in the module, then you would start to see your not in list code, and also the comb's after update code. So, it kind makes sense to put (define) the variable in the same code module that it is going to be used. Note that *could* create a whole new separate module, but then that would imply a design where other forms, and other code would need to use that variable bolAdd. So, since we are using the variable in the code in the "form" module, then we simply define this variable in that forms code module. It is kind of nice feature that each new form you make also creates a "form" module for code from that form. This natural grouping of code means that all code (and our variables) is likely to be found in the one code module for that form. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#15
|
|||
|
|||
EmployeeID is a text field and I think that is why it now fails at "
IngNewID = rst!EmployeeID". Excellent observation on your part. My code assumed a "auto" id. So, you got a situation where you combo box retuns a value like SMI105...etc... So, we need to change things a bit to reflect this Dim rst As DAO.Recordset If MsgBox("Do you want to add this value to the list?", _ vbYesNo) = vbYes Then Set rst = CurrentDb.OpenRecordset("contacts") ' in the above, you would of course replace "contacts" with the actual name of the table that you ' used rst.AddNew rst!EmployeeID = NewData rst.Update rst.Close Set rst = Nothing DoCmd.OpenForm "ContactsE", , , "EmployeeID = '" & NewData & "'", , acDialog ' in the above, you of course replace "ContactsE"with your frmEmployeeSetup ' note how I now have surrounded the text value with single quotes ' note how we don't need lngID anymore Response = acDataErrAdded bolAdd = True Else Response = acDataErrContinue End If Likely, your comb box "after update" code that worked before to "move" to the record can remain as you had it. We still need the If bolAdd = True Then bolAdd = False Me.Requery End If ...........your combo code to "move" to the reocrd would follow he -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#16
|
|||
|
|||
Close form, skips calling form focus
There is a good record created.
However... 1. the new ID is not appearing in [EmployeeID] field on [frmEmployeeSetup] (previously handled via open args) so none of the new employee entry data is saved. There is a field on that form -- [EmployeeID]. 2. the [frmEmployeeSetupNewID] form does not close at [frmEmployeeSetup] opening. I cannot find where to place DoCmd close in the after update code. 3. the newly created record does not appear on [frmEmployee] -- that's why I started this. 4. although the new record has only an ID, when I go to [frmEmployee] that is no longer available as no requery of that combo box. Where to now? -- Thanks for your help, Chris "Albert D.Kallal" wrote: EmployeeID is a text field and I think that is why it now fails at " IngNewID = rst!EmployeeID". Excellent observation on your part. My code assumed a "auto" id. So, you got a situation where you combo box retuns a value like SMI105...etc... So, we need to change things a bit to reflect this Dim rst As DAO.Recordset If MsgBox("Do you want to add this value to the list?", _ vbYesNo) = vbYes Then Set rst = CurrentDb.OpenRecordset("contacts") ' in the above, you would of course replace "contacts" with the actual name of the table that you ' used rst.AddNew rst!EmployeeID = NewData rst.Update rst.Close Set rst = Nothing DoCmd.OpenForm "ContactsE", , , "EmployeeID = '" & NewData & "'", , acDialog ' in the above, you of course replace "ContactsE"with your frmEmployeeSetup ' note how I now have surrounded the text value with single quotes ' note how we don't need lngID anymore Response = acDataErrAdded bolAdd = True Else Response = acDataErrContinue End If Likely, your comb box "after update" code that worked before to "move" to the record can remain as you had it. We still need the If bolAdd = True Then bolAdd = False Me.Requery End If ...........your combo code to "move" to the reocrd would follow he -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#17
|
|||
|
|||
Close form, skips calling form focus
OK...I figured out to change [frmEmployeeSetup] to NOT DataEntry. So, I have
the record ok. Still, I'm not bringing the new record up on [frmEmployee]. Please help! -- Thanks for your help, Chris "Albert D.Kallal" wrote: EmployeeID is a text field and I think that is why it now fails at " IngNewID = rst!EmployeeID". Excellent observation on your part. My code assumed a "auto" id. So, you got a situation where you combo box retuns a value like SMI105...etc... So, we need to change things a bit to reflect this Dim rst As DAO.Recordset If MsgBox("Do you want to add this value to the list?", _ vbYesNo) = vbYes Then Set rst = CurrentDb.OpenRecordset("contacts") ' in the above, you would of course replace "contacts" with the actual name of the table that you ' used rst.AddNew rst!EmployeeID = NewData rst.Update rst.Close Set rst = Nothing DoCmd.OpenForm "ContactsE", , , "EmployeeID = '" & NewData & "'", , acDialog ' in the above, you of course replace "ContactsE"with your frmEmployeeSetup ' note how I now have surrounded the text value with single quotes ' note how we don't need lngID anymore Response = acDataErrAdded bolAdd = True Else Response = acDataErrContinue End If Likely, your comb box "after update" code that worked before to "move" to the record can remain as you had it. We still need the If bolAdd = True Then bolAdd = False Me.Requery End If ...........your combo code to "move" to the reocrd would follow he -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#18
|
|||
|
|||
Close form, skips calling form focus
OK, I got it now. Work with it enough and THINK and it comes.
Thanks for all your help. -- Chris "Albert D.Kallal" wrote: EmployeeID is a text field and I think that is why it now fails at " IngNewID = rst!EmployeeID". Excellent observation on your part. My code assumed a "auto" id. So, you got a situation where you combo box retuns a value like SMI105...etc... So, we need to change things a bit to reflect this Dim rst As DAO.Recordset If MsgBox("Do you want to add this value to the list?", _ vbYesNo) = vbYes Then Set rst = CurrentDb.OpenRecordset("contacts") ' in the above, you would of course replace "contacts" with the actual name of the table that you ' used rst.AddNew rst!EmployeeID = NewData rst.Update rst.Close Set rst = Nothing DoCmd.OpenForm "ContactsE", , , "EmployeeID = '" & NewData & "'", , acDialog ' in the above, you of course replace "ContactsE"with your frmEmployeeSetup ' note how I now have surrounded the text value with single quotes ' note how we don't need lngID anymore Response = acDataErrAdded bolAdd = True Else Response = acDataErrContinue End If Likely, your comb box "after update" code that worked before to "move" to the record can remain as you had it. We still need the If bolAdd = True Then bolAdd = False Me.Requery End If ...........your combo code to "move" to the reocrd would follow he -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#19
|
|||
|
|||
Close form, skips calling form focus
"Chris" wrote in message
... OK...I figured out to change [frmEmployeeSetup] to NOT DataEntry. So, I have the record ok. Still, I'm not bringing the new record up on [frmEmployee]. Probably the code to open the form is wrong What does your open form command look like? We are going to use a "where" clause of the open form to "send", or better term "open"the form to the record we just created. So, what does your open form command look like? In fact, it probably a good idea for you to post both the comb not in list code as you have, and also the comb box after update code.... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
Requerying a pop up form to display in the main form | Jennifer P | Using Forms | 13 | April 5th, 2005 06:59 PM |
Calling a form on report close action. | NEMO2K | Setting Up & Running Reports | 4 | October 5th, 2004 08:13 AM |
How can I move the focus to a control on a subform? | Brandon | General Discussion | 7 | July 17th, 2004 01:39 AM |
Form Doesn't Go To New Record | Steve | New Users | 15 | May 16th, 2004 04:33 PM |