Whilst in a form - open another form and click to bring data across?

[kate10123]

Hi there,

I have a form called frm_tutor which has a subform called frm_tutorial1. When the user adds a new record they may click a button to search for a student. This opens another form called frm_StudentSearch. I want the user to be able to double click a search result and the firstname, lastname and studentID to appear in frm_tutor!frm_tutorial1.form.

My problem is I don't know where to put the codes for this to happen as it can't be 'on load' as the original form will be open in the background and it is only if the user needs to find a student.

I can add an attachment if that would help.

 

[MagicMan]

Hi,
If I read into your request correctly, you have a Pop-up frm_StudentSearch triggered by a command. If you open the form with WindowMode:=acDialog, when that form is closed control will return to the vb statement immediately following the DoCmd.Openform. If you use temporary variables to hold the student fields, they can be referenced in the frm_tutor!frm_tutorial1.form vb code to populate the form fields.
EG: In frm_StudentSearch set TempVars("Student") = strStudent (or Me.Student) and in frm_tutor!frm_tutorial1.form Me.Student = Tempvars!Student. Hope this helps.
Bob

 

[kate10123]

But surely if I reference the fields in frm_tutor then any other time that the search button is not used these fields will have #Error! in them won't they?

 

[wazz]

can you change the textboxes in the subform to a single combobox that lists all the students? then the user can search that list instead of a popup.

 

[kate10123]

The studentsearch form has a listbox (called QuickSearch) that the user can use to search for a student - I have a textbox that they can type in a surname, firstname or ID to find the student and any matches appear in this listbox.

On clicking the listbox item I want the StudentID to populate the StudentID in the frm_tutor!frm_tutorial1.form!StudentID textbox

 

[MagicMan]

I am assuming the tables to be:
Tutor - TutorID, TudorFirstName, TutorLastName, availability etc
Tutorial - TutorID, Subject(to be tutored) (multiple items), StudentId, StudentFirstName, StudentLastName
If more than one student can be tutored in the subject, then you would have a third table TutorID & SubjectID & StudentID as the key.
In any case, the student info fields would remain as nulls, when not selected. If the form fields are unbound, the fields can be populated on the screen when selected and BeforeUpdate for the form, you can test the fields for nulls (NZ) and update the table. If you wish them to be bound, they should appear as spaces when the student search is not performed. In addition, the 3 student fields could made invisible when not populated and set to visible when a student is selected (Me.StudentID.Visible = True). There are many ways to handle your requirement (including opening the student form with a parameter which on return can be split(fields delimited with a comma and use the Split command to reference them on return). I think the first response is the easiest to implement quickly and should work fine.
Smiles Bob
Editted.....follows
I read the other responses which happened while I was typing this. I have a very similar popup form for inventory lookup multiple ways (UPC/Description1/Description2) and when selected from any of the combo searches, populates my text boxes. I use tempvars to pass the text box values back to the calling screen and trigger a new db entry.

 

[kate10123]

Thanks for taking the time to reply to my post. I have tried to use the TempVar to pass the studentID from the listbox to the studentID textbox on the frm_tutor form but I am not sure if I have put the correct expression term in macro.

Could you possibly take a look at the attachment? frm_tutor is where it all happens - from here you click 'find student' and then this brings up the pop up form from which a user can click an item.

 

[wazz]

you were searching the search form itself instead of the tutorial form.
i also changed the search to look for an id-field (Me.QuickSearch.Column(2)) instead of a name field which could have duplicates.

    With Forms!frm_tutor!tutorial.Form
        'DoCmd.Requery
        .RecordsetClone.FindFirst "studentid = " & Me.QuickSearch.Column(2)
'        .RecordsetClone.FindFirst "[Surname] = '" & Me![QuickSearch] & "'"
        If Not .RecordsetClone.NoMatch Then
           .Bookmark = .RecordsetClone.Bookmark
        Else
           MsgBox "Could not locate [" & Me![QuickSearch] & "]"
        End If
    End With

 

[kate10123]

Sorry where does that code go? The search form has the search code which works fine. The bit I am stuck with is the QuickSearch click event when I try to bring the value across from this search form to the tutorial form.

 

[wazz]

the code above would go on the QuickSearch_AfterUpdate() event but will only find existing students on frmTutor.

This? :

Private Sub QuickSearch_AfterUpdate()
    Forms!frm_tutor!tutorial.Form!StudentID = Me.QuickSearch.Column(2)
End Sub

make sure the user is on a new record or the current one will be changed.

 

[kate10123]

Brill! thanks alot!

 

[wazz]

no prob. do you see how it works?

 

[kate10123]

yeah I think I was getting confused as to which form was the focus point, it is a lot clearer with the QuickSearch event. I assumed it would have to be placed in the click event.

 

[wazz]

hmph. i'm sure it could go on the click event. not sure how it wound up there. either way for this...

(actually i was referring to '= Me.QuickSearch.Column(2)'. the id-field is in the second column of your search listbox (columns are counted 0,1,2,etc.)) ...

 

[MagicMan]

Thanks Wazz....TempVars is a Access 2007 feature. Im sorry I did not ask which version and I went to bed soon after the last post. Having several virus problems reported today, so I was late getting back to the forums. Kate...nice code...you almost had Wazz's answer (in your commented code). This webpage has simple examples of how to refer to fields across forms, a good reference. http://www.accessdb.info/content/view/112/45/

 

[wazz]

hi magicman. no probs. it's getting tricky with the different versions. i'm unfamiliar with 2007.
i was hoping you would keep posting, taking the 'design' angle and i would keep posting with some simple options to 'make it go', with the hope that kate10123 would get something out of it from both angles. tnx. c ya.