|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help needed with simple field problem (0/1)I'm new to Access. I am a programmer, but my only database experience was when I made a medium sized FoxBase application for a small business years ago. The project I've started working on is way to simple to justify writing an application. The database just needs to have client contact information, appointment dates, pending messages, and payments records. I'm using Microsoft Access 2000. I started learning this today. Here's my hang-up: I'm trying to get a field to display a Clients name from another table. When I select the client ID, while adding a new record (with a Combo Box), it will only place the ClientID in the record, and not the name. I tried to do it the way it is in the sample NWIND database (attached here) that comes with Access. Select the Orders table. When you add a new record, there's a drop down for customer name. When you select one, the name is added to the record (as expected...). Now open the ClientDB database (attached here), select the Messages table, and add a new record. When you select a client name (there's only two), it places the ClientID in the Clients name field. I tried changing different things, but I can't get it to work. I don't see a significant difference between what I have and what's in the NWIND database. I've spent a couple hours trying to get it to display the clients name. Any help appreciated. I have attached the two databases to this message. (The NWIND sample database open with 5 errors, but it does what I'm doing here okay.) I have another question. Is there going to be a way to add messages (table) and appointments (table) from the customers table? (Rather than selecting the messages table, and then selecting the customers name from a drop down list.) The person this is for is going to want to be able to do everything from the customer list. (Adding records, that is - I will set up reports he can select. He's going to want to select a customer, and then do whatever with it - add dates, add messgaes.) If there's not going to be a way to do this (other than switching to the given table...), I need to stop here and find another application, rather than learn this one. (If so, suggestions?) The Attachments didn't get included.
I just put them on OrbitFiles instead. You can download them here: http://www.orbitfiles.com/download/id2209446471.html (ClientDB) http://www.orbitfiles.com/download/id2209445579.html (ClientDB) http://www.orbitfiles.com/download/id2209448731.html (NWIND) On Thu, 15 Nov 2007 05:44:58 -0500, D***@NoEmail.com wrote: Show quote >Hi, > >I'm new to Access. I am a programmer, but my only database experience >was when I made a medium sized FoxBase application for a small >business years ago. > >The project I've started working on is way to simple to justify >writing an application. The database just needs to have client contact >information, appointment dates, pending messages, and payments >records. > >I'm using Microsoft Access 2000. > >I started learning this today. Here's my hang-up: > >I'm trying to get a field to display a Clients name from another >table. When I select the client ID, while adding a new record (with a >Combo Box), it will only place the ClientID in the record, and not the >name. > >I tried to do it the way it is in the sample NWIND database (attached >here) that comes with Access. Select the Orders table. When you add a >new record, there's a drop down for customer name. When you select >one, the name is added to the record (as expected...). > >Now open the ClientDB database (attached here), select the Messages >table, and add a new record. When you select a client name (there's >only two), it places the ClientID in the Clients name field. I tried >changing different things, but I can't get it to work. I don't see a >significant difference between what I have and what's in the NWIND >database. I've spent a couple hours trying to get it to display the >clients name. > >Any help appreciated. > >I have attached the two databases to this message. (The NWIND sample >database open with 5 errors, but it does what I'm doing here okay.) > >I have another question. Is there going to be a way to add messages >(table) and appointments (table) from the customers table? (Rather >than selecting the messages table, and then selecting the customers >name from a drop down list.) The person this is for is going to want >to be able to do everything from the customer list. (Adding records, >that is - I will set up reports he can select. He's going to want to >select a customer, and then do whatever with it - add dates, add >messgaes.) If there's not going to be a way to do this (other than >switching to the given table...), I need to stop here and find another >application, rather than learn this one. (If so, suggestions?) Well, the attachements did show up. I'm using a new newsreader. The
attachement are in a seperate message with a 1/1 appended to the subject header. On Thu, 15 Nov 2007 06:04:42 -0500, D***@NoEmail.com wrote: Show quote >The Attachments didn't get included. >I just put them on OrbitFiles instead. >You can download them here: >http://www.orbitfiles.com/download/id2209446471.html (ClientDB) >http://www.orbitfiles.com/download/id2209445579.html (ClientDB) >http://www.orbitfiles.com/download/id2209448731.html (NWIND) > >On Thu, 15 Nov 2007 05:44:58 -0500, D***@NoEmail.com wrote: > >>Hi, >> >>I'm new to Access. I am a programmer, but my only database experience >>was when I made a medium sized FoxBase application for a small >>business years ago. >> >>The project I've started working on is way to simple to justify >>writing an application. The database just needs to have client contact >>information, appointment dates, pending messages, and payments >>records. >> >>I'm using Microsoft Access 2000. >> >>I started learning this today. Here's my hang-up: >> >>I'm trying to get a field to display a Clients name from another >>table. When I select the client ID, while adding a new record (with a >>Combo Box), it will only place the ClientID in the record, and not the >>name. >> >>I tried to do it the way it is in the sample NWIND database (attached >>here) that comes with Access. Select the Orders table. When you add a >>new record, there's a drop down for customer name. When you select >>one, the name is added to the record (as expected...). >> >>Now open the ClientDB database (attached here), select the Messages >>table, and add a new record. When you select a client name (there's >>only two), it places the ClientID in the Clients name field. I tried >>changing different things, but I can't get it to work. I don't see a >>significant difference between what I have and what's in the NWIND >>database. I've spent a couple hours trying to get it to display the >>clients name. >> >>Any help appreciated. >> >>I have attached the two databases to this message. (The NWIND sample >>database open with 5 errors, but it does what I'm doing here okay.) >> >>I have another question. Is there going to be a way to add messages >>(table) and appointments (table) from the customers table? (Rather >>than selecting the messages table, and then selecting the customers >>name from a drop down list.) The person this is for is going to want >>to be able to do everything from the customer list. (Adding records, >>that is - I will set up reports he can select. He's going to want to >>select a customer, and then do whatever with it - add dates, add >>messgaes.) If there's not going to be a way to do this (other than >>switching to the given table...), I need to stop here and find another >>application, rather than learn this one. (If so, suggestions?) > I'm trying to get a field to display a Clients name from another Not sure what you're trying to do. But I'll cover all three cases I can > table. When I select the client ID, while adding a new record (with a > Combo Box), it will only place the ClientID in the record, and not the > name. think of 1) If you're trying to simply display the client's name in the combo box, which is bound to the client ID, then make the combo box have two columns, with the second column being the name, and set the width of the first column to 0, which will hide the client ID. 2) If you're trying to populate a client name field in the form that also has the client ID field (which would be a non-normalized design), then use the AfterUpdate event of the combo box to populate that field. 3) If (as I think is the case) you want to display information from the Clients table when you select a Client ID in the form bound to the Messages table, then what you do is: a) In the form's recordsource, add the Clients table. So you would have two tables in the recordsource: Clients and Messages, and join them on the ClientID field. b) Bind the client-related controls in your form to the fields from the Clients table. c) Make sure your combo box is bound to the ClientID field in the Messages table. Now when you select a ClientID from the drop-down, the data from the fields from the corresponding record in the Clients table should automatically appear on the form. > I have another question. Is there going to be a way to add messages Yes, you can do that one of two ways. Either create subforms in the > (table) and appointments (table) from the customers table? (Rather > than selecting the messages table, and then selecting the customers > name from a drop down list.) The person this is for is going to want > to be able to do everything from the customer list Customers form for the Messages and Appointments tables (the subforms will be linked on the ClientID field, and Access will manage creating and filtering the records for you); or use pop-ups from the Customers form to enter Messages and Appointments (here, you'd have to manage the records yourself). Obviously, subforms are a lot more convenient, both for you to set up, as well as for the user to use, since they would be right there on the form, and the data is visible at all times. The downside of subforms is form real estate, since they take up room. If you don't have a lot of room to work with; or if you want a lot of information in your subforms, then pop-ups might be better. Subforms also use up more system resources, since they're open all the time. But I don't think that's anything you'd have to worry about, really. .. > that is - I will set up reports he can select. He's going to want to > select a customer, and then do whatever with it - add dates, add I think you'll find Access subforms to be just what you're looking for. Very > messgaes.) If there's not going to be a way to do this (other than > switching to the given table...), I need to stop here and find another > application, rather than learn this one. (If so, suggestions?) > easy to work with, and a very convenient tool. Neil
Show quote
On Thu, 15 Nov 2007 11:04:46 GMT, "Neil" <nospam@nospam.net> wrote: I guessed and put 0,1 here, which got converted to 0";1" - which> >> I'm trying to get a field to display a Clients name from another >> table. When I select the client ID, while adding a new record (with a >> Combo Box), it will only place the ClientID in the record, and not the >> name. > >Not sure what you're trying to do. But I'll cover all three cases I can >think of > >1) If you're trying to simply display the client's name in the combo box, >which is bound to the client ID, then make the combo box have two columns, >with the second column being the name, and set the width of the first column >to 0, which will hide the client ID. > solved my problem. Thanks. (I hadn't made any forms yet.) Show quote >2) If you're trying to populate a client name field in the form that also I set up a form, with a subform. This will works well. Thanks.>has the client ID field (which would be a non-normalized design), then use >the AfterUpdate event of the combo box to populate that field. > >3) If (as I think is the case) you want to display information from the >Clients table when you select a Client ID in the form bound to the Messages >table, then what you do is: > > a) In the form's recordsource, add the Clients table. So you would have >two tables in the recordsource: Clients and Messages, and join them on the >ClientID field. > > b) Bind the client-related controls in your form to the fields from the >Clients table. > > c) Make sure your combo box is bound to the ClientID field in the >Messages table. > > Now when you select a ClientID from the drop-down, the data from the >fields from the corresponding record in the Clients table should >automatically appear on the form. > >> I have another question. Is there going to be a way to add messages >> (table) and appointments (table) from the customers table? (Rather >> than selecting the messages table, and then selecting the customers >> name from a drop down list.) The person this is for is going to want >> to be able to do everything from the customer list > >Yes, you can do that one of two ways. Either create subforms in the >Customers form for the Messages and Appointments tables (the subforms will >be linked on the ClientID field, and Access will manage creating and >filtering the records for you); or use pop-ups from the Customers form to >enter Messages and Appointments (here, you'd have to manage the records >yourself). > >Obviously, subforms are a lot more convenient, both for you to set up, as >well as for the user to use, since they would be right there on the form, >and the data is visible at all times. The downside of subforms is form real >estate, since they take up room. If you don't have a lot of room to work >with; or if you want a lot of information in your subforms, then pop-ups >might be better. Subforms also use up more system resources, since they're >open all the time. But I don't think that's anything you'd have to worry >about, really. > >. > that is - I will set up reports he can select. He's going to want to >> select a customer, and then do whatever with it - add dates, add >> messgaes.) If there's not going to be a way to do this (other than >> switching to the given table...), I need to stop here and find another >> application, rather than learn this one. (If so, suggestions?) >> > >I think you'll find Access subforms to be just what you're looking for. Very >easy to work with, and a very convenient tool. > >Neil > On the form, I would like a combo box on top, where I can choose a different client from a list, which changes what record I'm looking at - like the arrow buttons at the bottom do (which I'd like to remove). Can this be done with an 'on change' event? No, you'd use the AfterUpdate event of the combo box, and then use FindFirst
against the recordsetclone object to find the appropriate record. After finding the appropriate record, set the form's Bookmark property to the Recordsetclone's Bookmark property. But, if you're going to get rid of the navigation buttons at the bottom and are always going to just display one record, then a better approach would be to just reset the form's recordsource after a client is selected: Private Sub MyCombo_AfterUpdate If Not Isnull(Me.MyCombo) Then Me.Recordsource = "Select * From MyClientTable Where ClientID=" & Me.MyCombo End If End Sub You could also refer to a query instead of the table, if you prefer. The subforms should requery themselves automatically. Show quote > I set up a form, with a subform. This will works well. Thanks. > > On the form, I would like a combo box on top, where I can choose a > different client from a list, which changes what record I'm looking at > - like the arrow buttons at the bottom do (which I'd like to remove). > Can this be done with an 'on change' event? > >
Show quote
On Fri, 16 Nov 2007 07:23:08 GMT, "Neil" <nospam@nospam.net> wrote: That worked. Thanks again.>No, you'd use the AfterUpdate event of the combo box, and then use FindFirst >against the recordsetclone object to find the appropriate record. After >finding the appropriate record, set the form's Bookmark property to the >Recordsetclone's Bookmark property. > >But, if you're going to get rid of the navigation buttons at the bottom and >are always going to just display one record, then a better approach would be >to just reset the form's recordsource after a client is selected: > >Private Sub MyCombo_AfterUpdate > > If Not Isnull(Me.MyCombo) Then > Me.Recordsource = "Select * From MyClientTable Where ClientID=" & >Me.MyCombo > End If > >End Sub When new records are added, they don't show up in the combo box until I close the client form and reopen it. Show quote > >You could also refer to a query instead of the table, if you prefer. The >subforms should requery themselves automatically. > > >> I set up a form, with a subform. This will works well. Thanks. >> >> On the form, I would like a combo box on top, where I can choose a >> different client from a list, which changes what record I'm looking at >> - like the arrow buttons at the bottom do (which I'd like to remove). >> Can this be done with an 'on change' event? >> >> > On Fri, 16 Nov 2007 23:14:44 -0500, D***@NoEmail.com wrote:
>When new records are added, they don't show up in the combo box until Requery the combo box in the code which adds the new records (the form's>I close the client form and reopen it. AfterUpdate event for example). John W. Vinson [MVP] How are you adding new records? In the above, you stated that you wanted to
take out the record selectors and only show one record at a time. If so, then you should not let your users add new records by going to the new record in the form (because then you'll have two records in the form instead of one, when they add a new record). So, how are you adding new records? Show quote > > That worked. Thanks again. > > When new records are added, they don't show up in the combo box until > I close the client form and reopen it. > I added records using the record selectors. I haven't figured out how
to delete them yet - they don't appear on the Design View screen. I haven't looked that hard - been doing other things. Once I use the combo box to select a record, the record selector count changes to 1. I can still arrow over to 2 and enter a new record. When I do that the new record doesn't show up in the combo box list (the others still do). If I close the form and reopen it, the combo box sees the new record(s). Show quote On Sat, 17 Nov 2007 00:49:12 -0600, "Neil" <nospam@nospam.net> wrote: >How are you adding new records? In the above, you stated that you wanted to >take out the record selectors and only show one record at a time. If so, >then you should not let your users add new records by going to the new >record in the form (because then you'll have two records in the form instead >of one, when they add a new record). So, how are you adding new records? > >> >> That worked. Thanks again. >> >> When new records are added, they don't show up in the combo box until >> I close the client form and reopen it. >> > <D***@NoEmail.com> wrote in message
news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... By "record selectors" I assume you mean "navigation buttons" at the bottom >I added records using the record selectors. I haven't figured out how > to delete them yet - they don't appear on the Design View screen. I > haven't looked that hard - been doing other things. of the screen, right? (The record selector is the vertical bar on the left that has an arrowhead in it.) You can turn off the navigation bar and/or the record selector in the form's property. They're both listed there with Yes/No options. > Well, as John noted, just requery the combo box when a new record is > Once I use the combo box to select a record, the record selector count > changes to 1. I can still arrow over to 2 and enter a new record. > When I do that the new record doesn't show up in the combo box list > (the others still do). If I close the form and reopen it, the combo > box sees the new record(s). inserted (I'd do the After Insert event of the form). Just do: Me.MyComboBox.Requery That's all. But, as I noted, you shouldn't add records with the navigation buttons if you're only displaying one record at a time. So, here's what you do: 1) In the form's properties, set Allow Additions to No. That will prevent the user from adding a record manually. 2) Add a button to your form for adding records. When the user clicks the button, prompt them for any required field information. If there is no required field information, and if the primary key is automatically generated, then you don't need a prompt. 3) In the button's On Click event, after the form (if any) prompts for values, do the following: ======================================= Dim rs As Recordset Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset) rs.AddNew rs!FieldPK = somevalue rs!OtherField = someothervalue 'This is optional rs.Update Me.Recordsource = "Select * From MyTable Where FieldPK="& somevalue rs.close set rs = nothing ========================================= The above is for if you prompt the user for the PK (customer ID?). If the PK is an autonumber PK, then you'd do this instead: ========================================= Dim rs As Recordset Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) rs.AddNew rs!OtherField = someothervalue 'This is optional. rs.Update rs.bookmark = rs.LastModified Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK rs.close set rs = nothing ========================================= 4) While in a code module, go to Tools | References. Uncheck Microsoft ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked, then scroll down the list, and check the highest numbered version of it. And that should do it. Neil Show quote > > On Sat, 17 Nov 2007 00:49:12 -0600, "Neil" <nospam@nospam.net> wrote: >>How are you adding new records? In the above, you stated that you wanted >>to >>take out the record selectors and only show one record at a time. If so, >>then you should not let your users add new records by going to the new >>record in the form (because then you'll have two records in the form >>instead >>of one, when they add a new record). So, how are you adding new records? >> >>> >>> That worked. Thanks again. >>> >>> When new records are added, they don't show up in the combo box until >>> I close the client form and reopen it. >>> >>
Show quote
On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> wrote: I've put that in three places (below). The only thing that updates the> ><D***@NoEmail.com> wrote in message >news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... (SNIP) >> >> Once I use the combo box to select a record, the record selector count >> changes to 1. I can still arrow over to 2 and enter a new record. >> When I do that the new record doesn't show up in the combo box list >> (the others still do). If I close the form and reopen it, the combo >> box sees the new record(s). > >Well, as John noted, just requery the combo box when a new record is >inserted (I'd do the After Insert event of the form). Just do: > > Me.MyComboBox.Requery > combo box is closing the form and reopening it. Private Sub Combo36_GotFocus() Me.Combo36.Requery End Sub Private Sub Form_AfterInsert() Me.Combo36.Requery End Sub Private Sub Form_AfterUpdate() Me.Combo36.Requery End Sub (SNIP) Show quote >The above is for if you prompt the user for the PK (customer ID?). If the PK That worked. Thanks.>is an autonumber PK, then you'd do this instead: > >========================================= >Dim rs As Recordset > >Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) > >rs.AddNew >rs!OtherField = someothervalue 'This is optional. >rs.Update > >rs.bookmark = rs.LastModified >Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK > >rs.close >set rs = nothing >========================================= > (SNIP)>4) While in a code module, go to Tools | References. Uncheck Microsoft >ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked, >then scroll down the list, and check the highest numbered version of it. > >And that should do it. > >Neil > On Sat, 17 Nov 2007 18:50:46 -0500, D***@NoEmail.com wrote:
Show quote >On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> wrote: I usually use the OnEnter event when I want to requery a combo.> >> >><D***@NoEmail.com> wrote in message >>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... >(SNIP) >>> >>> Once I use the combo box to select a record, the record selector count >>> changes to 1. I can still arrow over to 2 and enter a new record. >>> When I do that the new record doesn't show up in the combo box list >>> (the others still do). If I close the form and reopen it, the combo >>> box sees the new record(s). >> >>Well, as John noted, just requery the combo box when a new record is >>inserted (I'd do the After Insert event of the form). Just do: >> >> Me.MyComboBox.Requery >> > >I've put that in three places (below). The only thing that updates the >combo box is closing the form and reopening it. > >Private Sub Combo36_GotFocus() > Me.Combo36.Requery >End Sub > >Private Sub Form_AfterInsert() > Me.Combo36.Requery >End Sub > Wayne Gillespie Gosford NSW Australia Problem with the OnEnter event is that then you're requerying the combo
every time you enter it, instead of just when the data changes. Show quote > I usually use the OnEnter event when I want to requery a combo. > Wayne Gillespie > Gosford NSW Australia Well, it should work. What's the rowsource of your combo box? Please copy
and paste it here exactly. <D***@NoEmail.com> wrote in message Show quote news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com... > On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> wrote: > >> >><D***@NoEmail.com> wrote in message >>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... > (SNIP) >>> >>> Once I use the combo box to select a record, the record selector count >>> changes to 1. I can still arrow over to 2 and enter a new record. >>> When I do that the new record doesn't show up in the combo box list >>> (the others still do). If I close the form and reopen it, the combo >>> box sees the new record(s). >> >>Well, as John noted, just requery the combo box when a new record is >>inserted (I'd do the After Insert event of the form). Just do: >> >> Me.MyComboBox.Requery >> > > I've put that in three places (below). The only thing that updates the > combo box is closing the form and reopening it. > > Private Sub Combo36_GotFocus() > Me.Combo36.Requery > End Sub > > Private Sub Form_AfterInsert() > Me.Combo36.Requery > End Sub > > Private Sub Form_AfterUpdate() > Me.Combo36.Requery > End Sub > > > (SNIP) > >>The above is for if you prompt the user for the PK (customer ID?). If the >>PK >>is an autonumber PK, then you'd do this instead: >> >>========================================= >>Dim rs As Recordset >> >>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) >> >>rs.AddNew >>rs!OtherField = someothervalue 'This is optional. >>rs.Update >> >>rs.bookmark = rs.LastModified >>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK >> >>rs.close >>set rs = nothing >>========================================= > > That worked. Thanks. > >> >>4) While in a code module, go to Tools | References. Uncheck Microsoft >>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked, >>then scroll down the list, and check the highest numbered version of it. >> >>And that should do it. >> >>Neil >> > > (SNIP) On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" <nospam@nospam.net> wrote: Row Source Type: Table/Query>Well, it should work. What's the rowsource of your combo box? Please copy >and paste it here exactly. Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName], [Clients].[MiddleName], [Clients].[LastName] FROM Clients; I am adding records from a control button on the form now. I tested this again, and now the combo box is updated with Combo36_gotFocus(). I'd prefer to only update that when necessary, not every time a new record is selected... I don't know why that works now. The combo box is still not updated with either Form_AfterInsert() or Form_AfterUpdate(). Show quote > ><D***@NoEmail.com> wrote in message >news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com... >> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> wrote: >> >>> >>><D***@NoEmail.com> wrote in message >>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... >> (SNIP) >>>> >>>> Once I use the combo box to select a record, the record selector count >>>> changes to 1. I can still arrow over to 2 and enter a new record. >>>> When I do that the new record doesn't show up in the combo box list >>>> (the others still do). If I close the form and reopen it, the combo >>>> box sees the new record(s). >>> >>>Well, as John noted, just requery the combo box when a new record is >>>inserted (I'd do the After Insert event of the form). Just do: >>> >>> Me.MyComboBox.Requery >>> >> >> I've put that in three places (below). The only thing that updates the >> combo box is closing the form and reopening it. >> >> Private Sub Combo36_GotFocus() >> Me.Combo36.Requery >> End Sub >> >> Private Sub Form_AfterInsert() >> Me.Combo36.Requery >> End Sub >> >> Private Sub Form_AfterUpdate() >> Me.Combo36.Requery >> End Sub >> >> >> (SNIP) >> >>>The above is for if you prompt the user for the PK (customer ID?). If the >>>PK >>>is an autonumber PK, then you'd do this instead: >>> >>>========================================= >>>Dim rs As Recordset >>> >>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) >>> >>>rs.AddNew >>>rs!OtherField = someothervalue 'This is optional. >>>rs.Update >>> >>>rs.bookmark = rs.LastModified >>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK >>> >>>rs.close >>>set rs = nothing >>>========================================= >> >> That worked. Thanks. >> >>> >>>4) While in a code module, go to Tools | References. Uncheck Microsoft >>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked, >>>then scroll down the list, and check the highest numbered version of it. >>> >>>And that should do it. >>> >>>Neil >>> >> >> (SNIP) > Well, if you changed the code to add records from a command button, per the
instructions I sent, then that would explain why it's not working from the form's AfterUpdate or AfterInsert event: the form is not inserting the record; you are, though the code. So the AfterUpdate and AfterInsert events wouldn't apply. Just put the combo box requery code at the end of the routine that adds the new record, and you should be fine. <D***@NoEmail.com> wrote in message Show quote news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com... > On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" <nospam@nospam.net> wrote: > >>Well, it should work. What's the rowsource of your combo box? Please copy >>and paste it here exactly. > > Row Source Type: Table/Query > Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName], > [Clients].[MiddleName], [Clients].[LastName] FROM Clients; > > I am adding records from a control button on the form now. > > I tested this again, and now the combo box is updated with > Combo36_gotFocus(). I'd prefer to only update that when necessary, > not every time a new record is selected... I don't know why that > works now. The combo box is still not updated with either > Form_AfterInsert() or Form_AfterUpdate(). > >> >><D***@NoEmail.com> wrote in message >>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com... >>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> wrote: >>> >>>> >>>><D***@NoEmail.com> wrote in message >>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... >>> (SNIP) >>>>> >>>>> Once I use the combo box to select a record, the record selector count >>>>> changes to 1. I can still arrow over to 2 and enter a new record. >>>>> When I do that the new record doesn't show up in the combo box list >>>>> (the others still do). If I close the form and reopen it, the combo >>>>> box sees the new record(s). >>>> >>>>Well, as John noted, just requery the combo box when a new record is >>>>inserted (I'd do the After Insert event of the form). Just do: >>>> >>>> Me.MyComboBox.Requery >>>> >>> >>> I've put that in three places (below). The only thing that updates the >>> combo box is closing the form and reopening it. >>> >>> Private Sub Combo36_GotFocus() >>> Me.Combo36.Requery >>> End Sub >>> >>> Private Sub Form_AfterInsert() >>> Me.Combo36.Requery >>> End Sub >>> >>> Private Sub Form_AfterUpdate() >>> Me.Combo36.Requery >>> End Sub >>> >>> >>> (SNIP) >>> >>>>The above is for if you prompt the user for the PK (customer ID?). If >>>>the >>>>PK >>>>is an autonumber PK, then you'd do this instead: >>>> >>>>========================================= >>>>Dim rs As Recordset >>>> >>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) >>>> >>>>rs.AddNew >>>>rs!OtherField = someothervalue 'This is optional. >>>>rs.Update >>>> >>>>rs.bookmark = rs.LastModified >>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK >>>> >>>>rs.close >>>>set rs = nothing >>>>========================================= >>> >>> That worked. Thanks. >>> >>>> >>>>4) While in a code module, go to Tools | References. Uncheck Microsoft >>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not >>>>checked, >>>>then scroll down the list, and check the highest numbered version of it. >>>> >>>>And that should do it. >>>> >>>>Neil >>>> >>> >>> (SNIP) >> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" <nospam@nospam.net> wrote: I tried that, but I don't know how to reference it, and haven't gotten>Well, if you changed the code to add records from a command button, per the >instructions I sent, then that would explain why it's not working from the >form's AfterUpdate or AfterInsert event: the form is not inserting the >record; you are, though the code. So the AfterUpdate and AfterInsert events >wouldn't apply. > >Just put the combo box requery code at the end of the routine that adds the >new record, and you should be fine. > around to searching Google for that. "Me" is not pointing to the form in the add record routine. (There is no "Combo36" in the list after you type "Me." - like there is in the AfterUpdate routine.) I need to get a basic book on using Access (just systax, I know how to program - I've also never used VB before). It took me about 30 minutes searching Google to find how to change a fields value... (you use the "clone"...) Do you know of a good/short online reference? Show quote > ><D***@NoEmail.com> wrote in message >news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com... >> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" <nospam@nospam.net> wrote: >> >>>Well, it should work. What's the rowsource of your combo box? Please copy >>>and paste it here exactly. >> >> Row Source Type: Table/Query >> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName], >> [Clients].[MiddleName], [Clients].[LastName] FROM Clients; >> >> I am adding records from a control button on the form now. >> >> I tested this again, and now the combo box is updated with >> Combo36_gotFocus(). I'd prefer to only update that when necessary, >> not every time a new record is selected... I don't know why that >> works now. The combo box is still not updated with either >> Form_AfterInsert() or Form_AfterUpdate(). >> >>> >>><D***@NoEmail.com> wrote in message >>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com... >>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> wrote: >>>> >>>>> >>>>><D***@NoEmail.com> wrote in message >>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... >>>> (SNIP) >>>>>> >>>>>> Once I use the combo box to select a record, the record selector count >>>>>> changes to 1. I can still arrow over to 2 and enter a new record. >>>>>> When I do that the new record doesn't show up in the combo box list >>>>>> (the others still do). If I close the form and reopen it, the combo >>>>>> box sees the new record(s). >>>>> >>>>>Well, as John noted, just requery the combo box when a new record is >>>>>inserted (I'd do the After Insert event of the form). Just do: >>>>> >>>>> Me.MyComboBox.Requery >>>>> >>>> >>>> I've put that in three places (below). The only thing that updates the >>>> combo box is closing the form and reopening it. >>>> >>>> Private Sub Combo36_GotFocus() >>>> Me.Combo36.Requery >>>> End Sub >>>> >>>> Private Sub Form_AfterInsert() >>>> Me.Combo36.Requery >>>> End Sub >>>> >>>> Private Sub Form_AfterUpdate() >>>> Me.Combo36.Requery >>>> End Sub >>>> >>>> >>>> (SNIP) >>>> >>>>>The above is for if you prompt the user for the PK (customer ID?). If >>>>>the >>>>>PK >>>>>is an autonumber PK, then you'd do this instead: >>>>> >>>>>========================================= >>>>>Dim rs As Recordset >>>>> >>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) >>>>> >>>>>rs.AddNew >>>>>rs!OtherField = someothervalue 'This is optional. >>>>>rs.Update >>>>> >>>>>rs.bookmark = rs.LastModified >>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK >>>>> >>>>>rs.close >>>>>set rs = nothing >>>>>========================================= >>>> >>>> That worked. Thanks. >>>> >>>>> >>>>>4) While in a code module, go to Tools | References. Uncheck Microsoft >>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not >>>>>checked, >>>>>then scroll down the list, and check the highest numbered version of it. >>>>> >>>>>And that should do it. >>>>> >>>>>Neil >>>>> >>>> >>>> (SNIP) >>> > OK, we need to back up a second here. You said that the routine I sent to
add a record was working. That routine referenced Me in the Me.Recordsource= item. But you write: "'Me' is not pointing to the form in the add record routine." So, if "Me" is not pointing to the form in the add record routine, then how does the Me.Recordsource= line in the add record routine work? If Me works in one line of the routine, it will work in the other line of the same routine. So, I assume that the add record routine is in your button's OnClick event, and it resides in the form's code module, correct? If so, then Me will work. If you're not seeing Combo36, then it's either not in the form's code module, or there's a compile error and that has to be resolved first. Try compiling and see what happens. <D***@NoEmail.com> wrote in message Show quote news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com... > On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" <nospam@nospam.net> wrote: > >>Well, if you changed the code to add records from a command button, per >>the >>instructions I sent, then that would explain why it's not working from the >>form's AfterUpdate or AfterInsert event: the form is not inserting the >>record; you are, though the code. So the AfterUpdate and AfterInsert >>events >>wouldn't apply. >> >>Just put the combo box requery code at the end of the routine that adds >>the >>new record, and you should be fine. >> > > I tried that, but I don't know how to reference it, and haven't gotten > around to searching Google for that. "Me" is not pointing to the form > in the add record routine. (There is no "Combo36" in the list after > you type "Me." - like there is in the AfterUpdate routine.) I need to > get a basic book on using Access (just systax, I know how to program - > I've also never used VB before). It took me about 30 minutes > searching Google to find how to change a fields value... (you use the > "clone"...) Do you know of a good/short online reference? > > >> >><D***@NoEmail.com> wrote in message >>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com... >>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" <nospam@nospam.net> wrote: >>> >>>>Well, it should work. What's the rowsource of your combo box? Please >>>>copy >>>>and paste it here exactly. >>> >>> Row Source Type: Table/Query >>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName], >>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients; >>> >>> I am adding records from a control button on the form now. >>> >>> I tested this again, and now the combo box is updated with >>> Combo36_gotFocus(). I'd prefer to only update that when necessary, >>> not every time a new record is selected... I don't know why that >>> works now. The combo box is still not updated with either >>> Form_AfterInsert() or Form_AfterUpdate(). >>> >>>> >>>><D***@NoEmail.com> wrote in message >>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com... >>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> wrote: >>>>> >>>>>> >>>>>><D***@NoEmail.com> wrote in message >>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... >>>>> (SNIP) >>>>>>> >>>>>>> Once I use the combo box to select a record, the record selector >>>>>>> count >>>>>>> changes to 1. I can still arrow over to 2 and enter a new record. >>>>>>> When I do that the new record doesn't show up in the combo box list >>>>>>> (the others still do). If I close the form and reopen it, the combo >>>>>>> box sees the new record(s). >>>>>> >>>>>>Well, as John noted, just requery the combo box when a new record is >>>>>>inserted (I'd do the After Insert event of the form). Just do: >>>>>> >>>>>> Me.MyComboBox.Requery >>>>>> >>>>> >>>>> I've put that in three places (below). The only thing that updates the >>>>> combo box is closing the form and reopening it. >>>>> >>>>> Private Sub Combo36_GotFocus() >>>>> Me.Combo36.Requery >>>>> End Sub >>>>> >>>>> Private Sub Form_AfterInsert() >>>>> Me.Combo36.Requery >>>>> End Sub >>>>> >>>>> Private Sub Form_AfterUpdate() >>>>> Me.Combo36.Requery >>>>> End Sub >>>>> >>>>> >>>>> (SNIP) >>>>> >>>>>>The above is for if you prompt the user for the PK (customer ID?). If >>>>>>the >>>>>>PK >>>>>>is an autonumber PK, then you'd do this instead: >>>>>> >>>>>>========================================= >>>>>>Dim rs As Recordset >>>>>> >>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) >>>>>> >>>>>>rs.AddNew >>>>>>rs!OtherField = someothervalue 'This is optional. >>>>>>rs.Update >>>>>> >>>>>>rs.bookmark = rs.LastModified >>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK >>>>>> >>>>>>rs.close >>>>>>set rs = nothing >>>>>>========================================= >>>>> >>>>> That worked. Thanks. >>>>> >>>>>> >>>>>>4) While in a code module, go to Tools | References. Uncheck Microsoft >>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not >>>>>>checked, >>>>>>then scroll down the list, and check the highest numbered version of >>>>>>it. >>>>>> >>>>>>And that should do it. >>>>>> >>>>>>Neil >>>>>> >>>>> >>>>> (SNIP) >>>> >> Your right. I must have made a typo. There's a new problem. The new
record is not sorted in the combo box, it's at the top of the list. When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list is resorted. I did a couple tests, and this behavior is consistent. (I put the requery just before exit sub in the add record routine.) Show quote On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" <nospam@nospam.net> wrote: >OK, we need to back up a second here. You said that the routine I sent to >add a record was working. That routine referenced Me in the Me.Recordsource= >item. But you write: "'Me' is not pointing to the form in the add record >routine." So, if "Me" is not pointing to the form in the add record routine, >then how does the Me.Recordsource= line in the add record routine work? If >Me works in one line of the routine, it will work in the other line of the >same routine. > >So, I assume that the add record routine is in your button's OnClick event, >and it resides in the form's code module, correct? If so, then Me will work. >If you're not seeing Combo36, then it's either not in the form's code >module, or there's a compile error and that has to be resolved first. Try >compiling and see what happens. > > ><D***@NoEmail.com> wrote in message >news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com... >> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" <nospam@nospam.net> wrote: >> >>>Well, if you changed the code to add records from a command button, per >>>the >>>instructions I sent, then that would explain why it's not working from the >>>form's AfterUpdate or AfterInsert event: the form is not inserting the >>>record; you are, though the code. So the AfterUpdate and AfterInsert >>>events >>>wouldn't apply. >>> >>>Just put the combo box requery code at the end of the routine that adds >>>the >>>new record, and you should be fine. >>> >> >> I tried that, but I don't know how to reference it, and haven't gotten >> around to searching Google for that. "Me" is not pointing to the form >> in the add record routine. (There is no "Combo36" in the list after >> you type "Me." - like there is in the AfterUpdate routine.) I need to >> get a basic book on using Access (just systax, I know how to program - >> I've also never used VB before). It took me about 30 minutes >> searching Google to find how to change a fields value... (you use the >> "clone"...) Do you know of a good/short online reference? >> >> >>> >>><D***@NoEmail.com> wrote in message >>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com... >>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" <nospam@nospam.net> wrote: >>>> >>>>>Well, it should work. What's the rowsource of your combo box? Please >>>>>copy >>>>>and paste it here exactly. >>>> >>>> Row Source Type: Table/Query >>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName], >>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients; >>>> >>>> I am adding records from a control button on the form now. >>>> >>>> I tested this again, and now the combo box is updated with >>>> Combo36_gotFocus(). I'd prefer to only update that when necessary, >>>> not every time a new record is selected... I don't know why that >>>> works now. The combo box is still not updated with either >>>> Form_AfterInsert() or Form_AfterUpdate(). >>>> >>>>> >>>>><D***@NoEmail.com> wrote in message >>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com... >>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> wrote: >>>>>> >>>>>>> >>>>>>><D***@NoEmail.com> wrote in message >>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... >>>>>> (SNIP) >>>>>>>> >>>>>>>> Once I use the combo box to select a record, the record selector >>>>>>>> count >>>>>>>> changes to 1. I can still arrow over to 2 and enter a new record. >>>>>>>> When I do that the new record doesn't show up in the combo box list >>>>>>>> (the others still do). If I close the form and reopen it, the combo >>>>>>>> box sees the new record(s). >>>>>>> >>>>>>>Well, as John noted, just requery the combo box when a new record is >>>>>>>inserted (I'd do the After Insert event of the form). Just do: >>>>>>> >>>>>>> Me.MyComboBox.Requery >>>>>>> >>>>>> >>>>>> I've put that in three places (below). The only thing that updates the >>>>>> combo box is closing the form and reopening it. >>>>>> >>>>>> Private Sub Combo36_GotFocus() >>>>>> Me.Combo36.Requery >>>>>> End Sub >>>>>> >>>>>> Private Sub Form_AfterInsert() >>>>>> Me.Combo36.Requery >>>>>> End Sub >>>>>> >>>>>> Private Sub Form_AfterUpdate() >>>>>> Me.Combo36.Requery >>>>>> End Sub >>>>>> >>>>>> >>>>>> (SNIP) >>>>>> >>>>>>>The above is for if you prompt the user for the PK (customer ID?). If >>>>>>>the >>>>>>>PK >>>>>>>is an autonumber PK, then you'd do this instead: >>>>>>> >>>>>>>========================================= >>>>>>>Dim rs As Recordset >>>>>>> >>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) >>>>>>> >>>>>>>rs.AddNew >>>>>>>rs!OtherField = someothervalue 'This is optional. >>>>>>>rs.Update >>>>>>> >>>>>>>rs.bookmark = rs.LastModified >>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK >>>>>>> >>>>>>>rs.close >>>>>>>set rs = nothing >>>>>>>========================================= >>>>>> >>>>>> That worked. Thanks. >>>>>> >>>>>>> >>>>>>>4) While in a code module, go to Tools | References. Uncheck Microsoft >>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not >>>>>>>checked, >>>>>>>then scroll down the list, and check the highest numbered version of >>>>>>>it. >>>>>>> >>>>>>>And that should do it. >>>>>>> >>>>>>>Neil >>>>>>> >>>>>> >>>>>> (SNIP) >>>>> >>> > Where you place the requery command should have no effect on the sorting of
the combo box. Is you combo explicitly sorted? If so, then this shouldn't be happening. If not, then there might be some weird glitch with a new record being at the top, etc. Either way, just make sure you have an explicit sort in your combo's rowsource, and you should fine. <D***@NoEmail.com> wrote in message Show quote news:97o7k3t54hi9hljadr709mevkel6m31ic1@4ax.com... > Your right. I must have made a typo. There's a new problem. The new > record is not sorted in the combo box, it's at the top of the list. > When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list > is resorted. I did a couple tests, and this behavior is consistent. > (I put the requery just before exit sub in the add record routine.) > > > On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" <nospam@nospam.net> wrote: > >>OK, we need to back up a second here. You said that the routine I sent to >>add a record was working. That routine referenced Me in the >>Me.Recordsource= >>item. But you write: "'Me' is not pointing to the form in the add record >>routine." So, if "Me" is not pointing to the form in the add record >>routine, >>then how does the Me.Recordsource= line in the add record routine work? If >>Me works in one line of the routine, it will work in the other line of the >>same routine. >> >>So, I assume that the add record routine is in your button's OnClick >>event, >>and it resides in the form's code module, correct? If so, then Me will >>work. >>If you're not seeing Combo36, then it's either not in the form's code >>module, or there's a compile error and that has to be resolved first. Try >>compiling and see what happens. >> >> >><D***@NoEmail.com> wrote in message >>news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com... >>> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" <nospam@nospam.net> wrote: >>> >>>>Well, if you changed the code to add records from a command button, per >>>>the >>>>instructions I sent, then that would explain why it's not working from >>>>the >>>>form's AfterUpdate or AfterInsert event: the form is not inserting the >>>>record; you are, though the code. So the AfterUpdate and AfterInsert >>>>events >>>>wouldn't apply. >>>> >>>>Just put the combo box requery code at the end of the routine that adds >>>>the >>>>new record, and you should be fine. >>>> >>> >>> I tried that, but I don't know how to reference it, and haven't gotten >>> around to searching Google for that. "Me" is not pointing to the form >>> in the add record routine. (There is no "Combo36" in the list after >>> you type "Me." - like there is in the AfterUpdate routine.) I need to >>> get a basic book on using Access (just systax, I know how to program - >>> I've also never used VB before). It took me about 30 minutes >>> searching Google to find how to change a fields value... (you use the >>> "clone"...) Do you know of a good/short online reference? >>> >>> >>>> >>>><D***@NoEmail.com> wrote in message >>>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com... >>>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" <nospam@nospam.net> wrote: >>>>> >>>>>>Well, it should work. What's the rowsource of your combo box? Please >>>>>>copy >>>>>>and paste it here exactly. >>>>> >>>>> Row Source Type: Table/Query >>>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName], >>>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients; >>>>> >>>>> I am adding records from a control button on the form now. >>>>> >>>>> I tested this again, and now the combo box is updated with >>>>> Combo36_gotFocus(). I'd prefer to only update that when necessary, >>>>> not every time a new record is selected... I don't know why that >>>>> works now. The combo box is still not updated with either >>>>> Form_AfterInsert() or Form_AfterUpdate(). >>>>> >>>>>> >>>>>><D***@NoEmail.com> wrote in message >>>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com... >>>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> >>>>>>> wrote: >>>>>>> >>>>>>>> >>>>>>>><D***@NoEmail.com> wrote in message >>>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... >>>>>>> (SNIP) >>>>>>>>> >>>>>>>>> Once I use the combo box to select a record, the record selector >>>>>>>>> count >>>>>>>>> changes to 1. I can still arrow over to 2 and enter a new record. >>>>>>>>> When I do that the new record doesn't show up in the combo box >>>>>>>>> list >>>>>>>>> (the others still do). If I close the form and reopen it, the >>>>>>>>> combo >>>>>>>>> box sees the new record(s). >>>>>>>> >>>>>>>>Well, as John noted, just requery the combo box when a new record is >>>>>>>>inserted (I'd do the After Insert event of the form). Just do: >>>>>>>> >>>>>>>> Me.MyComboBox.Requery >>>>>>>> >>>>>>> >>>>>>> I've put that in three places (below). The only thing that updates >>>>>>> the >>>>>>> combo box is closing the form and reopening it. >>>>>>> >>>>>>> Private Sub Combo36_GotFocus() >>>>>>> Me.Combo36.Requery >>>>>>> End Sub >>>>>>> >>>>>>> Private Sub Form_AfterInsert() >>>>>>> Me.Combo36.Requery >>>>>>> End Sub >>>>>>> >>>>>>> Private Sub Form_AfterUpdate() >>>>>>> Me.Combo36.Requery >>>>>>> End Sub >>>>>>> >>>>>>> >>>>>>> (SNIP) >>>>>>> >>>>>>>>The above is for if you prompt the user for the PK (customer ID?). >>>>>>>>If >>>>>>>>the >>>>>>>>PK >>>>>>>>is an autonumber PK, then you'd do this instead: >>>>>>>> >>>>>>>>========================================= >>>>>>>>Dim rs As Recordset >>>>>>>> >>>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) >>>>>>>> >>>>>>>>rs.AddNew >>>>>>>>rs!OtherField = someothervalue 'This is optional. >>>>>>>>rs.Update >>>>>>>> >>>>>>>>rs.bookmark = rs.LastModified >>>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK >>>>>>>> >>>>>>>>rs.close >>>>>>>>set rs = nothing >>>>>>>>========================================= >>>>>>> >>>>>>> That worked. Thanks. >>>>>>> >>>>>>>> >>>>>>>>4) While in a code module, go to Tools | References. Uncheck >>>>>>>>Microsoft >>>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not >>>>>>>>checked, >>>>>>>>then scroll down the list, and check the highest numbered version of >>>>>>>>it. >>>>>>>> >>>>>>>>And that should do it. >>>>>>>> >>>>>>>>Neil >>>>>>>> >>>>>>> >>>>>>> (SNIP) >>>>>> >>>> >> Here's the combo's rowsource:
SELECT [Clients].[ClientID], [Clients].[FullName] FROM Clients ORDER BY [Clients].[FullName]; Show quote On Wed, 21 Nov 2007 12:23:33 GMT, "Neil" <nospam@nospam.net> wrote: >Where you place the requery command should have no effect on the sorting of >the combo box. Is you combo explicitly sorted? If so, then this shouldn't be >happening. If not, then there might be some weird glitch with a new record >being at the top, etc. Either way, just make sure you have an explicit sort >in your combo's rowsource, and you should fine. > > ><D***@NoEmail.com> wrote in message >news:97o7k3t54hi9hljadr709mevkel6m31ic1@4ax.com... >> Your right. I must have made a typo. There's a new problem. The new >> record is not sorted in the combo box, it's at the top of the list. >> When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list >> is resorted. I did a couple tests, and this behavior is consistent. >> (I put the requery just before exit sub in the add record routine.) >> >> >> On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" <nospam@nospam.net> wrote: >> >>>OK, we need to back up a second here. You said that the routine I sent to >>>add a record was working. That routine referenced Me in the >>>Me.Recordsource= >>>item. But you write: "'Me' is not pointing to the form in the add record >>>routine." So, if "Me" is not pointing to the form in the add record >>>routine, >>>then how does the Me.Recordsource= line in the add record routine work? If >>>Me works in one line of the routine, it will work in the other line of the >>>same routine. >>> >>>So, I assume that the add record routine is in your button's OnClick >>>event, >>>and it resides in the form's code module, correct? If so, then Me will >>>work. >>>If you're not seeing Combo36, then it's either not in the form's code >>>module, or there's a compile error and that has to be resolved first. Try >>>compiling and see what happens. >>> >>> >>><D***@NoEmail.com> wrote in message >>>news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com... >>>> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" <nospam@nospam.net> wrote: >>>> >>>>>Well, if you changed the code to add records from a command button, per >>>>>the >>>>>instructions I sent, then that would explain why it's not working from >>>>>the >>>>>form's AfterUpdate or AfterInsert event: the form is not inserting the >>>>>record; you are, though the code. So the AfterUpdate and AfterInsert >>>>>events >>>>>wouldn't apply. >>>>> >>>>>Just put the combo box requery code at the end of the routine that adds >>>>>the >>>>>new record, and you should be fine. >>>>> >>>> >>>> I tried that, but I don't know how to reference it, and haven't gotten >>>> around to searching Google for that. "Me" is not pointing to the form >>>> in the add record routine. (There is no "Combo36" in the list after >>>> you type "Me." - like there is in the AfterUpdate routine.) I need to >>>> get a basic book on using Access (just systax, I know how to program - >>>> I've also never used VB before). It took me about 30 minutes >>>> searching Google to find how to change a fields value... (you use the >>>> "clone"...) Do you know of a good/short online reference? >>>> >>>> >>>>> >>>>><D***@NoEmail.com> wrote in message >>>>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com... >>>>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" <nospam@nospam.net> wrote: >>>>>> >>>>>>>Well, it should work. What's the rowsource of your combo box? Please >>>>>>>copy >>>>>>>and paste it here exactly. >>>>>> >>>>>> Row Source Type: Table/Query >>>>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName], >>>>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients; >>>>>> >>>>>> I am adding records from a control button on the form now. >>>>>> >>>>>> I tested this again, and now the combo box is updated with >>>>>> Combo36_gotFocus(). I'd prefer to only update that when necessary, >>>>>> not every time a new record is selected... I don't know why that >>>>>> works now. The combo box is still not updated with either >>>>>> Form_AfterInsert() or Form_AfterUpdate(). >>>>>> >>>>>>> >>>>>>><D***@NoEmail.com> wrote in message >>>>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com... >>>>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> >>>>>>>> wrote: >>>>>>>> >>>>>>>>> >>>>>>>>><D***@NoEmail.com> wrote in message >>>>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... >>>>>>>> (SNIP) >>>>>>>>>> >>>>>>>>>> Once I use the combo box to select a record, the record selector >>>>>>>>>> count >>>>>>>>>> changes to 1. I can still arrow over to 2 and enter a new record. >>>>>>>>>> When I do that the new record doesn't show up in the combo box >>>>>>>>>> list >>>>>>>>>> (the others still do). If I close the form and reopen it, the >>>>>>>>>> combo >>>>>>>>>> box sees the new record(s). >>>>>>>>> >>>>>>>>>Well, as John noted, just requery the combo box when a new record is >>>>>>>>>inserted (I'd do the After Insert event of the form). Just do: >>>>>>>>> >>>>>>>>> Me.MyComboBox.Requery >>>>>>>>> >>>>>>>> >>>>>>>> I've put that in three places (below). The only thing that updates >>>>>>>> the >>>>>>>> combo box is closing the form and reopening it. >>>>>>>> >>>>>>>> Private Sub Combo36_GotFocus() >>>>>>>> Me.Combo36.Requery >>>>>>>> End Sub >>>>>>>> >>>>>>>> Private Sub Form_AfterInsert() >>>>>>>> Me.Combo36.Requery >>>>>>>> End Sub >>>>>>>> >>>>>>>> Private Sub Form_AfterUpdate() >>>>>>>> Me.Combo36.Requery >>>>>>>> End Sub >>>>>>>> >>>>>>>> >>>>>>>> (SNIP) >>>>>>>> >>>>>>>>>The above is for if you prompt the user for the PK (customer ID?). >>>>>>>>>If >>>>>>>>>the >>>>>>>>>PK >>>>>>>>>is an autonumber PK, then you'd do this instead: >>>>>>>>> >>>>>>>>>========================================= >>>>>>>>>Dim rs As Recordset >>>>>>>>> >>>>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) >>>>>>>>> >>>>>>>>>rs.AddNew >>>>>>>>>rs!OtherField = someothervalue 'This is optional. >>>>>>>>>rs.Update >>>>>>>>> >>>>>>>>>rs.bookmark = rs.LastModified >>>>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK >>>>>>>>> >>>>>>>>>rs.close >>>>>>>>>set rs = nothing >>>>>>>>>========================================= >>>>>>>> >>>>>>>> That worked. Thanks. >>>>>>>> >>>>>>>>> >>>>>>>>>4) While in a code module, go to Tools | References. Uncheck >>>>>>>>>Microsoft >>>>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not >>>>>>>>>checked, >>>>>>>>>then scroll down the list, and check the highest numbered version of >>>>>>>>>it. >>>>>>>>> >>>>>>>>>And that should do it. >>>>>>>>> >>>>>>>>>Neil >>>>>>>>> >>>>>>>> >>>>>>>> (SNIP) >>>>>>> >>>>> >>> > No clue. I have no idea why the new record would be at the top of the list
when called from one place, but in the correct sort order when called from another place. That makes absolutely no sense. Sorry I couldn't be more helpful here. <D***@NoEmail.com> wrote in message Show quote news:o1eck3huc3rts4r52nk8322lc0qff99mff@4ax.com... > Here's the combo's rowsource: > SELECT [Clients].[ClientID], [Clients].[FullName] FROM Clients ORDER > BY [Clients].[FullName]; > > On Wed, 21 Nov 2007 12:23:33 GMT, "Neil" <nospam@nospam.net> wrote: > >>Where you place the requery command should have no effect on the sorting >>of >>the combo box. Is you combo explicitly sorted? If so, then this shouldn't >>be >>happening. If not, then there might be some weird glitch with a new record >>being at the top, etc. Either way, just make sure you have an explicit >>sort >>in your combo's rowsource, and you should fine. >> >> >><D***@NoEmail.com> wrote in message >>news:97o7k3t54hi9hljadr709mevkel6m31ic1@4ax.com... >>> Your right. I must have made a typo. There's a new problem. The new >>> record is not sorted in the combo box, it's at the top of the list. >>> When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list >>> is resorted. I did a couple tests, and this behavior is consistent. >>> (I put the requery just before exit sub in the add record routine.) >>> >>> >>> On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" <nospam@nospam.net> wrote: >>> >>>>OK, we need to back up a second here. You said that the routine I sent >>>>to >>>>add a record was working. That routine referenced Me in the >>>>Me.Recordsource= >>>>item. But you write: "'Me' is not pointing to the form in the add record >>>>routine." So, if "Me" is not pointing to the form in the add record >>>>routine, >>>>then how does the Me.Recordsource= line in the add record routine work? >>>>If >>>>Me works in one line of the routine, it will work in the other line of >>>>the >>>>same routine. >>>> >>>>So, I assume that the add record routine is in your button's OnClick >>>>event, >>>>and it resides in the form's code module, correct? If so, then Me will >>>>work. >>>>If you're not seeing Combo36, then it's either not in the form's code >>>>module, or there's a compile error and that has to be resolved first. >>>>Try >>>>compiling and see what happens. >>>> >>>> >>>><D***@NoEmail.com> wrote in message >>>>news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com... >>>>> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" <nospam@nospam.net> wrote: >>>>> >>>>>>Well, if you changed the code to add records from a command button, >>>>>>per >>>>>>the >>>>>>instructions I sent, then that would explain why it's not working from >>>>>>the >>>>>>form's AfterUpdate or AfterInsert event: the form is not inserting the >>>>>>record; you are, though the code. So the AfterUpdate and AfterInsert >>>>>>events >>>>>>wouldn't apply. >>>>>> >>>>>>Just put the combo box requery code at the end of the routine that >>>>>>adds >>>>>>the >>>>>>new record, and you should be fine. >>>>>> >>>>> >>>>> I tried that, but I don't know how to reference it, and haven't gotten >>>>> around to searching Google for that. "Me" is not pointing to the form >>>>> in the add record routine. (There is no "Combo36" in the list after >>>>> you type "Me." - like there is in the AfterUpdate routine.) I need to >>>>> get a basic book on using Access (just systax, I know how to program - >>>>> I've also never used VB before). It took me about 30 minutes >>>>> searching Google to find how to change a fields value... (you use the >>>>> "clone"...) Do you know of a good/short online reference? >>>>> >>>>> >>>>>> >>>>>><D***@NoEmail.com> wrote in message >>>>>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com... >>>>>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" <nospam@nospam.net> wrote: >>>>>>> >>>>>>>>Well, it should work. What's the rowsource of your combo box? Please >>>>>>>>copy >>>>>>>>and paste it here exactly. >>>>>>> >>>>>>> Row Source Type: Table/Query >>>>>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName], >>>>>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients; >>>>>>> >>>>>>> I am adding records from a control button on the form now. >>>>>>> >>>>>>> I tested this again, and now the combo box is updated with >>>>>>> Combo36_gotFocus(). I'd prefer to only update that when necessary, >>>>>>> not every time a new record is selected... I don't know why that >>>>>>> works now. The combo box is still not updated with either >>>>>>> Form_AfterInsert() or Form_AfterUpdate(). >>>>>>> >>>>>>>> >>>>>>>><D***@NoEmail.com> wrote in message >>>>>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com... >>>>>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> >>>>>>>>> wrote: >>>>>>>>> >>>>>>>>>> >>>>>>>>>><D***@NoEmail.com> wrote in message >>>>>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... >>>>>>>>> (SNIP) >>>>>>>>>>> >>>>>>>>>>> Once I use the combo box to select a record, the record selector >>>>>>>>>>> count >>>>>>>>>>> changes to 1. I can still arrow over to 2 and enter a new >>>>>>>>>>> record. >>>>>>>>>>> When I do that the new record doesn't show up in the combo box >>>>>>>>>>> list >>>>>>>>>>> (the others still do). If I close the form and reopen it, the >>>>>>>>>>> combo >>>>>>>>>>> box sees the new record(s). >>>>>>>>>> >>>>>>>>>>Well, as John noted, just requery the combo box when a new record >>>>>>>>>>is >>>>>>>>>>inserted (I'd do the After Insert event of the form). Just do: >>>>>>>>>> >>>>>>>>>> Me.MyComboBox.Requery >>>>>>>>>> >>>>>>>>> >>>>>>>>> I've put that in three places (below). The only thing that updates >>>>>>>>> the >>>>>>>>> combo box is closing the form and reopening it. >>>>>>>>> >>>>>>>>> Private Sub Combo36_GotFocus() >>>>>>>>> Me.Combo36.Requery >>>>>>>>> End Sub >>>>>>>>> >>>>>>>>> Private Sub Form_AfterInsert() >>>>>>>>> Me.Combo36.Requery >>>>>>>>> End Sub >>>>>>>>> >>>>>>>>> Private Sub Form_AfterUpdate() >>>>>>>>> Me.Combo36.Requery >>>>>>>>> End Sub >>>>>>>>> >>>>>>>>> >>>>>>>>> (SNIP) >>>>>>>>> >>>>>>>>>>The above is for if you prompt the user for the PK (customer ID?). >>>>>>>>>>If >>>>>>>>>>the >>>>>>>>>>PK >>>>>>>>>>is an autonumber PK, then you'd do this instead: >>>>>>>>>> >>>>>>>>>>========================================= >>>>>>>>>>Dim rs As Recordset >>>>>>>>>> >>>>>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) >>>>>>>>>> >>>>>>>>>>rs.AddNew >>>>>>>>>>rs!OtherField = someothervalue 'This is optional. >>>>>>>>>>rs.Update >>>>>>>>>> >>>>>>>>>>rs.bookmark = rs.LastModified >>>>>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& >>>>>>>>>>rs!FieldPK >>>>>>>>>> >>>>>>>>>>rs.close >>>>>>>>>>set rs = nothing >>>>>>>>>>========================================= >>>>>>>>> >>>>>>>>> That worked. Thanks. >>>>>>>>> >>>>>>>>>> >>>>>>>>>>4) While in a code module, go to Tools | References. Uncheck >>>>>>>>>>Microsoft >>>>>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not >>>>>>>>>>checked, >>>>>>>>>>then scroll down the list, and check the highest numbered version >>>>>>>>>>of >>>>>>>>>>it. >>>>>>>>>> >>>>>>>>>>And that should do it. >>>>>>>>>> >>>>>>>>>>Neil >>>>>>>>>> >>>>>>>>> >>>>>>>>> (SNIP) >>>>>>>> >>>>>> >>>> >> (I'm reposting this as it never showed up on my newsfeed.)
Here's the combo's rowsource: SELECT [Clients].[ClientID], [Clients].[FullName] FROM Clients ORDER BY [Clients].[FullName]; Show quote On Wed, 21 Nov 2007 12:23:33 GMT, "Neil" <nospam@nospam.net> wrote: >Where you place the requery command should have no effect on the sorting of >the combo box. Is you combo explicitly sorted? If so, then this shouldn't be >happening. If not, then there might be some weird glitch with a new record >being at the top, etc. Either way, just make sure you have an explicit sort >in your combo's rowsource, and you should fine. > > ><D***@NoEmail.com> wrote in message >news:97o7k3t54hi9hljadr709mevkel6m31ic1@4ax.com... >> Your right. I must have made a typo. There's a new problem. The new >> record is not sorted in the combo box, it's at the top of the list. >> When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list >> is resorted. I did a couple tests, and this behavior is consistent. >> (I put the requery just before exit sub in the add record routine.) >> >> >> On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" <nospam@nospam.net> wrote: >> >>>OK, we need to back up a second here. You said that the routine I sent to >>>add a record was working. That routine referenced Me in the >>>Me.Recordsource= >>>item. But you write: "'Me' is not pointing to the form in the add record >>>routine." So, if "Me" is not pointing to the form in the add record >>>routine, >>>then how does the Me.Recordsource= line in the add record routine work? If >>>Me works in one line of the routine, it will work in the other line of the >>>same routine. >>> >>>So, I assume that the add record routine is in your button's OnClick >>>event, >>>and it resides in the form's code module, correct? If so, then Me will >>>work. >>>If you're not seeing Combo36, then it's either not in the form's code >>>module, or there's a compile error and that has to be resolved first. Try >>>compiling and see what happens. >>> >>> >>><D***@NoEmail.com> wrote in message >>>news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com... >>>> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" <nospam@nospam.net> wrote: >>>> >>>>>Well, if you changed the code to add records from a command button, per >>>>>the >>>>>instructions I sent, then that would explain why it's not working from >>>>>the >>>>>form's AfterUpdate or AfterInsert event: the form is not inserting the >>>>>record; you are, though the code. So the AfterUpdate and AfterInsert >>>>>events >>>>>wouldn't apply. >>>>> >>>>>Just put the combo box requery code at the end of the routine that adds >>>>>the >>>>>new record, and you should be fine. >>>>> >>>> >>>> I tried that, but I don't know how to reference it, and haven't gotten >>>> around to searching Google for that. "Me" is not pointing to the form >>>> in the add record routine. (There is no "Combo36" in the list after >>>> you type "Me." - like there is in the AfterUpdate routine.) I need to >>>> get a basic book on using Access (just systax, I know how to program - >>>> I've also never used VB before). It took me about 30 minutes >>>> searching Google to find how to change a fields value... (you use the >>>> "clone"...) Do you know of a good/short online reference? >>>> >>>> >>>>> >>>>><D***@NoEmail.com> wrote in message >>>>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com... >>>>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" <nospam@nospam.net> wrote: >>>>>> >>>>>>>Well, it should work. What's the rowsource of your combo box? Please >>>>>>>copy >>>>>>>and paste it here exactly. >>>>>> >>>>>> Row Source Type: Table/Query >>>>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName], >>>>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients; >>>>>> >>>>>> I am adding records from a control button on the form now. >>>>>> >>>>>> I tested this again, and now the combo box is updated with >>>>>> Combo36_gotFocus(). I'd prefer to only update that when necessary, >>>>>> not every time a new record is selected... I don't know why that >>>>>> works now. The combo box is still not updated with either >>>>>> Form_AfterInsert() or Form_AfterUpdate(). >>>>>> >>>>>>> >>>>>>><D***@NoEmail.com> wrote in message >>>>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com... >>>>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" <nospam@nospam.net> >>>>>>>> wrote: >>>>>>>> >>>>>>>>> >>>>>>>>><D***@NoEmail.com> wrote in message >>>>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com... >>>>>>>> (SNIP) >>>>>>>>>> >>>>>>>>>> Once I use the combo box to select a record, the record selector >>>>>>>>>> count >>>>>>>>>> changes to 1. I can still arrow over to 2 and enter a new record. >>>>>>>>>> When I do that the new record doesn't show up in the combo box >>>>>>>>>> list >>>>>>>>>> (the others still do). If I close the form and reopen it, the >>>>>>>>>> combo >>>>>>>>>> box sees the new record(s). >>>>>>>>> >>>>>>>>>Well, as John noted, just requery the combo box when a new record is >>>>>>>>>inserted (I'd do the After Insert event of the form). Just do: >>>>>>>>> >>>>>>>>> Me.MyComboBox.Requery >>>>>>>>> >>>>>>>> >>>>>>>> I've put that in three places (below). The only thing that updates >>>>>>>> the >>>>>>>> combo box is closing the form and reopening it. >>>>>>>> >>>>>>>> Private Sub Combo36_GotFocus() >>>>>>>> Me.Combo36.Requery >>>>>>>> End Sub >>>>>>>> >>>>>>>> Private Sub Form_AfterInsert() >>>>>>>> Me.Combo36.Requery >>>>>>>> End Sub >>>>>>>> >>>>>>>> Private Sub Form_AfterUpdate() >>>>>>>> Me.Combo36.Requery >>>>>>>> End Sub >>>>>>>> >>>>>>>> >>>>>>>> (SNIP) >>>>>>>> >>>>>>>>>The above is for if you prompt the user for the PK (customer ID?). >>>>>>>>>If >>>>>>>>>the >>>>>>>>>PK >>>>>>>>>is an autonumber PK, then you'd do this instead: >>>>>>>>> >>>>>>>>>========================================= >>>>>>>>>Dim rs As Recordset >>>>>>>>> >>>>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) >>>>>>>>> >>>>>>>>>rs.AddNew >>>>>>>>>rs!OtherField = someothervalue 'This is optional. >>>>>>>>>rs.Update >>>>>>>>> >>>>>>>>>rs.bookmark = rs.LastModified >>>>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK >>>>>>>>> >>>>>>>>>rs.close >>>>>>>>>set rs = nothing >>>>>>>>>========================================= >>>>>>>> >>>>>>>> That worked. Thanks. >>>>>>>> >>>>>>>>> >>>>>>>>>4) While in a code module, go to Tools | References. Uncheck >>>>>>>>>Microsoft >>>>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not >>>>>>>>>checked, >>>>>>>>>then scroll down the list, and check the highest numbered version of >>>>>>>>>it. >>>>>>>>> >>>>>>>>>And that should do it. >>>>>>>>> >>>>>>>>>Neil >>>>>>>>> >>>>>>>> >>>>>>>> (SNIP) >>>>>>> >>>>> >>> > The original showed up, and I replied to it. Here's my reply in case for some reason you didn't see it: No clue. I have no idea why the new record would be at the top of the list when requery is called from one place, but in the correct sort order when called from another place. That makes absolutely no sense. Sorry I couldn't be more helpful here. <D***@NoEmail.com> wrote in message Show quote news:4g8dk312k85qrpj5r5k3mo2qu65itc8gut@4ax.com... > (I'm reposting this as it never showed up on my newsfeed.) > > Here's the combo's rowsource: > SELECT [Clients].[ClientID], [Clients].[FullName] FROM Clients ORDER > BY [Clients].[FullName]; > > On Wed, 21 Nov 2007 12:23:33 GMT, "Neil" <nospam@nospam.net> wrote: > >>Where you place the requery command should have no effect on the sorting >>of >>the combo box. Is you combo explicitly sorted? If so, then this shouldn't >>be >>happening. If not, then there might be some weird glitch with a new record >>being at the top, etc. Either way, just make sure you have an explicit >>sort >>in your combo's rowsource, and you should fine. >> >> >><D***@NoEmail.com> wrote in message >>news:97o7k3t54hi9hljadr709mevkel6m31ic1@4ax.com... >>> Your right. I must have made a typo. There's a new problem. The new >>> record is not sorted in the combo box, it's at the top of the list. >>> When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list >>> is resorted. I did a couple tests, and this behavior is consistent. >>> (I put the requery just before exit sub in the add record routine.) >>> >>> >>> On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" <nospam@nospam.net> wrote: >>> >>>>OK, we need to back up a second here. You said that the routine I sent >>>>to >>>>add a record was working. That routine referenced Me in the >>>>Me.Recordsource= >>>>item. But you write: "'Me' is not pointing to the form in the add record >>>>routine." So, if "Me" is not pointing to the form in the add record >>>>routine, >>>>then how does the Me.Recordsource= line in the add record routine work? >>>>If >>>>Me works in one line of the routine, it will work in the other line of >>>>the >>>>same routine. >>>> >>>>So, I assume that the add record routine is in your button's OnClick >>>>event, >>>>and it resides in the form's code module, correct? If so, then Me will >>>>work. >>>>If you're not seeing Combo36, then it's either not in the form's code >>>>module, or there's a compile error and that has to be resolved first. >>>>Try >>>>compiling and see what happens. >>>> >>>> >>>><D***@NoEmail.com> wrote in message >>>>news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com... >>>>> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" <nospam@nospam.net> wrote: >>>>> >>>>>>Well, if you changed the code to add records from a command button, >>>>>>per >>>>>>the >>>>>>instructions I sent, then that would explain why it's not working from >>>>>>the >>>>>>form's AfterUpdate or AfterInsert event: the form is not inserting the >>>>>>record; you are, though the code. So the AfterUpdate and AfterInsert >>>>>>events >>>>>>wouldn't apply. >>>>>> >>>>>>Just put the combo box requery code at the end of the routine that >>>>>>adds >>>>>>the >>>>>>new record, and you should be fine. >>>>>> >>>>> >>>>> I tried that, but I don't know how to reference it, and haven't gotten >>>>> around to searching Google for that. "Me" is not pointing to the form >>>>> in the add record routine. (There is no "Combo36" in the list after >>>>> you type "Me." - like there is in the AfterUpdate routine.) I need to >>>>> get a basic book on using Access (just systax, I know how to program - >>>>> I've also never used VB before). It took me about 30 minutes >>>>> searching Google to find how to change a fields value... (you use the >>>>> "clone"...) Do you know of a good/short online reference? >>>>> >>>>> >>>>>> >>>>>><D***@NoEmail.com> wrote in message >>>>>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com... >>>>>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" <nospam@nospam.net> wrote: >>>>>>> >>>>>>>>Well, it should work. What's the rowsource of your combo box? Please >>>>>>>>copy >>>>>>>>and paste it here exactly. >>>>>>> >>>>>>> Row Source Type: Table/Query >>>>>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName], >>>>>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients; >>>>>>> >>>>>>> I am adding records from a control button on the form now. >>>>>>> >>>>>>> I tested this again, and now the combo box is updated with >>>>>>> Combo36_gotFocus(). I'd prefer to only update that when necessary, >>&g | |||||||||||||||||||||||
