A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Which is better: one or several requeries?



 
 
Thread Tools Display Modes
  #1  
Old March 26th, 2010, 04:22 AM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default Which is better: one or several requeries?

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
  #2  
Old March 26th, 2010, 05:52 AM posted to microsoft.public.access.gettingstarted
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default Which is better: one or several requeries?

Huh? Can you explain why you have 30 of something? Can you provide some
context or samples?

--
Duane Hookom
MS Access MVP

"forest8" wrote in message
...
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


  #3  
Old March 26th, 2010, 06:04 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Which is better: one or several requeries?

On Thu, 25 Mar 2010 20:22:01 -0700, forest8
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]
  #4  
Old March 26th, 2010, 03:58 PM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default Which is better: one or several requeries?

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
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]
.

  #5  
Old March 26th, 2010, 04:56 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Which is better: one or several requeries?

On Fri, 26 Mar 2010 07:58:03 -0700, forest8
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]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.