Select button which copies an entry to another form

[Christine Wurtz via AccessMonster.com]

Hello
Don't know if what i'd like to do is even possible, and my skills in VBA
being microscopic, i submit my request, and maybe someone will be able to
help.

I have following :

Table : TBLSites with fields SiteCode SiteName BeginDate EndDate, and
entries like for example :
PBS1 | Perth Boy School | 01/01/05 | 31/01/05
PBS2 | Paris Boy School | 15/01/05 | 30/06/05
PBS3 | Perth Boy School | 05/05/05 | 07/07/05

Form : F-SiteSearch (unbound) with 2 unbound textboxes SiteCode and
SiteName. THis is a form used to enter values which will be retrieved by
query Q-SiteSearch

Query : Q-SiteSearch (based on TBLSites) with all fields of TBLSites and
following criteria :
on SiteCode : Like[Forms]![F-SiteSearch]![SiteCode] or Is Null
on SiteName : Like ("*" & [Forms]![F-SiteSearch]![SiteName] & "*") or Is
Null
Typically my users will search sites on a part of the name, for example
keyword "boy" or keyword "perth". There is a form F-SitesResults which is
going to list the result.

Form : F-SiteResults, based on Q-SiteSearch (and for each record i have a
command button which opens another form for more info from my db).

MY QUESTION :
Typicall someone will open F-SiteSearch, enter a part of the name in
SiteName, click OK -> this, thru the query, opens the form F-SiteResults
with matching records. Example keyword "boy" will render all 3 records from
TBLSites.
What i would like to do is set a command button called "select" which will
copy the value of SiteCode from this form, close the F-SiteResults form,
come back to F-SiteSearch and paste the selected SiteCode in the field
SiteCode from F-SiteSearch.

So, very generally, with form2 based on form1 (and a request), is it
possible via a select button to populate a field of form1 and close form2 ?

Thank you very much for any help
Christine

 

[Nikos Yannacopoulos]

Christine,

I'll assume:
(a) a command button called cmdSearch on F-SiteSearch, and
(b)a listbox called lstSites on F-SiteResults, with its rowsource
originally being TBLSites.
The idea is that upon clicking cboSearch, the second form opens and the
listbox is populated with the search results; then the user simply
double-clicks on their selection and the job is done (no need for a
separate button; you can just have a label for the user under the list,
reading something like "double-click on your selection" or something...).

Now, the code behind cmdSearch sould be something like:

Private Sub cboSearch_Click()
Dim strOpArg As String
If Not IsNull(Me!SiteCode) Then
strOpArg = "SiteCode = '" & Me!SiteCode & "'"
End If
If Not IsNull(Me!SiteName) Then
If Not IsNull(strOpArg) Then
strOpArg = strOpArg & " And "
End If
strOpArg = strOpArg & "SiteName Like '*" & Me.SiteName & "*'"
End If
DoCmd.OpenForm "F-SiteResults", , , , , , strOpArgs
End Sub

This will open FSearchResults, passing the filter string in the
OpenArgs. In F-SearchResults open event, the following code will filter
the listbox based on the criteria passed through the OpenArgs:

Private Sub Form_Open(Cancel As Integer)
strRowSource = "SELECT * FROM TBLSites"
If Not IsNull(OpenArgs) Then
strRowSource = strRowSource & " WHERE " & OpenArgs
End If
Me!lstSites.RowSource = strRowSource
End Sub

Finally, you need an event procedure behind the listbox's On Double
Click event (or behind a separate command button, if you prefer that
approach

Private Sub lstSites_DblClick(Cancel As Integer)
Forms!F-SiteSearch!SiteCode = Me.lstSites
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Note: the above assumes (a) all fields from TBLSites in the listbox, and
(b) SiteCode being the bound column in the listbox.

HTH,
Nikos

Hello
Don't know if what i'd like to do is even possible, and my skills in VBA
being microscopic, i submit my request, and maybe someone will be able to
help.

I have following :

Table : TBLSites with fields SiteCode SiteName BeginDate EndDate, and
entries like for example :
PBS1 | Perth Boy School | 01/01/05 | 31/01/05
PBS2 | Paris Boy School | 15/01/05 | 30/06/05
PBS3 | Perth Boy School | 05/05/05 | 07/07/05

Form : F-SiteSearch (unbound) with 2 unbound textboxes SiteCode and
SiteName. THis is a form used to enter values which will be retrieved by
query Q-SiteSearch

Query : Q-SiteSearch (based on TBLSites) with all fields of TBLSites and
following criteria :
on SiteCode : Like[Forms]![F-SiteSearch]![SiteCode] or Is Null
on SiteName : Like ("*" & [Forms]![F-SiteSearch]![SiteName] & "*") or Is
Null
Typically my users will search sites on a part of the name, for example
keyword "boy" or keyword "perth". There is a form F-SitesResults which is
going to list the result.

Form : F-SiteResults, based on Q-SiteSearch (and for each record i have a
command button which opens another form for more info from my db).

MY QUESTION :
Typicall someone will open F-SiteSearch, enter a part of the name in
SiteName, click OK -> this, thru the query, opens the form F-SiteResults
with matching records. Example keyword "boy" will render all 3 records from
TBLSites.
What i would like to do is set a command button called "select" which will
copy the value of SiteCode from this form, close the F-SiteResults form,
come back to F-SiteSearch and paste the selected SiteCode in the field
SiteCode from F-SiteSearch.

So, very generally, with form2 based on form1 (and a request), is it
possible via a select button to populate a field of form1 and close form2 ?

Thank you very much for any help
Christine

 

[Christine Wurtz via AccessMonster.com]

Dear Nikos,

Unfortunately there it is not a listbox which displays the result on my
result form, myabe i should have informed about that :-/
I explain again, and try to be more precise. Can also send you a little db
made especially for that question, if it can help. So, below, how i create
things.

TABLE :
TBLSites with following fields
[SiteCode][SiteName] [BeginDate] [EndDate],
[SiteCode] is text, field size 8
[SiteName] is text, field size 255
[BeginDate] and [EndDate] are date fields

QUERY :
Q-SiteSearch based on table TBLSites, with all fields of that table.

FORM :
F-SiteResults, based on Q-SiteSearch, continuous form, with all the fields
used. This is gonna display all records this far.

Now, what i want to do, is that this form F-SiteResults only displays the
records matching certain criteria. 2 fields might be used : [SiteCode] OR
[SiteName]. Unfortunately people do generally only remember a part of the
name in SiteName. So i create a new form, F-SiteSearch

FORM F-SiteSearch : , unbound form, with 2 textboxes, called
[SiteCodeSearch] and [SiteNameSearch] and a command button called
[ValidateSearch] to validate and open form F-SiteResults. Remember that F-
SiteResults is linked to query Q-SiteSearch, so in order that what the user
enters on the F-SiteSearch form being taken in account by the query, i
enter 2 criteria on this query.

QUERY :
Q-SiteSearch, criteria are :
on [SiteCode] : Like[Forms]![F-SiteSearch]![SiteCode] or Is Null
on SiteName : Like ("*" & [Forms]![F-SiteSearch]![SiteName] & "*") or Is
Null

Typically the user will enter, on F-siteSearch, a part of a name in
[SiteName] and leave initially [SiteCode] blank (as he doesnt always
remember the code), and click the command button [ValidateSearch]. This
info is recovered by the query Q-SiteSearch and the continuous form F-
SiteResult is opened, displaying all matching records (aka the example i
gave in my first post, there could be a few records containing the word
"boy" but the SiteCode of each record is unique). On the F-SiteResults form
i put a command button called "Select" in front of each record (one button
in the detail section of the form, so that it appears in front of each
record).
So what i would like to do is that the user clicks on "select" for one of
the records listed on F-SiteResults, which would select the SiteCode of the
matching record, somehow copy it, close the form F-SiteResults, come back
on F-SiteSearch which was still open, and paste it in the unbound field
[SiteCodeSearch]. Then user clicks the command button {ValidateSearch]
which resubmits the query, opens form F-SiteResults and displays the result
with the unique record.
I can only fix existing VBA code, but im unable to create the code for this
command button "Select".

So Nikos, i hope this helps you more. Again sorry, i didnt mention there
wasnt any listbox.

With regards,
Christine

 

[Nikos Yannacopoulos]

Christine,

I'm the one who should apologize for not making this clearer in the
first place; what I meant was, forget the continuous forms and the
repeating command buttons for each record, and just add a listbox to the
form, which is easier to do and has a more "professional" look. In this
case, the query Q-SiteSearch is not required at all.

Also, I fail to see the logic behind going back to the original form,
pasting the SiteCode and re-opening the second form?! Is that so the
site details can be edited? If yes, then my proposed approach would
involve (a) the unbound form with the listbox for the user to select a
site, and (b) a bound form (on TBLSites) in normal view (one record at
the time), opened from the previous one directly on the selected record
(in which case there is no need to "paste" the SiteCode in the original
form).

If you still want to do it the way you started out to, it would help if
you posted the SQL for the second form's recordsource.
In any case, you are welcome to mail me your database if you want.

HTH,
Nikos

Dear Nikos,

Unfortunately there it is not a listbox which displays the result on my
result form, myabe i should have informed about that :-/
I explain again, and try to be more precise. Can also send you a little db
made especially for that question, if it can help. So, below, how i create
things.

TABLE :
TBLSites with following fields
[SiteCode][SiteName] [BeginDate] [EndDate],
[SiteCode] is text, field size 8
[SiteName] is text, field size 255
[BeginDate] and [EndDate] are date fields

QUERY :
Q-SiteSearch based on table TBLSites, with all fields of that table.

FORM :
F-SiteResults, based on Q-SiteSearch, continuous form, with all the fields
used. This is gonna display all records this far.

Now, what i want to do, is that this form F-SiteResults only displays the
records matching certain criteria. 2 fields might be used : [SiteCode] OR
[SiteName]. Unfortunately people do generally only remember a part of the
name in SiteName. So i create a new form, F-SiteSearch

FORM F-SiteSearch : , unbound form, with 2 textboxes, called
[SiteCodeSearch] and [SiteNameSearch] and a command button called
[ValidateSearch] to validate and open form F-SiteResults. Remember that F-
SiteResults is linked to query Q-SiteSearch, so in order that what the user
enters on the F-SiteSearch form being taken in account by the query, i
enter 2 criteria on this query.

QUERY :
Q-SiteSearch, criteria are :
on [SiteCode] : Like[Forms]![F-SiteSearch]![SiteCode] or Is Null
on SiteName : Like ("*" & [Forms]![F-SiteSearch]![SiteName] & "*") or Is
Null

Typically the user will enter, on F-siteSearch, a part of a name in
[SiteName] and leave initially [SiteCode] blank (as he doesnt always
remember the code), and click the command button [ValidateSearch]. This
info is recovered by the query Q-SiteSearch and the continuous form F-
SiteResult is opened, displaying all matching records (aka the example i
gave in my first post, there could be a few records containing the word
"boy" but the SiteCode of each record is unique). On the F-SiteResults form
i put a command button called "Select" in front of each record (one button
in the detail section of the form, so that it appears in front of each
record).
So what i would like to do is that the user clicks on "select" for one of
the records listed on F-SiteResults, which would select the SiteCode of the
matching record, somehow copy it, close the form F-SiteResults, come back
on F-SiteSearch which was still open, and paste it in the unbound field
[SiteCodeSearch]. Then user clicks the command button {ValidateSearch]
which resubmits the query, opens form F-SiteResults and displays the result
with the unique record.
I can only fix existing VBA code, but im unable to create the code for this
command button "Select".

So Nikos, i hope this helps you more. Again sorry, i didnt mention there
wasnt any listbox.

With regards,
Christine

 

[Christine Wurtz via AccessMonster.com]

Nikos,

I can't use a listbox, precisely because the user needs to search on a PART
of the content of the field SiteName, word which is not necessarly at the
beginning of the field (then it would make it much easier).

I think if you'd see the db and content of the records, you'd have an idea
of what i'd like to do. Do you mind sending me a mail at my address
ck.wurtz[AT]wanadoo[DOT]fr, so i can send you the db ? Not big, zipped it's
about 130 Kb.

Regards,
Christine

 

[Nikos Yannacopoulos]

Christine,

The place of the sting being searched inside the actual site name is
irrelevant, since we are using a:
.... Like '*" & Me.Something & "*' ....
That's exactly why it is enclosed in the asterisks.

I'll have a look at your .mdb though.

Nikos