Opening a form dynamically using a fields text as a parameter

[Sam Summers]

Hi again,

Been searching for the answer for this one but can't seem to find something that solves it.
All I am trying to do is open a form dynamically.
I want the user to double click on the selected subforms and open an edit form that will add records to the subform.
I need therefore to open the edit form using the name field or the personID field to link the two forms.
The edit form is based on a Table but is currently set for Data entry?
I just can't quite seem to get it working at the moment.
It just opens blank or doesn't work and throws an error.

Thanks very much in advance

 

[stormin_norm]

How bout this:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=55438&highlight=OpenForm

 

[Sam Summers]

I think its close but now i am getting the 'openform action was cancelled'.

I am using this code -

DoCmd.OpenForm "Enter930", , , "InstructorID = '" & Me!InstructorID & "'"

Thanks

 

[stormin_norm]

need some brackets.
Also- If the ID is numeric, no need for the single quotes around the InstructorID.

Try:
DoCmd.OpenForm "Enter930", , , "[InstructorID] = '" & Me![InstructorID] & "'"

 

[Sam Summers]

Hi,

Still getting nothing, just a blank form and 'You entered an expression that has no value'.

I wonder if it could be due to the code that runs the main form -

Option Compare Database
Option Explicit

Private Sub Form_Current()

Dim db As DAO.Database
Dim rs As DAO.Recordset

On Error GoTo err_RSMoveError

Set db = CurrentDb
Set rs = db.OpenRecordset("WorkingSkiingInstructors", dbOpenDynaset)

If rs.EOF = True Then
MsgBox "There are no instructors working today", vbInformation, "Lecht Ski School Rota"
Else
If rs.BOF = False Then
Me.Instructor1.SetFocus
Me.Instructor1.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor2.SetFocus
Me.Instructor2.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor3.SetFocus
Me.Instructor3.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor4.SetFocus
Me.Instructor4.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor5.SetFocus
Me.Instructor5.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor6.SetFocus
Me.Instructor6.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor7.SetFocus
Me.Instructor7.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor8.SetFocus
Me.Instructor8.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor9.SetFocus
Me.Instructor9.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor10.SetFocus
Me.Instructor10.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor11.SetFocus
Me.Instructor11.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor12.SetFocus
Me.Instructor12.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor13.SetFocus
Me.Instructor13.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor14.SetFocus
Me.Instructor14.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor15.SetFocus
Me.Instructor15.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor16.SetFocus
Me.Instructor16.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor17.SetFocus
Me.Instructor17.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor18.SetFocus
Me.Instructor18.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor19.SetFocus
Me.Instructor19.Text = rs("InstructorName")
rs.MoveNext
Me.Instructor20.SetFocus
Me.Instructor20.Text = rs("InstructorName")
Else
End If
End If

err_RSMoveError:
' Error 3021 = "No Current Record"
If Err.Number = 3021 Then
Resume Next
End If

End Sub

I just wonder if the problems are rooted in the possible inheritence of this code?

 

[stormin_norm]

What is the recordsource (under properties) for the form? A query or a table or nothing?

Looks almost like you have an unbound form (no recordsource) and setting it during run time.

Also why so many instructor text boxes. Why not make a continuous form and have them displayed that way?

-norm.

 

[Sam Summers]

Hi there,

The Main Form is bound to a Table and each of the subforms are bound to a Query e.g.

SELECT Instructor.InstructorID, Instructor.InstructorName, Instructor.Working, Instructor.Discipline, Day.Day, [930].DayID, [930].Date, [930].DisciplineID, [930].LevelID, [930].Name, [930].Age, [930].Paid
FROM Instructor INNER JOIN (Discipline INNER JOIN (Day INNER JOIN 930 ON Day.DayID = [930].DayID) ON Discipline.DisciplineID = [930].DisciplineID) ON Instructor.InstructorID = [930].InstructorID
WHERE (((Instructor.InstructorName)=[Forms]![Skiing]![Instructor1]) AND ((Instructor.Working)=True) AND ((Instructor.Discipline)="Skiing"));

The reason for so many text boxes is that they want to constantly see what instrucors are available. So i have a row of textboxes across the top of the main form.

Thanks for all your time helping me with this.
I've got to crack it somehow?

 

[stormin_norm]

I am a bit confused due to the amount of detail with no "big picture". Keep it simple and avoid using all those text boxes. Rethink what you want to show. ex: main form shows all instructors and subform changes as user selects instructor on main form.

You should tie the main form to a query which already performs some filtering (such as your: (Instructor.Working=True) AND (Instructor.Discipline="Skiing"))
and one subform to display the detail of a particular instructor vs. all those boxes. It will also make the form look very "busy".

What happens when you get max(instructors)+1 in other words an additional instructor and you don't have enough text fields?

 

[Lynn_AccessUser]

Is this an mdb or adp?

Parameter suggests an adp . . . that is why I ask.

 

[Sam Summers]

Its an mdb Lynn.


Norm, i shall have a think about it.
The trouble is that they want to be able to constantly view the instructor status and all the classes at once. But there may be a better way.

As far as additional instructors are concerned they have allowed space for the future as they only currently have between 10 and 15 absolute max.

 

[Rich]

The reason for so many text boxes is that they want to constantly see what instrucors are available. So i have a row of textboxes across the top of the main form.

Why not just use a Listbox?

 

[Sam Summers]

Yeah a listbox could be used.
It was just that they requested to constantly be able to see who was available so that they could book people as they came in to that instructor fast. Because there is often a queue of people waiting and they may be of different abilities and therefore classes. They need to allocate people very quickly. Each instructor takes one level(ability) for that day but may have 6 classes a day.