General Form & Query VB question

In essence, what i am try to say is, instead of selecting just one record is it possible to select more than one
record and send these as parameters to a query??
Gaz

yes it is possible.

you need to loop through the selected items in your combo and build a wherclause for your sql by appending selected items

Something like this Gaz,


Dim i as Integer
Dim sWhere as string
Dim sql as string

sql = "SELECT SiteInformation.SiteID, SiteInformation.SiteLocation, SiteInformation.Blocked, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated FROM SiteInformation"
With Forms!Form2
  With .ComboFormat2
     For Each i In .ItemsSelected
       sWhere = sWhere & .Column(2, i) & ", " 
    Next i
  End With
End With

sWhere = Left(sWhere , Len(sWhere ) - 2) ' drop last comma and space
sWhere = " WHERE SiteInformation.SiteType In( " & sWhere  & ")"
sql = sql & sWhere






Alan

if you use a list box inside the form with your data is displayed inside the list box including the primary key field then you can multi select and append the pk field for the records selected into an empty and temp table, that table can be used in your query to only populate the seleced records, I can help further with great details

cheers

ok Alan
thanks for the reply
first things first i am not a programmer but i do understand a good bit of code
currently the form allows 3 parameters to be sent to the query, as laid out below
the bit inside the >>>>> is where i am guessing i need to incorporate the code you
show(by the way i only 60% understand your code sorry!)
i know that you will understand the code below better than i
can you help some more
gaz

Private Sub Submit_Click()
Dim strWhere As String

strWhere = ""
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SiteLocCombo has now been replaced with the subform 'TestMulBook'

'If Not IsNull(Me.SiteLocCombo2) Then
   'strWhere = strWhere & " [SiteLocation]= '" & Me.SiteLocCombo2 & "' And "
'End If
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
If Not IsNull(Me.CycListCombo2) Then
  strWhere = strWhere & " [CycleID]= " & Me.CycListCombo2 & " And "
End If
 If Not IsNull(Me.YearCombo2) Then
  strWhere = strWhere & " [BookYear]= " & Me.YearCombo2
End If
 If strWhere <> "" Then
     strWherer = Left$(strWhere, Len(strWhere) - 5)
     
 End If
If CDate(Trim(Me.TextStartDate2) & Trim(Me.YearCombo2)) < Date Then
      MsgBox "The CycleID and Corresponding Date Selected is Passed, Please select a Future CycleID and Date"
  Me.CycListCombo2.SetFocus
  Exit Sub
  End If
 
DoCmd.OpenForm "NewBookForm", , , strWhere
DoCmd.Close acForm, "Form2"

 End Sub

all help is welcome Ahmedbahgat
thanks
gaz

Hi Gaz,

In the code you just posted which combo contains multiple selected items?

Alan

Alan, how u doin mate?, just a quick question is multi select is avaulable for comboboxes or only for listboxes?


cheers

Hi gaz,

it just occured to me; you cannot have multi selected items in a combo box, you can in a listbox though.

Alan

shhhh... dont tell anyone Ahmed  <blush>

cheers alan, this is what I expected, so I will give my solution based on a listbox

cheers

maybe we should just confirm with Gaz, that we are dealing with a listbox
somewhere in this scenario before we continue.

Alan

actually guys i thought that i couldn't use multiselect with a combobox
but instead of using a list box i tried to use a sub form instead
however, i can easily change it to a listbox if you are sure that i can get it to work
gaz

Hi gaz,

Re: the first Combobox(ComboFormat2  see code below) in the form determines the values available in the secondcombobox

Maybe the ideal senario here would be if the second combobox was in fact a list box, the filtering functionality you described would still work the same, but then you would be able to select multiple items from the listbox to build your wherecondition for you openorm action.


Alan

right i will get on that asap and get back to you
asap
gaz

Ok friends, I just did this the other day, the idea is to have a temp table "MyTempTable" that will have only one field, "MyPKField", this field will be a PK and  will store the primary keys for the selected records in a list box, the listbox bound column should be the primary key field for the displayed records

1. we will need to empty the temp table before we start appending the PK values for the selected items in the listbox:
   CurrentDB.Execute "DELETE MyTempTable.* FROM MyTempTable;"

2. we will loop through the selected items and as we loop we append the pk for each selected record to our temp table

I will assume the listbox name is "MyListbox" and here is the code for looping and appending to the temp table:

Dim rs As RecordSet
Set rs = CurrentDB.OpenRecordSet("SELECT MyTempTable.* FROM MyTempTable;")
Dim frm As Form, ctl As Control
Dim varItm As Variant
Set ctl = Forms!MyForm!MyListBox
For Each varItm In ctl.ItemsSelected
    rs.AddNew
    rs!MyPKField= ctl.ItemData(varItm)
    rs.Update
Next varItm
rs.Close

after step 2 is finished we have all the PK of the list box selected records, inside our temp table, and all you need to do is add this table to any query and link the only field in there with the PK field in the main table

cheers

What would you be going to name your listbox?
Think you can actually Right click the combobox and choose Change To > ListBox
Then rename it lstSomething

You will then need to modify the afterupdate event for your first combo so it filters the listbox
just need to change where it says cboSomename to lstSomeName, should still work.


Alan

Thats good Ahmed,

The resulting sql would then need to look something like this

SELECT SiteInformation.SiteID, SiteInformation.SiteLocation, SiteInformation.Blocked, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated FROM SiteInformation
WHERE SiteInformation.SiteType IN(Select Distinct MyPKField From MyTempTable)

yes?

Alan

Enjoy collaborating with you Ahmed,

Can you take it from her mate?, gotta go get some smokes from the shop.

you're in good hands Gaz..

Alan

right so i have discarded the subform and now i have replaced it with a list box
called 'SiteLocList'

here is the submit code again with the changes
but how do i incorporate your code to allow multiple selection so that the parameter SiteLocList is
actually more than one parameter value

Private Sub Submit_Click()
Dim strWhere As String

strWhere = ""
If Not IsNull(Me.SiteLocList) Then
   strWhere = strWhere & " [SiteLocation]= '" & Me.SiteLocList & "' And "
End If
If Not IsNull(Me.CycListCombo2) Then
  strWhere = strWhere & " [CycleID]= " & Me.CycListCombo2 & " And "
End If
 If Not IsNull(Me.YearCombo2) Then
  strWhere = strWhere & " [BookYear]= " & Me.YearCombo2
End If
 If strWhere <> "" Then
     strWherer = Left$(strWhere, Len(strWhere) - 5)
     
 End If
If CDate(Trim(Me.TextStartDate2) & Trim(Me.YearCombo2)) < Date Then
      MsgBox "The CycleID and Corresponding Date Selected is Passed, Please select a Future CycleID and Date"
  Me.CycListCombo2.SetFocus
  Exit Sub
  End If
 
DoCmd.OpenForm "NewBookForm", , , strWhere
DoCmd.Close acForm, "Form2"

 End Sub

hey Ahmed
the sql posted is not the sql for the query
it was the row source sql that populates the list box
sorry if i have confused the situation with this but i thought that i had said that earlier

SELECT SiteInformation.SiteID, SiteInformation.SiteLocation, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated FROM SiteInformation WHERE (((SiteInformation.SiteType)=Forms!Form2!ComboFor));

in my code 'strWhere' is actually made up of the three parameters sent to a query
SiteLocList
CycListCombo2
YearCombo2
 as it stands SiteLocList is one value, just want to be able to send more than one value as paramters for this

i hope that this is clearer
can you see what i mean??
gaz

Gaz, just arrived home, I will look at it soon and get back to you

Alan, yeh mate I will do my best but you are still in it when you have spare time smoker

cheers

cool
thanks
;0)
gaz

Hi gaz,

where ya up to?

have you set the Multi Select property setting of the listbox to 'Simple' yet?
What is the name of your listbox?
Is it on the same form as Command button Submit ?

Alan

Hey Gar, I think I helped with this a day ago or so, ok to put this into prespective, I'm slightly confused with what you want to do, howverer l will explain an important issue, so you may need to change your existing logic of passing parameters to the existing query

1. if we have a table called "Sites" and this table has a primary key field called "SiteID" which is possibly an auto number, if we create the following query:

SELECT Sites.* FROM Sites;

it will return all records in the table Sites so if we have 5 records in there, all 5 records will be returned, also assume the site ID for these 5 records are 1,2,3,4,5

2. now assume we have annother table called "SitesTemp", that only has one field which is the primary key as well, this field is a number type  and only have 2 records which are 1,2

3. if we create the following query :

SELECT Sites.*
FROM Sites INNER JOIN SitesTemp ON Sites.SiteID = SitesTemp.SiteIDTemp;

it will return only 2 records and not 5, because in this query we have both tables "Sites" and  "SitesTemp" linked via SiteID=SiteIDTemp

4. so the idea is to use a temp table which we empty first then append the selected items PK from the listbox to it

5. so we will end up with a temp table with a few records that each one is representing one of the selected items in our listbox

6. adding this SitesTemp table to any query we have that contains the Sites table and both fields "SiteID" and "SiteIDTemp" are linked, we will force the query to return only records from the sites table that exist in the sitestemp table, in anoter way it is like we are saying I need all sites that have siteid=bla  or siteid=blabla   or siteid=blablabla
so we are simulating passing more than one parameter that is dynamically dependant on the selected items on our list box

I'm not concerned about the first combo and the second listbox and how they relate togther as this should not interefere with what we want to do with the selected items on the listbox

the most important note will be for our listbox, the bound column must be the PK

please explain in great details what you want to do after selecting a few items from the listbox

cheers

another quetion Gar

I noticed you want to open the form NewBookForm and with criteria string that you compose, but how the listbox multi select should affect opening that form??


cheers

Hi Guys,

' Im up to here
' happy to go the way of using a temp table
' prefer using sub-selects instead of joins, because we can then use the same temp table again
' WHERE SiteInformation.SiteType IN(Select Distinct MyPKField From MyTempTable)
' If we open a form with a rowsource like this, we will be free to blow away the contents of the temp table
' and the form will be updatable.

' feed-back :)


Private Sub Submit_Click()

  CurrentDb.Execute "DELETE MyTempTable.* FROM MyTempTable;"
 
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("SELECT MyTempTable.* FROM MyTempTable;")
  Dim frm As Form, ctl As Control
  Dim varItm As Variant
  Set ctl = Me!MyListBox
  For Each varItm In ctl.ItemsSelected
      rs.AddNew
      rs!MyPKField = ctl.ItemData(varItm)
      rs.Update
  Next varItm
  rs.Close

End Sub

sorry
about the delay guys
thunderstorm last night and the electricity went out for a long while
don't mean to waste your time, anyway here i go at answering your queries regarding the thread

"have you set the Multi Select property setting of the listbox to 'Simple' yet?
What is the name of your listbox?
Is it on the same form as Command button Submit ?"

yes i have, and the listbox is called SitLocList and yes it is on the same form as the Submit command button, alan.

ok Ahmed i will try and explain this better, sorry if i have made it difficult to understand.

The form has:

 label                  ComboBox
|Site Format|    |ComboFor|

label               listBox
|Sites|         |SiteLocList|      >>>>>>>>>>>>> the values displayed in this Box are determined by the values selected in the ComboFor box

label                ComboBox
|CycleID|      |CycListCombo2|

label               ComboBox
|Year|          |YearCombo|

on submit
the values from the SitLocList, CycListCombo2 and YearCombo are passed to the query  as follows: (the where clause is
of most relevance and as it now stands this query will return just one value)

SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated, SiteBookings.CycleID, CDate([CycleStartDate] & [BookYear]) AS CycleStart, CDate([CycleEndDate] & [BookYear]) AS CycleEnd, SiteBookings.Price, SiteBookings.BookYear, SiteBookings.CustomerID, SiteBookings.ProductName, SiteBookings.ProductCategory, SiteBookings.Price, SiteBookings.CompanyName, SiteBookings.CompanyAddress, SiteBookings.City, SiteBookings.Country, SiteBookings.PostalCode, SiteBookings.PhoneNumber, SiteBookings.FaxNumber, SiteBookings.EmailAddress, SiteBookings.CampaignRef, SiteInformation.Blocked, SiteBookings.BookDate

FROM SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID

WHERE (((SiteInformation.SiteLocation)=[Forms]![Form1]![SiteLocList]) AND ((SiteBookings.CycleID)=[Forms]![Form1]![CycListCombo]) AND ((CDate([CycleStartDate] & [BookYear]))>Now()) AND ((SiteBookings.BookYear)=[Forms]![Form1]![YearCombo]) AND ((SiteBookings.CustomerID) Is Null) AND ((SiteBookings.ProductName) Is Null) AND ((SiteBookings.ProductCategory) Is Null) AND ((SiteBookings.CampaignRef) Is Null) AND ((SiteInformation.Blocked)=No));

instead of passing one value for the 'SiteLocList' i would ideally like to select several, regarding the 'NewBookForm' i would make it tabular to display all the returned records!

hope this helps further,
Gaz

ok, so logically i understand exactly how the temp table works
seems like a good idea
but i am not a programmer, cept for a little java and will probably need a little help
gaz
:)

hey ahmed
regarding the "MyPKField" should i call it SiteID also or should give it another name??
gaz

I really don't know how to incorporate this code you posted:

Dim rs As RecordSet
Set rs = CurrentDB.OpenRecordSet("SELECT MyTempTable.* FROM MyTempTable;")
Dim frm As Form, ctl As Control
Dim varItm As Variant
Set ctl = Forms!MyForm!MyListBox
For Each varItm In ctl.ItemsSelected
    rs.AddNew
    rs!MyPKField= ctl.ItemData(varItm)
    rs.Update
Next varItm
rs.Close

into the code i originally had on the submit button:

Private Sub Submit_Click()
Dim strWhere As String

strWhere = ""
If Not IsNull(Me.SiteLocList) Then
   strWhere = strWhere & " [SiteLocation]= '" & Me.SiteLocList & "' And "
End If
If Not IsNull(Me.CycListCombo2) Then
  strWhere = strWhere & " [CycleID]= " & Me.CycListCombo2 & " And "
End If
 If Not IsNull(Me.YearCombo2) Then
  strWhere = strWhere & " [BookYear]= " & Me.YearCombo2
End If
 If strWhere <> "" Then
     strWherer = Left$(strWhere, Len(strWhere) - 5)
     
 End If
If CDate(Trim(Me.TextStartDate2) & Trim(Me.YearCombo2)) < Date Then
      MsgBox "The CycleID and Corresponding Date Selected is Passed, Please select a Future CycleID and Date"
  Me.CycListCombo2.SetFocus
  Exit Sub
  End If
 
DoCmd.OpenForm "NewBookForm", , , strWhere
DoCmd.Close acForm, "Form2"

 End Sub

pls help
gaz

hey guys don't give up on me yet
pls help
gaz

Hi gaz,

just need about 10 minutes to wake up mate.

hey gar, sorry for the delay, the wife in hospital for a week now and been flat out, we will sort it out mate so do not worry, we have not given up yet and possibly never

it seems after reading your detailed explaination that the Temp Table is the way to go, so give me a few mins to rethink and see how it can be implemented with what you already have

cheers

Gi Gaz,

in the Where part of your sql you refer to a control called: CycListCombo

is this now called CycListCombo2

WHERE (((SiteInformation.SiteLocation)=[Forms]![Form1]![SiteLocList]) AND ((SiteBookings.CycleID)=[Forms]![Form1]![CycListCombo]) AND ((CDate([CycleStartDate] & [BookYear]))>Now()) AND ((SiteBookings.BookYear)=[Forms]![Form1]![YearCombo]) AND ((SiteBookings.CustomerID) Is Null) AND ((SiteBookings.ProductName) Is Null) AND ((SiteBookings.ProductCategory) Is Null) AND ((SiteBookings.CampaignRef) Is Null) AND ((SiteInformation.Blocked)=No));


Alan

sorry guys didn't realise that you guys where around
thanks for the help so far
let me try and answer your queries
first
>>>>>in the Where part of your sql you refer to a control called: CycListCombo?

yes it is, alan
Ahmed, sorry to here about your other half, hope that all is well :)


just give me a moment to read your last post and figure out what you have done
i want to learn this stuff so it is good for me to have a full understanding

Gaz

this is step 4
of your suggestion ahmed
the query, just wondered if it was correct???
btw you guys are great
thanks a lot!!!
Gaz:)

SELECT SiteBookings.BookingsNo, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated, SiteBookings.CycleID, CDate([CycleStartDate] & [BookYear]) AS CycleStart, CDate([CycleEndDate] & [BookYear]) AS CycleEnd, SiteBookings.Price, SiteBookings.BookYear, SiteBookings.CustomerID, SiteBookings.ProductName, SiteBookings.ProductCategory, SiteBookings.Price, SiteBookings.CompanyName, SiteBookings.CompanyAddress, SiteBookings.City, SiteBookings.Country, SiteBookings.PostalCode, SiteBookings.PhoneNumber, SiteBookings.FaxNumber, SiteBookings.EmailAddress, SiteBookings.CampaignRef, SiteInformation.Blocked, SiteBookings.BookDate

FROM MyTempTable, SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID

WHERE (((SiteBookings.SiteID)=[MyTempTable]![MyTempField]) AND ((SiteBookings.CycleID)=[Forms]![Form2]![CycListCombo2]) AND ((CDate([CycleStartDate] & [BookYear]))>Now()) AND ((SiteBookings.BookYear)=[Forms]![Form2]![YearCombo2]) AND ((SiteBookings.CustomerID) Is Null) AND ((SiteBookings.ProductName) Is Null) AND ((SiteBookings.ProductCategory) Is Null) AND ((SiteBookings.CampaignRef) Is Null) AND ((SiteInformation.Blocked)=No));

hey
Ahmed
I get a runtime type mismatch error at this line in your code

Set rs = CurrentDb.OpenRecordset("SELECT MyTempTable.* FROM MyTempTable;")

now SiteBookings.SiteID is a Number Datatype and MyTempTable.MyTempField is also set as a Number Datatype
and i don't understand the error

any ideas
gaz

I think the problem in your query will be in this bit:

FROM MyTempTable, SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID

it seems to me that there in no link between the MyTempTable and te proper table in your query, I believe what you have missed is after adding MyTempTable to your query you need to drag MyTempField in it on top of the related field in another table in your query

I just can not come up with the syntax because it seems you have many tables in your query, so the way to do it, is to drag that Field "The only field in the temp table" on op of the related field possibly the SiteID in Site Booking Table

cheers

for the error make sure you have DAO 3.6 liberary included, ie open any module in design and make sure the MS DAO x.xx is registered and ticked

cheers

the record source for SiteLocList is:
SELECT SiteInformation.SiteID, SiteInformation.SiteLocation, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated FROM SiteInformation WHERE (((SiteInformation.SiteType)=Forms!Form2!ComboFor));

when i created this list box the wizard asked if i wanted the primary key SiteID hidden, would this cause a problem??
 by the way the Bound Column is 1
which should be the SiteID right??

i can't think of anything else right now
pls let me know if you spot what it may be!!
Cheers
Gaz

the listbox specs seems right to me, the quickest way now to fix this is to email me the database and I will fix it quick, just point me to where this code will be and what version of access

cheers

ok ahmed i did what you said with the drag and drop
in query design mode
but in access it just moves aside and creates room for the MyTempTable and Its Field
however, i created a join between MyTempTable.MyTempField and SiteBookings.SiteID
this is what i got, is this what you mean:

SELECT SiteBookings.BookingsNo, MyTempTable.MyTempField, SiteBookings.SiteID, SiteInformation.SiteLocation, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated, SiteBookings.CycleID, CDate([CycleStartDate] & [BookYear]) AS CycleStart, CDate([CycleEndDate] & [BookYear]) AS CycleEnd, SiteBookings.Price, SiteBookings.BookYear, SiteBookings.CustomerID, SiteBookings.ProductName, SiteBookings.ProductCategory, SiteBookings.Price, SiteBookings.CompanyName, SiteBookings.CompanyAddress, SiteBookings.City, SiteBookings.Country, SiteBookings.PostalCode, SiteBookings.PhoneNumber, SiteBookings.FaxNumber, SiteBookings.EmailAddress, SiteBookings.CampaignRef, SiteInformation.Blocked, SiteBookings.BookDate
***********************************************************************************************
FROM MyTempTable INNER JOIN (SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID) ON MyTempTable.MyTempField = SiteInformation.SiteID
*************************************************************************************************

WHERE (((SiteBookings.CycleID)=[Forms]![Form2]![CycListCombo2]) AND ((CDate([CycleStartDate] & [BookYear]))>Now()) AND ((SiteBookings.BookYear)=[Forms]![Form2]![YearCombo2]) AND ((SiteBookings.CustomerID) Is Null) AND ((SiteBookings.ProductName) Is Null) AND ((SiteBookings.ProductCategory) Is Null) AND ((SiteBookings.CampaignRef) Is Null) AND ((SiteInformation.Blocked)=No));

The version of access is Access 2002
you will find the SQL in NewSAQuery2
the Forms are Form2 and NewBookForm2
i will zip it up and email it to you asap if you are sure that that is ok??
btw thanks and i think that the DAO library 3.6 is included

Gaz:)

***********************************************************************************************
FROM MyTempTable INNER JOIN (SiteInformation INNER JOIN (CycleInformation INNER JOIN SiteBookings ON CycleInformation.CycleID = SiteBookings.CycleID) ON SiteInformation.SiteID = SiteBookings.SiteID) ON MyTempTable.MyTempField = SiteInformation.SiteID
*************************************************************************************************

this looks much better, now what is the problem, as a way to troubleshoot, when you run the code under the submit button if you do ot receive an error, can you check the table "MyTempTable" and see if there are records in there matching the number of records you selected in the listbox

cheers

Gar,please remeber after you zip it, change the file extension from zip to aaa as zip files are not allowed through my mail server

cheers

>>>>>>>>>"this looks much better, now what is the problem, as a way to troubleshoot, when you run the code under the submit button if you do ot receive an error, can you check the table "MyTempTable" and see if there are records in there matching the number of records you selected in the listbox"

i did but there where no numbers
in the field, which would suggest that it is not sending the numbers from the form to the MyTempTable.MyTempField right?
gaz
p.s. if you still want me to send the DB i will need your email address

yeh mate you are right if records are not there, please email it to me

ahmed@epilepsy.org.au

cheers

sorry ahmed,  but how do change the file extension
i really am clueless!
jeeesh!!!!
gaz

right i sent it but
i don't think that it worked right
i just changed the file extension to aaa in the attachment file
and that won't work
i feel like a twat!
gaz

for the runtime error

change this
Dim rs As RecordSet
to
Dim rs As DAO.RecordSet

Acc 2002 has ADO has default reference so you need to explicitly define DAO or Access will try to use ADO which has different parameters for it's Open method.



Alan

ok do not worry about renaming it send it as zip, I know where the server will block it and i will get it, however you will receieve a notification that the attchment was blocked so ignore it

cheers

it is on its way ahmed
if you don't get it i can upload it to a geocities server and you can download it from there!
gaz

alan i will make the change and see how i get on
thanks for the help

by the way 500 points to both you guys for this

Very kind offer gaz but...

I dont want points, Ahmed has done ALL the work he deserves ALL the points.
The mods and page eds git a bit iffy about posting points for - questions.
And seeing I am a PE now... dillema. LOL

Alan

hey guys
with alan's change it works

excellent
you two rock

the only thing that seems to be amiss is that it doesn't clear the MyTempTable.MyTempField
for every new search and it displays the previous search data as well as the current search data
gaz

hey ahmed did you get it yet
the first one was just me being stupid
disregard it
but the second one was the real deal

hey Ahmed, i sent it again
just in case
but i have dial-up so give it a few minutes
gaz

Did you run the delete sql as Ahmed suggested in his earlier posts?

CurrentDB.Execute "DELETE MyTempTable.* FROM MyTempTable;"

Alan

Alan, no worries
no doubt i will get an opportunity to reward you again
but the offer still stands
and you have helped a lot
gaz
:)

oops!
doh! i was looking for that
but won't it delete the table altogether, Alan??????

did not get it yet, but glad to hear it is working now, look 250 to alan and 250 to me is not a bad idea

cheers

I'll get ya next time round mate :)

no it will not delete the table, just the records inside


cheers

just received it now, I will still have a quick look and give you my feed back


cheers

ok guys (i can't believe that you didn't get it yet, it has just sent for the second time!!)
with the table delete it works fine, thank you for your patience, i know that i am a pain in the butt
i can give you both 500, you deserve it
i can open another thread and have a link to the question
i have done it before and it was ok
thanks ever so much
Alan look out for a thread called Attention of Alan!
gaz

i will award points 2moro
it is late and i should really go to bed
thank you for you time guys
and feedback would be greatly appreciated
Slan Abhaile
Gaz

cheers gar and alan,

Gar, would you like me to have a look at it still, or you are fine now, well I'm happy to answer any further question related to this TA

Alan, thanks for your help with this question, also I just sent the final version "Of the database i told you about" to my client and waiting for the last feedback "I had 3 feedbacks so far", after then I can continue the matter I raised with you a couple of weeks ago


cheers

Cool Ahmed,

at your leisure my freind.

Alan

no problem Ahmed, if you want to look at it you can, i am appreciative either way

sorry Alan, i opened another thread for you to get points but the MAN wouldn't let
me do it, apparently they have discontinued this practice.
i will get you again mate

cheers Guys
you guys are belter!!!
:)
Gaz