subform w/ datasheet view

[NeoFax]

I would like to build a form that has a subform (datasheet view) and 4
comboboxes(used to filter the subform if desired). On open the
subform pulls with no filter. However, the data I want displayed is
currently in two different tables and I would like to update the
information in the one table. The first table contains all open work
for the production floor and the second table holds all of the issues
found by the technicians for each work instruction. I can combine the
info via a query, but this will not allow the data entered to update
the table. How would I go about accomplishing this?

 

[Tom van Stiphout]

On Wed, 14 Jan 2009 17:37:14 -0800 (PST), NeoFax <[email protected]>
wrote:

A subform in datasheet view cannot additionally show 4 comboboxes.
Sorry, that's just a limitation of datasheet view you will have to
work around. Perhaps you would consider Continuous View.

On the topic of a subform with data from two tables: it is MUCH easier
to create two subforms, one for each table. Put them in a tab control
so the user can easily switch between the two.

-Tom.
Microsoft Access MVP

 

[NeoFax]

A subform in datasheet view cannot additionally show 4 comboboxes.
Sorry, that's just a limitation of datasheet view you will have to
work around. Perhaps you would consider Continuous View.

On the topic of a subform with data from two tables: it is MUCH easier
to create two subforms, one for each table. Put them in a tab control
so the user can easily switch between the two.

-Tom.
Microsoft Access MVP




- Show quoted text -

I guess I didn't explain myself well enough. Here is what I would
like to have:

Helo: ===^ (Dropdown box)
Station: ===^ (Dropdown box)
Job Card: ===^ (Dropdown box
Helo Station Operation Job Card Status Description Issue
------------------------------------------------------------
<Helo Station Op Job Card Status Description> Issue
<Helo Station Op Job Card Status Description> Issue
<Helo Station Op Job Card Status Description> Issue
<Helo Station Op Job Card Status Description> Issue
<Helo Station Op Job Card Status Description> Issue
<Helo Station Op Job Card Status Description> Issue
<Helo Station Op Job Card Status Description> Issue

The info above the break is the main form and below the subform. The
info between the brackets is from tblMECAPPDump and should not be
updateable, just viewable. The column issue is the info I want
updated/appended into tblIssue along with the PrimaryKeyID from
tblMECAPPDump so they are relatable. If I cannot accomplish this with
a datasheet view, continuous form is fine. I can also use VBA code to
do this if it is the easiest way to accomplish it. I hope this
explains things better.

 

[Tom van Stiphout]

Thanks for the additional information. The 4 dropdowns on the main
form, plus datasheet view should work just fine. You may want to add a
button "Apply Filter". Then in that button's click event concatenate
the filter expression, and assign it to the subform. Something like
(I'm making up some object names):

dim strFilter as string
'NOTE: assuming Helo is a number and Station is a string (needs to be
wrapped in single-quotes)
strFilter = "Helo=" & Me.cboHelo & _
Station='" & Me.cboStation & "'" 'etc.

With Me.mySubformControl.Form
.Filter = strFilter
.FilterOn
end with

On how to keep the subform updatable (with regards to the Issue
field): I have had good luck with creating a solution along these
lines:
Create a query and select only the Issue table. Put the IssueID and
Issue in the grid.
Make this query the RecordSource for your subform. Bind the Issue
control to the Issue field (ControlSource property).
For the other fields, use DLookup to pull the values. That will also
automatically make them readonly. Many DLookups will make the form
rather slow; I am hoping you don't have too many records in that
table.

You can certainly experiment with a query that selects from both the
Helo and the Issue table. Don't forget to put the PKs in the query
results even if you're not showing those columns. But in my experience
such forms will often be readonly.

-Tom.
Microsoft Access MVP

 

[NeoFax]

Thanks for the additional information. The 4 dropdowns on the main
form, plus datasheet view should work just fine. You may want to add a
button "Apply Filter". Then in that button's click event concatenate
the filter expression, and assign it to the subform. Something like
(I'm making up some object names):

dim strFilter as string
'NOTE: assuming Helo is a number and Station is a string (needs to be
wrapped in single-quotes)
strFilter = "Helo=" & Me.cboHelo & _
  Station='" & Me.cboStation & "'"  'etc.

With Me.mySubformControl.Form
  .Filter = strFilter
  .FilterOn
end with

On how to keep the subform updatable (with regards to the Issue
field): I have had good luck with creating a solution along these
lines:
Create a query and select only the Issue table. Put the IssueID and
Issue in the grid.
Make this query the RecordSource for your subform. Bind the Issue
control to the Issue field (ControlSource property).
For the other fields, use DLookup to pull the values. That will also
automatically make them readonly. Many DLookups will make the form
rather slow; I am hoping you don't have too many records in that
table.

You can certainly experiment with a query that selects from both the
Helo and the Issue table. Don't forget to put the PKs in the query
results even if you're not showing those columns. But in my experience
such forms will often be readonly.

-Tom.
Microsoft Access MVP







- Show quoted text -

Yes, I currently have the filter code. I never thought of using
DLookup in datasheet view. I currently use DLookup for other stuff in
the DB. The table is quite large, but I only plan to use the fields I
stated previously. Will the size of the table still slow the dlookup
down or does it only matter for the "column" (so to speak) you are
looking in. e.g. DLookup for the helo would only be as slow as the
lookup in the helo records, or does the whole table get pulled and
looked at?

 

[NeoFax]

Yes, I currently have the filter code.  I never thought of using
DLookup in datasheet view.  I currently use DLookup for other stuff in
the DB.  The table is quite large, but I only plan to use the fields I
stated previously.  Will the size of the table still slow the dlookup
down or does it only matter for the "column" (so to speak) you are
looking in.  e.g. DLookup for the helo would only be as slow as the
lookup in the helo records, or does the whole table get pulled and
looked at?- Hide quoted text -

- Show quoted text -

The problem with this is it only shows what is already in the Issue
table. I want to see all the records that are in a query that shows
all open work. Then from this work the technician can select the work
that is still open and type in problems for each work card.