recordset in excel-like form

[NT100]

Hi,

I need to use VBA to build an interface for display the reccordset in excel-like format, it allows a selection of a particular entry in the recordset for editing and its associated one-to-many values display in a subform.

I would be grateful if you've any idea/sample for this.

Thank you in advance.

NT100

 

[June7]

Why do you need this? I don't expect it will be simple.

Provide example raw data and example of what you want as a form.

 

[NT100]

Why do you need this? I don't expect it will be simple.

Provide example raw data and example of what you want as a form.

The end users prefer the excel-like format.


tblTutor -> ID, LastName, FirstName, Specialist (=key in tblSpecialty),
ID LastName FirstName, Specialist
3 Carol Miguel 2
5 Tim Buffet 4

tblSpecialty -> ID, SDesc
ID SDesc
1 Specialist
2 Non specialist – with HKAM recognized intermediate equivalent qualification(s)
3 Non-Specialist – with quotable postgraduate qualification(s)
4 Non-Specialist – without quotable postgraduate qualification(s)
5 Non-Clinical qualification(s)

Requirements
1) an excel-like structure for recordset from tblTutor,
2) a click on a particular record in the recordset, the subform of excel-like structure for the tutor's specialists display.
3) allow recordset and subform entries update/addition

I wish this may help clarify the requirements.

 

[June7]

Not much. Did not provide mockup of the 'Excel-like' format. Exactly what would this look like? Regardless, it will be complex coding and I am not interested in doing it for you. Don't think I've ever seen a db that does this.

Users should really be educated and re-oriented to a relational database structure. Otherwise, maybe just stick with Excel workbooks.

 

[arnelgp]

you can use the datasheet form.

 

[June7]

Okay, maybe I misunderstood what 'Excel-like' means. Yes, a datasheet form looks like an Excel worksheet grid but do not expect it to act like a worksheet.

If you do not want to 'de-normalize' the data, then yes, the data can be simply displayed in form set to Datasheet view or use Continuous view and arrange controls to look like datasheet.

 

[NT100]

That's great! main form is default view to "datasheet". However, subform can't be displayed in main form's default view of datasheet.

Can I set default view both main form and subform to default view of "datasheet"?

 

[isladogs]

Access does allow the use of subdatasheets which allow you to 'drill down' from one table to one or more linked tables. The method has its uses but can also be confusing for end users.

When you set up a Subdatasheet, a + sign appears next to each record. Click the + to see the associated record(s) in the linked table.

 

[arnelgp]

2) a click on a particular record in the recordset, the subform of excel-like structure for the tutor's specialists display.

you can make the "subform" as Pop up datasheet.

 

[arnelgp]

... or you can create a Form with two subforms.
one subform for tblTutor and another for tblSpecialty.
add the two subform to a New Form.
add an unbound textbox to the New Form.
on the current event of the New Form, set the unbound textbox
you also need to create another field in tblSpecialty (foreign key to Specialty field of tblTutor).
that way you can make a Master/Child Field Link to both tblTutor subform and tblSpecialty subform.

 

[Rafal]

You can use split form for the main form - this way you can have main records as datasheet and additionally active record can be seen in heading as a set of preferred size and format boxes. And by command e.g. dbl-click on the cell of datasheet (or in heading) you can open each record as a pop-up window of records from subform releted to this. Instead of pop-up normal full size form can be opened related to one record from main form and all records from subform - subform in this case may be set to be displayed as a continuous or datasheet form. Closing pop-up or normal (full) form, you will be back in spleet form of main form. Save records commands are important to do before and after opening subform view.