Solved: open a form and go to a specified record

[cassius]

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]

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]

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]

[

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]

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]

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]

Excellent, can you mark the thread as "solved" using the Thread Tools at the top of the forum page?