Open a form record from another form (1 Viewer)

[maestro83]

Hi,

I have 2 forms.

The first is called frmStudentDetails, this form is the main form where i edit student details and add new records.

The second form is a dialog box I created, called frmFindStudent, that has a combo box and a command button. The combobox is linked to my students names in frmStudentDetails and I want the command button to, on click, open frmStudentDetails at the record corresponding to the students name selected in the combobox.

I think what I need is a wherecondition but I am unsure as to how to link the wherecondition to another form.

Is this possible or is there an easier way to search for a record to update it.

I'm fairly new to this and please don't hesitate to ask for more info.

Thanks,
Matt

 

[micks55]

Hi Matt. Is this any help. First soultion doesn't use two forms, it uses a combo in the header of the student form to locate the selected record.

You have a two column combo with ID and Name then you hide the first column by setting its width to 0cm. I have called it cboStudent.
In the AfterUpdate event of the combo put something like this
Private Sub cboStudent_AfterUpdate()
If Me!cboStudent > "" Then
Me.Filter = "" 'these two lines remove
Me.Requery 'any previous search
Me.RecordsetClone.FindFirst "[StudentID] = " & Me!cboStudent
Me.Bookmark = Me.RecordsetClone.Bookmark
DoCmd.GoToControl "StudentName" 'change/delete this if you dont want it
Me!cboStudent = Null 'delete this if you dont want to reset the conbo
End If
End Sub

Alternatively you can open a second form with a where condition like this
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmStudent" 'keep the quotes
stLinkCriteria = "[StudentID]=" & Me!cboStudent 'keep the quotes and the ID must be in the first column of the combo
DoCmd.OpenForm stDocName, , , stLinkCriteria 'notice the three ,,,

Hope it helps, Mike

 

[maestro83]

Thanks heaps, I got it working better but now instead of showing the students name it shows a somewhat random number or the StudentName or just a blank space. EG

NAME
NAME
NUMBER
BLANK
ID
BLANK
NUMBER

any ideas

thanks in advance
Matt

 

[maestro83]

Heh ignore that, somehow the data in the StudentName field changed itself to either nothing or a random number.

While I have you here I cant get my table to combine the fields for FirstName and LastName to automaticaly enter them as StudentName, I have a Title on my form like so =[FirstName] & " " & [LastName] but this doesnt work as the default value for the Student name field, how can I do this? for testing purposes I have just manually been entering their fullnames to get the search working

cheers matt

 

[micks55]

I would not have a field called StudentName as this is duplicating the contents of FirstName and LastName and you can always join them on the fly just as you have for the title.

You could use an unbound text box with = FirstName & " " & LastName
In queries (ie: the one that is the rowsource of you combo) you can use StudentName: FirstName & " " LastName.

If you do want a field to hold the full name, put the code in the AfterUpdate of both the FirstName and LastName fields. (In AfterUpdate) Me!StudentName = Me!FirstName & " " & Me!LastName.

 

[maestro83]

Thanks for your help, I got it working thanks to you

Now if only it would let me import the data from Excel...
When I try to import the data to an existing table it gives me an error with no message other then the import failed. I tried importing the data to a new table but this time it adds 65000 odd records

What am I doing wrong?

Cheers Matt

 

[micks55]

65000 sounds like the max lines in an excel sheet so no solution comes to mind. if it helps, send me the excel sheet (or post it here) and i'll see if i can import it to a dummy table then you could use an append query from that. sorry but won't be back online till at least tuesday.

 

[maestro83]

Hey Mick

I worked out the problem. My ID field had autonumber and primary key so i deleted the field and ran a macro that select the fields like A1:O398 with contains field names(identical to my table) and it worked like a charm.

Once all data was there i re-added the ID field with primary key and autonumber and hey presto

Cheers for your help
Matt