Problem with search form

Hi All,
I am creating a music database for a friend and have run into a problem.
Within the ALBUM table, I wanted to store the ARTIST_ID rather than the
ARTIST_NAME. To do this, I intended to have have a command button on the
album form which would open a search form (based on the artist table). This
works as I wanted and allows me to get to one record. I then planned to have
a button on the search form which when clicked, would updated the album form
with the selected artist ID.

I could hard code this but I want to use this search form for other forms
also so I need to know which form to set the value on. I was hoping to use
the openargs value of the search form as I could set this from the form that
opened the search form but I can't figure out how to use this to set the
selected value on whatever form opened it.

I hope this is clear??

Can anyone help?

TIA,

Mark

Re: Problem with search form

When you open the search form using OpenForm, use the following argument:

WindowMode:=acDialog

This causes the code to suspend execution at the OpenForm statement, waiting
for the opened form (the search form) to be closed.

On your search form, you would typically have two buttons, a Cancel button
and an OK button. The Cancel button closes the search form. The OK button
(and this is crucial!) renders the search form invisible by setting it's
Visible property to False. *Both of these actions will cause the code in
the album form to begin running again*.

Now back in the album form, you test the search form to see if it is still
open. If it is open, you grab the artist_id from it, and then close it. If
it's not open, you know that the user clicked the Cancel button and you
don't have an artist_id.

Example:

DoCmd.OpenForm "frmSearch", WindowMode:=acDialog
If adhIsFormOpen("frmSearch") Then
Me!artist_id = Forms!frmSearch!txtArtistId
DoCmd.Close acForm, "frmSearch"
End If

And here's the function adhIsFormOpen (courtesy of the Access Developers'
Handbook):

Public Function adhIsFormOpen( _
strName As String) As Boolean
' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert (Sybex)
' Copyright 1999. All rights reserved.

On Error Resume Next
Dim fIsOpen As Boolean
fIsOpen = _
CurrentProject.AllForms(strName).IsLoaded
adhIsFormOpen = (Err.Number = 0) And fIsOpen
Err.Clear
End Function

"Mark" wrote in message
news:cOKdnXEztZq24OjanZ2dnUVZ8sOonZ2d@bt.com...
> Hi All,
> I am creating a music database for a friend and have run into a
> problem. Within the ALBUM table, I wanted to store the ARTIST_ID rather
> than the ARTIST_NAME. To do this, I intended to have have a command button
> on the album form which would open a search form (based on the artist
> table). This works as I wanted and allows me to get to one record. I then
> planned to have a button on the search form which when clicked, would
> updated the album form with the selected artist ID.
>
> I could hard code this but I want to use this search form for other forms
> also so I need to know which form to set the value on. I was hoping to use
> the openargs value of the search form as I could set this from the form
> that opened the search form but I can't figure out how to use this to set
> the selected value on whatever form opened it.
>
> I hope this is clear??
>
> Can anyone help?
>
> TIA,
>
> Mark
>

Re: Problem with search form

Many thanks for the help Baz, worked a treat!!!

Regards,

Mark

"Baz" wrote in message
news:4775dee9$0$13932$fa0fcedb@news.zen.co.uk...
> When you open the search form using OpenForm, use the following argument:
>
> WindowMode:=acDialog
>
> This causes the code to suspend execution at the OpenForm statement,
> waiting for the opened form (the search form) to be closed.
>
> On your search form, you would typically have two buttons, a Cancel button
> and an OK button. The Cancel button closes the search form. The OK
> button (and this is crucial!) renders the search form invisible by setting
> it's Visible property to False. *Both of these actions will cause the
> code in the album form to begin running again*.
>
> Now back in the album form, you test the search form to see if it is still
> open. If it is open, you grab the artist_id from it, and then close it.
> If it's not open, you know that the user clicked the Cancel button and you
> don't have an artist_id.
>
> Example:
>
> DoCmd.OpenForm "frmSearch", WindowMode:=acDialog
> If adhIsFormOpen("frmSearch") Then
> Me!artist_id = Forms!frmSearch!txtArtistId
> DoCmd.Close acForm, "frmSearch"
> End If
>
> And here's the function adhIsFormOpen (courtesy of the Access Developers'
> Handbook):
>
> Public Function adhIsFormOpen( _
> strName As String) As Boolean
> ' From Access 2000 Developer's Handbook, Volume I
> ' by Getz, Litwin, and Gilbert (Sybex)
> ' Copyright 1999. All rights reserved.
>
> On Error Resume Next
> Dim fIsOpen As Boolean
> fIsOpen = _
> CurrentProject.AllForms(strName).IsLoaded
> adhIsFormOpen = (Err.Number = 0) And fIsOpen
> Err.Clear
> End Function
>
> "Mark" wrote in message
> news:cOKdnXEztZq24OjanZ2dnUVZ8sOonZ2d@bt.com...
>> Hi All,
>> I am creating a music database for a friend and have run into a
>> problem. Within the ALBUM table, I wanted to store the ARTIST_ID rather
>> than the ARTIST_NAME. To do this, I intended to have have a command
>> button on the album form which would open a search form (based on the
>> artist table). This works as I wanted and allows me to get to one record.
>> I then planned to have a button on the search form which when clicked,
>> would updated the album form with the selected artist ID.
>>
>> I could hard code this but I want to use this search form for other forms
>> also so I need to know which form to set the value on. I was hoping to
>> use the openargs value of the search form as I could set this from the
>> form that opened the search form but I can't figure out how to use this
>> to set the selected value on whatever form opened it.
>>
>> I hope this is clear??
>>
>> Can anyone help?
>>
>> TIA,
>>
>> Mark
>>
>
>