Which is better: one or several requeries?
in [Getting Started]
|
Prev: How do I set it up so that the table and form shows 2 decimal
Next: How does the tax field work in the transaction template
From: forest8 on 25 Mar 2010 23:22 Hi I have created about 30 NotInList events in my database. Is it better to have 30 individual requeries or 1 requery that contains all of them? Thank you
From: Duane Hookom on 26 Mar 2010 00:52 Huh? Can you explain why you have 30 of something? Can you provide some context or samples? -- Duane Hookom MS Access MVP "forest8" <forest8(a)discussions.microsoft.com> wrote in message news:6802BE29-E028-4264-826E-F013DCE9DA25(a)microsoft.com... > Hi > > I have created about 30 NotInList events in my database. > > Is it better to have 30 individual requeries or 1 requery that contains > all > of them? > > Thank you
From: John W. Vinson on 26 Mar 2010 01:04 On Thu, 25 Mar 2010 20:22:01 -0700, forest8 <forest8(a)discussions.microsoft.com> wrote: >Hi > >I have created about 30 NotInList events in my database. > >Is it better to have 30 individual requeries or 1 requery that contains all >of them? > >Thank you None. A properly written NotInList event will automatically requery the combo box to which it is attached, and unless you're doing something really strange, no other query or object should be affected. Please explain a bit more about what sounds like a really strange form! -- John W. Vinson [MVP]
From: forest8 on 26 Mar 2010 10:58 Hi I am currently trying to build a case management database which involves students and different areas of interest: Presecreening, Pre-entry, surveys at 6 month intervals, current information, health needs, mental needs, risk factors, care, treatment, etc. I was a bit thrown off when some NotInList events sorted and some didn't. They are all using the same code. Each time I copied the events I made the necessary changes. This is the code I used for the NotInEvent procedure for adding a new hometown: Private Sub Hometown_NotInList(NewData As String, Response As Integer) On Error GoTo Insert_Error intAnswer = MsgBox("This city is not currently in the list." & vbCrLf & _ "Would you like to add this city to the list now?" _ , vbQuestion + vbYesNo, "This city") If intAnswer = vbYes Then strSQL = "INSERT INTO CB_City([City]) " & _ "VALUES ('" & NewData & "');" DoCmd.SetWarnings False CurrentDb.Execute strSQL, dbFailOnError DoCmd.SetWarnings True MsgBox "This city has been added to the list." _ , vbInformation, "NewData" Response = acDataErrAdded End If Exit Sub Insert_Error: MsgBox "The attempted insert produced the following error:" & vbCrLf & Err Response = acDataErrContinue End Sub The changes I would make is to the message box statement and the table and field name. Thanks "John W. Vinson" wrote: > On Thu, 25 Mar 2010 20:22:01 -0700, forest8 > <forest8(a)discussions.microsoft.com> wrote: > > >Hi > > > >I have created about 30 NotInList events in my database. > > > >Is it better to have 30 individual requeries or 1 requery that contains all > >of them? > > > >Thank you > > None. > > A properly written NotInList event will automatically requery the combo box to > which it is attached, and unless you're doing something really strange, no > other query or object should be affected. > > Please explain a bit more about what sounds like a really strange form! > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 26 Mar 2010 11:56
On Fri, 26 Mar 2010 07:58:03 -0700, forest8 <forest8(a)discussions.microsoft.com> wrote: >Hi > >I am currently trying to build a case management database which involves >students and different areas of interest: Presecreening, Pre-entry, surveys >at 6 month intervals, current information, health needs, mental needs, risk >factors, care, treatment, etc. What's the structure *OF YOUR TABLES*? It all depends on the Tables. >I was a bit thrown off when some NotInList events sorted and some didn't. NotInList has *absolutely nothing* to do with sorting. A NotInList event will allow you to add another record to a table or RowSource. The sorting of the combo box is controlled by the ordering of the RowSource query. If you add a new row to the table and have an Order By clause in the query, that will sort the records and display them in order. >They are all using the same code. Each time I copied the events I made the >necessary changes. > >This is the code I used for the NotInEvent procedure for adding a new >hometown: > >Private Sub Hometown_NotInList(NewData As String, Response As Integer) > >On Error GoTo Insert_Error > intAnswer = MsgBox("This city is not currently in the list." & vbCrLf & _ > "Would you like to add this city to the list now?" _ > , vbQuestion + vbYesNo, "This city") > >If intAnswer = vbYes Then >strSQL = "INSERT INTO CB_City([City]) " & _ >"VALUES ('" & NewData & "');" >DoCmd.SetWarnings False >CurrentDb.Execute strSQL, dbFailOnError >DoCmd.SetWarnings True >MsgBox "This city has been added to the list." _ >, vbInformation, "NewData" >Response = acDataErrAdded >End If >Exit Sub > >Insert_Error: > MsgBox "The attempted insert produced the following error:" & vbCrLf & Err > Response = acDataErrContinue >End Sub This code should requery the combo box. If the combo box's RowSource is sorted, it will display it sorted; if the combo box's RowSource isn't sorted, the order of records will be arbitrary. So I think you've been looking in the wrong place to solve your real problem. -- John W. Vinson [MVP] |