subforms

  • Thread starter Thread starter SPMU
  • Start date Start date
S

SPMU

In my main form I can search for a particular field (Salesman) and then the
subform brings up all records referring to that particular Salesman. I want
to then pick a particular record in this subform and then bring up a form to
modify that particular record. I'm not quite sure what I'm suppose to do.
Please help.
 
Since you give no details about what the two forms data comes from it is
hard to give advice, but if the subform shows all the fields you want to
edit you shoul be able to edit the information right there.

One possibility is to design a regular form, based on the same table or
query as the subform. Once you have designed it, delete the form's
RecordSource property. This is so when you open the form it does not have to
retrieve all the records in the underlying table or query.

You could create a command button on your main foirm that opens your edit
form, or you could use the DoubleClick event for the subform.

Then create a query string by designing a query in the query design tool
that select the same data. Disply the query in SQL view and cut and paste
the query into the form's Form_Open event sub. Add a WHERE clause to select
only the record you have selected in the subform.
You would need to decide which field in the subform uniquely defines the
record, for example a SalemanID, which could be hidden if you don't want to
display it. The WHERE clause would be created by appending it, something
like this:
"............ WHERE MyTable.SalemanID = " & Forms!MySubForm.Form!SalesmanID
& ";"
If the salesman ID was 21 the above will evaluate to
............ WHERE MyTable.SalemanID = 21;"
This is on the assumption that the SalemanID field in the table is a numeric
field. If it is a text field you need to add a single quote between the =
and the ", like this = '", and also between the " and the ; at the end of
the query string, like this "';". This is because when you use text as
select criteria in a query the spec need to be sourrounded by single quotes.
In that case, if the samlesman ID was ABROWN, the string would evaluate to:
............ WHERE MyTable.SalemanID = 'ABROWN';"

Once you have generated the query string you would assing it as the
RecordSource property of the edit form, like this
Me.RecordSource = strSQL, where strSQL is the string variable that holds the
query string

This will cause the edit form to show only the record you want to edit.

Ragnar
 
Back
Top