Solved

General Form & Query VB question

Posted on 2004-04-13
70
568 Views
Last Modified: 2008-03-03
Hello
I have a form that when the user selects data from comboboxes and hits submit
it runs a query and opens the data in another form

the first Combobox(ComboFormat2  see code below) in the form determines the values available in the secondcombobox
however, this only allows me to search for one record at a time
i have tried to put a subform in the form that has a row source
as follows:
SELECT SiteInformation.SiteID, SiteInformation.SiteLocation, SiteInformation.Blocked, SiteInformation.SiteType, SiteInformation.Prismatic, SiteInformation.Illuminated FROM SiteInformation WHERE SiteInformation.SiteType=Forms!Form2!ComboFormat2;
and it works in displaying the values, the question I would like to be able to highlight or select several records from this subform
and on submit send them as parameters of the query as opposed to just one record
is this possible????
i can send all the code i have so far if needed!
Gaz
0
Comment
Question by:Gar04
  • 32
  • 20
  • 18
70 Comments
 

Author Comment

by:Gar04
ID: 10819486
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10819494
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







0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10819499
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
0
 

Author Comment

by:Gar04
ID: 10819530
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
0
 

Author Comment

by:Gar04
ID: 10819574
all help is welcome Ahmedbahgat
thanks
gaz
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10819575
Hi Gaz,

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

Alan


0
 
LVL 16

Expert Comment

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


cheers
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10819582
Hi gaz,

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

Alan
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10819587
shhhh... dont tell anyone Ahmed  <blush>
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10819588
cheers alan, this is what I expected, so I will give my solution based on a listbox

cheers
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10819599
maybe we should just confirm with Gaz, that we are dealing with a listbox
somewhere in this scenario before we continue.

Alan
0
 

Author Comment

by:Gar04
ID: 10819618
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10819626
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
0
 

Author Comment

by:Gar04
ID: 10819641
right i will get on that asap and get back to you
asap
gaz
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10819646
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10819647
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10819672
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10819684
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
0
 

Author Comment

by:Gar04
ID: 10819768
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

0
 

Author Comment

by:Gar04
ID: 10820012
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
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10820094
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
0
 

Author Comment

by:Gar04
ID: 10820192
cool
thanks
;0)
gaz
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10820258
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
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10820291
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
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10820308
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10820336
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
0
 

Author Comment

by:Gar04
ID: 10825014
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
0
 

Author Comment

by:Gar04
ID: 10825288
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
:)
0
 

Author Comment

by:Gar04
ID: 10825450
hey ahmed
regarding the "MyPKField" should i call it SiteID also or should give it another name??
gaz
0
 

Author Comment

by:Gar04
ID: 10826237
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

0
 

Author Comment

by:Gar04
ID: 10828938
hey guys don't give up on me yet
pls help
gaz
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10828964
Hi gaz,

just need about 10 minutes to wake up mate.
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10828979
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10829056
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
0
 
LVL 16

Accepted Solution

by:
ahmedbahgat earned 500 total points
ID: 10829062
ok step 1 we need to remove the SiteLocList  from your Where Clause, the reason is we will use our Temp Table, ie:

strWhere = ""
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"


step 2, make sure you created our temp table, table name is "MyTempTable" and has one field that s PK and its name is "MyTempField" and the field data type should be the same the Primary Key of the data displayed in the Listbox

for example if the PK of the sites is string, then the field in our temp table should be string
if the PK of the sites is Auto Number, then the field in our temp table should be a NUMBER "Important"

step 3 we insert my bit of code within your code that I modified in step 1 so the result will be:


Private Sub Submit_Click()
Dim strWhere As String

strWhere = ""
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

If Me.SiteLocList = -1  Then ' this line will check if the user did not select at least one record from the listbox'
      MsgBox "Please select at least one site"
      Me.SiteLocList .SetFocus
      Exit Sub
End If

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!SiteLocList
For Each varItm In ctl.ItemsSelected
    rs.AddNew
    rs!MyTempField= ctl.ItemData(varItm)
    rs.Update
Next varItm
rs.Close

DoCmd.OpenForm "NewBookForm", , , strWhere
DoCmd.Close acForm, "Form2"


Step 4, we need to add our temp table to the query of your Form "NewBookForm",  ie find the record source for that form, if it is a query then open in design and add our temp table, then drag the field "MyTempField" from our temp table on top of the equevelent field in the main table

if your form is based on a table then create a query that is based on that table and our temp table, remember to drag the related fields from the 2 tables on top of each pther ie a line should be there between the 2 fields

hope this will make it clearer

cheers

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Gar04
ID: 10829765
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
0
 

Author Comment

by:Gar04
ID: 10829851
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));
0
 

Author Comment

by:Gar04
ID: 10829881
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
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10829891


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
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10829900
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
0
 

Author Comment

by:Gar04
ID: 10829920
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
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10829950
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
0
 

Author Comment

by:Gar04
ID: 10829955
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));
0
 

Author Comment

by:Gar04
ID: 10829971
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:)
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10829975
***********************************************************************************************
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
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10829979
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
0
 

Author Comment

by:Gar04
ID: 10829982
>>>>>>>>>"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
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10829987
yeh mate you are right if records are not there, please email it to me

ahmed@epilepsy.org.au

cheers
0
 

Author Comment

by:Gar04
ID: 10830007
sorry ahmed,  but how do change the file extension
i really am clueless!
jeeesh!!!!
gaz
0
 

Author Comment

by:Gar04
ID: 10830025
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10830043
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
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10830048
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
0
 

Author Comment

by:Gar04
ID: 10830064
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10830081
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



0
 

Author Comment

by:Gar04
ID: 10830090
hey guys
with alan's change it works

excellent
you two rock

0
 

Author Comment

by:Gar04
ID: 10830100
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
0
 

Author Comment

by:Gar04
ID: 10830118
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
0
 

Author Comment

by:Gar04
ID: 10830130
hey Ahmed, i sent it again
just in case
but i have dial-up so give it a few minutes
gaz
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10830139


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

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

Alan
0
 

Author Comment

by:Gar04
ID: 10830147
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
:)
0
 

Author Comment

by:Gar04
ID: 10830161
oops!
doh! i was looking for that
but won't it delete the table altogether, Alan??????
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10830162
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10830163
I'll get ya next time round mate :)
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10830165
no it will not delete the table, just the records inside


cheers
0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10830168
just received it now, I will still have a quick look and give you my feed back


cheers
0
 

Author Comment

by:Gar04
ID: 10830189
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
0
 

Author Comment

by:Gar04
ID: 10830204
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





0
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 10838037
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10838053
Cool Ahmed,

at your leisure my freind.

Alan

0
 

Author Comment

by:Gar04
ID: 10838657
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
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now