PDA

View Full Version : Solved: open a form and go to a specified record



cassius
04-13-2005, 02:54 AM
I have a database to keep track of my mp3 collection. Amongst my tables I have the following:

tblSingers (SingerID, Singer etc)
tblAlbums (AlbumID, Album etc)
tblSongs (SongID, Song etc)
tblSongsSingers (SingerID, SongID)
tblSongsAlbums (SongID, AlbumID)

Eventually, from these tables i created the required queries and forms.

Now i created a new form for searching purposes, so in the form header i put some unbound combo boxes which are populated from the above mentioned tables (ex. the singers combo, has this rowsource
SELECT tblSingers.SingerID, tblSingers.Singer FROM tblSingers ORDER BY tblSingers.Singer; ... with a column count of 2, widths 0cm, 2.542cm). Eventually, when i choose a singer from this combo box i will requery the form, and it will display only those songs for the singer chosen. So far so good.

My problem now is here, i want that from the records which are displayed if i will double click on them, the main songs form will be opened and it will display the record in this form as well.

In similar situations I used to do it as follows:

Dim stLinkCriteria As String
stLinkCriteria = "[txtSongID] = " & Me.SongID
DoCmd.OpenForm "frmSongs", , , stLinkCriteria
However, this time first i am getting the parameter box to enter the txtSongID and then another parameter box prompting me for qrySongs.Singer and if in this boxes i will try to put some data, i get run time error 3009.

Any ideas what might be wrong pls ?

Thanks

OBP
04-13-2005, 10:11 AM
The parameter boxes are being generated by the Query behind the main songs form.
Have you opened the main songs form on it's own?
If so do you get the parameter boxes?
The other thing that may cause this problem is that you are using unbound combo boxes. You may need to pass the "selected value" from the combo to the field named [txtSongID] which should be on the form.
Is the Main songs form just for use with this form or is it used in it's own right?
If it is only for use with this form then an alternative is to put the [txtSongID] field in the Query's Criteria line.

Norie
04-13-2005, 05:52 PM
I don't know if this will help but this is some code I used to use to switch between forms.


Option Compare Database
Option Explicit

Function ChangeForms(ByRef NForm As String, ByRef OForm As String, Optional frmCriteria As String, _
Optional subForm As String, Optional subField As String, Optional subCriteria As String)
' this function will change from OForm to NForm
' the optional arguments allow the following
' frmCriteria - will open OForm to record according to this criteria
' subForm, subField, subCriteria - if Nform contains a subform with records
' related to a record on OForm then if these arguments are present (and correct)
' focus will move to related record on the subform

Dim subFind As Boolean
On Error GoTo Err_ChangeForms

If frmCriteria = "" Then
DoCmd.OpenForm NForm
Else
DoCmd.OpenForm NForm, , , frmCriteria
ChangeForms = True
GoTo Exit_ChangeForms
End If

subFind = Not (subForm = "" Or subField = "" Or subCriteria = "")

If subFind Then
If IsSubForm(NForm, subForm) Then
Forms(NForm).SetFocus
DoCmd.GoToControl subForm
DoCmd.GoToControl subField
DoCmd.FindRecord subCriteria
ChangeForms = True
End If
Else
ChangeForms = True
End If

Exit_ChangeForms:
If OForm <> NForm Then
If ChangeForms Then
DoCmd.Close acForm, OForm
Else
DoCmd.Close acForm, NForm
Forms(OForm).SetFocus
End If
End If

Exit Function

Err_ChangeForms:
MsgBox Err.Description, vbInformation, "Error message"
ChangeForms = False
Resume Exit_ChangeForms

End Function

cassius
04-13-2005, 11:18 PM
[
The parameter boxes are being generated by the Query behind the main songs form.
Have you opened the main songs form on it's own?
If so do you get the parameter boxes?

The Main Songs form is the main form, where a user will enter the data (i.e. a new song), in which case it opens without any problems (no parameter boxes).


Is the Main songs form just for use with this form or is it used in it's own right?


As stated above, it is the main source for data inputting, so yes it is used in it's own right.

p.s. Thank you Norie for your sample function, i will try to give a look at it as well. :)

OBP
04-14-2005, 01:12 AM
cassius, is your form in "continuous" or "single" form mode?
I have the VBA for a continuous form which works if you click on the record selector or on a label in the record.

cassius
04-14-2005, 01:55 AM
Hi,

The main form is a single form, however, the form from which i am double clicking is a continuous form. Actually, it seems that i've some kind of sorted out the problem.

The main issue was that after those parameter boxes, i started getting a run time error 3008, where this has resulted that the main form, had the record locks property set to All Records.

Now it seems that everything is working as i've wanted :)

However, i would like to thank you very much for your support and the willing for help (this goes to nori as well) :)

OBP
04-14-2005, 04:12 AM
Excellent, can you mark the thread as "solved" using the Thread Tools at the top of the forum page?