By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,425 Members | 2,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,425 IT Pros & Developers. It's quick & easy.

Selectinhg only filtered records (boolean?)

P: n/a
Hi everyone,

I am trying to open the form "Languages" with a diffrent record
source
to the "Contacts" form where I conducted the search or filter... .

I was wondering whether there was a vba code to select
ONLY ALL FILTERED records of my first form or the "Contacts"
and open this filtered set of records in my "Languages" form. I have a
key field on both tables named id_individual.

Most posts I have seen have only refered to linking just !!one
record!! to another form. I want to select all filtered records in my
continous form to pass on to another continous form.

Subsequently the idea would be to filter those records with the
selected languages.

At the moment I have been using unbound checkboxes with hiden
functions on the background to select each individual contact to be
stored as boolean. The selected records could then be opened in the
"language" form without any problem....

obviously this is a problem when there is over 100 contacts to select
from...
>>>>>>>>>>>>>here is extract of the code used to select the checkboxes of each individual to then pass on to another form.<<<<<<<<<<<<<<<<<<
' Assigns Boolean
Dim colCheckBox As New Collection

Public Function IsChecked(vID As Variant) As Boolean

Dim lngID As Variant

IsChecked = False

If IsNull(vID) = True Then Exit Function

For Each lngID In colCheckBox
If vID = lngID Then
IsChecked = True
Exit For
End If
Next lngID

End Function
-------------------------------

' some back end code
Private Sub Check11_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeySpace Then
KeyCode = 0
Call Command64_Click
End If

End Sub
------------------------------------------------
' This is the function placed behind the unbound checkbox found in the
Detail section of the form.
' This command saves the id.individual of the record selected as
boolean

Private Sub Command64_Click()

Debug.Print "id_individual = " & Me.id_individual

If IsChecked(Me.id_individual) = False Then
colCheckBox.Add CLng(Me.id_individual), CStr(Me.id_individual)
Else
colCheckBox.Remove (CStr(Me.id_individual))
End If
Me.check11.Requery

End Sub

--------------------------------------------------------------

' Function to collect the selected records

Private Function MySelected() As String
Dim i As Integer

For i = 1 To colCheckBox.Count
If MySelected <"" Then
MySelected = MySelected & ","
End If
MySelected = MySelected & colCheckBox(i)

Next i
End Function
---------------------------
' other backend code.. I assume to visualy select and deselect
checkboxes
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

' key hand

Select Case KeyCode

Case vbKeyUp
KeyCode = 0
On Error Resume Next
DoCmd.GoToRecord acActiveDataObject, , acPrevious

Case vbKeyDown
KeyCode = 0
On Error Resume Next
DoCmd.GoToRecord acActiveDataObject, , acNext

Case vbKeyReturn
If IsNull(Me.id_individual) = False Then
KeyCode = 0
Call EditMain
End If

End Select

End Sub

----------------------------------------------
' Finally the code to open the form with "Myslected" records
Private Sub expertise_Click()

'Dim strLinkCriteria As String

Dim strWhere As String
strWhere = MySelected

If strWhere <"" Then

strWhere = "id_individual in (" & strWhere & ")"

End If
DoCmd.OpenForm "expertise_search", acNormal, , strWhere


End Sub
----------------------------------------------------------------------------

<<<<<><<<<<<<<<<<

I am not... interested in using sub_forms.. for the simple fact you
cannot view records in a continous mode..
I am just looking for a solution iether by selecting all the
checkboxes of each filtered contact as boolean or by connecting i.e
all ID_contact(s) of one filtered form to another filtered form..
I hope I am not asking too much...

Aug 28 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Tue, 28 Aug 2007 06:47:09 -0700, Ironr4ge
<sc***************@yahoo.comwrote:

If I understand you correctly you would want to open L with a query
like this (L and C are forms and tables):
select * from L where ContactID in (select ContactID from C)

The way I do that is to first put the ContactIDs of the filtered C in
a "temp" table, and then inner join L with that table. This can be
done with a few lines of DAO code looping over C's recordsetclone.

-Tom.

>Hi everyone,

I am trying to open the form "Languages" with a diffrent record
source
to the "Contacts" form where I conducted the search or filter... .

I was wondering whether there was a vba code to select
ONLY ALL FILTERED records of my first form or the "Contacts"
and open this filtered set of records in my "Languages" form. I have a
key field on both tables named id_individual.

Most posts I have seen have only refered to linking just !!one
record!! to another form. I want to select all filtered records in my
continous form to pass on to another continous form.

Subsequently the idea would be to filter those records with the
selected languages.

At the moment I have been using unbound checkboxes with hiden
functions on the background to select each individual contact to be
stored as boolean. The selected records could then be opened in the
"language" form without any problem....

obviously this is a problem when there is over 100 contacts to select
from...
>>>>>>>>>>>>>>here is extract of the code used to select the checkboxes of each individual to then pass on to another form.<<<<<<<<<<<<<<<<<<
' Assigns Boolean
Dim colCheckBox As New Collection

Public Function IsChecked(vID As Variant) As Boolean

Dim lngID As Variant

IsChecked = False

If IsNull(vID) = True Then Exit Function

For Each lngID In colCheckBox
If vID = lngID Then
IsChecked = True
Exit For
End If
Next lngID

End Function
-------------------------------

' some back end code
Private Sub Check11_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeySpace Then
KeyCode = 0
Call Command64_Click
End If

End Sub
------------------------------------------------
' This is the function placed behind the unbound checkbox found in the
Detail section of the form.
' This command saves the id.individual of the record selected as
boolean

Private Sub Command64_Click()

Debug.Print "id_individual = " & Me.id_individual

If IsChecked(Me.id_individual) = False Then
colCheckBox.Add CLng(Me.id_individual), CStr(Me.id_individual)
Else
colCheckBox.Remove (CStr(Me.id_individual))
End If
Me.check11.Requery

End Sub

--------------------------------------------------------------

' Function to collect the selected records

Private Function MySelected() As String
Dim i As Integer

For i = 1 To colCheckBox.Count
If MySelected <"" Then
MySelected = MySelected & ","
End If
MySelected = MySelected & colCheckBox(i)

Next i
End Function
---------------------------
' other backend code.. I assume to visualy select and deselect
checkboxes
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

' key hand

Select Case KeyCode

Case vbKeyUp
KeyCode = 0
On Error Resume Next
DoCmd.GoToRecord acActiveDataObject, , acPrevious

Case vbKeyDown
KeyCode = 0
On Error Resume Next
DoCmd.GoToRecord acActiveDataObject, , acNext

Case vbKeyReturn
If IsNull(Me.id_individual) = False Then
KeyCode = 0
Call EditMain
End If

End Select

End Sub

----------------------------------------------
' Finally the code to open the form with "Myslected" records
Private Sub expertise_Click()

'Dim strLinkCriteria As String

Dim strWhere As String
strWhere = MySelected

If strWhere <"" Then

strWhere = "id_individual in (" & strWhere & ")"

End If
DoCmd.OpenForm "expertise_search", acNormal, , strWhere


End Sub
----------------------------------------------------------------------------

<<<<<><<<<<<<<<<<

I am not... interested in using sub_forms.. for the simple fact you
cannot view records in a continous mode..
I am just looking for a solution iether by selecting all the
checkboxes of each filtered contact as boolean or by connecting i.e
all ID_contact(s) of one filtered form to another filtered form..
I hope I am not asking too much...
Aug 28 '07 #2

P: n/a
On Aug 28, 4:33 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Tue, 28 Aug 2007 06:47:09 -0700, Ironr4ge

<scherrer_ricca...@yahoo.comwrote:

If I understand you correctly you would want to open L with a query
like this (L and C are forms and tables):
select * from L where ContactID in (select ContactID from C)

The way I do that is to first put the ContactIDs of the filtered C in
a "temp" table, and then inner join L with that table. This can be
done with a few lines of DAO code looping over C's recordsetclone.

-Tom.

Thanks for the reply Tom,

This would be a perfect solution which I had not fully envisioned
before. I will do some research into this method today. However, if
you know (or anyone for that matter) a particular post which explains
this step by step, I would realy appreciate if you posted a link.

Cheers,

Ricky,

Aug 29 '07 #3

P: n/a
On Aug 29, 11:14 am, Ironr4ge <scherrer_ricca...@yahoo.comwrote:
On Aug 28, 4:33 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Tue, 28 Aug 2007 06:47:09 -0700, Ironr4ge
<scherrer_ricca...@yahoo.comwrote:
If I understand you correctly you would want to open L with a query
like this (L and C are forms and tables):
select * from L where ContactID in (select ContactID from C)
The way I do that is to first put the ContactIDs of the filtered C in
a "temp" table, and then inner join L with that table. This can be
done with a few lines of DAO code looping over C's recordsetclone.
-Tom.

Thanks for the reply Tom,

This would be a perfect solution which I had not fully envisioned
before. I will do some research into this method today. However, if
you know (or anyone for that matter) a particular post which explains
this step by step, I would realy appreciate if you posted a link.

Cheers,

Ricky,
Ya sorry.. forgot to ask will this method be viable in a multi user
environment.?'

Aug 29 '07 #4

P: n/a
On Wed, 29 Aug 2007 03:09:56 -0700, Ironr4ge
<sc***************@yahoo.comwrote:

Yes. You either put the temp table in the front-end, or (e.g. if SQL
Server is your BE and you're in an ADP) you add a column MachineName
to the temp table.

-Tom.

>On Aug 29, 11:14 am, Ironr4ge <scherrer_ricca...@yahoo.comwrote:
>On Aug 28, 4:33 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Tue, 28 Aug 2007 06:47:09 -0700, Ironr4ge
<scherrer_ricca...@yahoo.comwrote:
If I understand you correctly you would want to open L with a query
like this (L and C are forms and tables):
select * from L where ContactID in (select ContactID from C)
The way I do that is to first put the ContactIDs of the filtered C in
a "temp" table, and then inner join L with that table. This can be
done with a few lines of DAO code looping over C's recordsetclone.
-Tom.

Thanks for the reply Tom,

This would be a perfect solution which I had not fully envisioned
before. I will do some research into this method today. However, if
you know (or anyone for that matter) a particular post which explains
this step by step, I would realy appreciate if you posted a link.

Cheers,

Ricky,

Ya sorry.. forgot to ask will this method be viable in a multi user
environment.?'
Aug 29 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.