Using Buttons to Open Subforms

[JeepsR4Mud]

My subforms are designed in datasheet view, but when I access them with my buttons in the main form, they appear in a standard column view.

How can I set the controls to preserve the datasheet view?

Thanks.

Gayle Ann

 

[skwilliams]

Go to the subform's properties and under view's allowed, select Datasheet View.

 

[JeepsR4Mud]

That doesn't seem to work.

THe subform is accessed by a button, and there don't seem to be any controls for specificing the view.

When I actially open the subform as a form, it opens as a data sheet, but not when accessed with the button.

I went throught he properties, and selected no for form view and yes to datasheet, but the control button seems to overide that pereference.

Thanks.

 

[skwilliams]

If you access your VB code which executes when the button is clicked. It could be that your DoCmd.OpenForm is set to acNormal instead of acFormDS. This is the code that tells the program in what view to open the form.

If that doesn't work, perhaps you could post the database or email me.

 

[Pathetique]

What if you use a button to call a macro that opens the form. The macro action OpenForm allows you to specify what you need under the Action Argument called View

Does this help?

Path

 

[JeepsR4Mud]

Okay, here is the code excuted when the button is activated:

Private Sub Staff_Injury_Click()
On Error GoTo Err_Staff_Injury_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Staff Injury"

stLinkCriteria = "[Institution]=" & "'" & Me![List85] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Staff_Injury_Click:
Exit Sub

Err_Staff_Injury_Click:
MsgBox Err.Description
Resume Exit_Staff_Injury_Click

End Sub



I have a table called Institutions that lists the name for each facility. In the form, the value is selected from a list box (List85).

The main form is called WEEKLY REPORT and draws from a table called WEEKLY REPORT. WEEKLY REPORT lists the information each site reports, but for which there are not multiple answers. Since, there can be multiple staff injuries (unfortunately), I have a separate table known as STAFF INJURIES.

I am not sure I have it designed properly in that the identifying fields are INSTITUTION and WEEK. In the main table/form WEEKLY REPORT, each institution has only one record per week. Each subform duplicates the INSTITUTION and WEEK fields. WEEK is date of the SUnday that starts the week in question.

I'm not sure how to set the primary keys and develop the relationships.

I know the table INSTITUTIONS can have a primary key for the field INSTITUTION because each facility is listed only once.


Is it something like:

INSTITUTIONS --- WEEKLY REPORTS ---- STAFF INJURIES

Linking the INSTITUTION field from INSTITUTIONS to the INSTITUTION field in WEEKLY REPORTS and then the INSTITUTION field in WEEKLY REPORTS to the INSTITUTION field in STAFF INJURIES?

When I co back to the record, I'd like the subform to retrieve only the records relevant to that institution and the week selected in the main form.

At some point I think I'm going to need a button, prior to the form opening, that asks me to select the institution and the week.

I did an employee database with subforms and didn't have any problem because everything used one employee ID number, but here there are two fields that make the record unique: WEEK and INSTITUTION.

When I open the subform, I'd like the INSTITUTION field and the WEEK field to default to the values in those fields in the subform. I know there is a way to do that, but I can't remember.

I used to be fairly proficient in this stuff, but haven't done anything in VB for 4 years. I have a lot of relearning to do!!!

I'm headed home now, but will check back in tonight, and will be working on it tomorrow.

Thanks!

 

[JeepsR4Mud]

Would it be proper to assign each record a unique name? Create a field called ID? It could be a combination of the Facility name and the date? For example, ABC092502.

If I did that my subform problems would be solved, but I didn't know if that could become cumbersome at some point.

Next question.

Is there a way to set a field to combine the values of previous fields to create this field, or does it have to be manually entered?

I already have a field INSTITUTION to define the site. And WEEK to define the date. Can I have a command that combines the values entered into these fields for the ID field?

Thanks.

 

[skwilliams]

As far as the Datasheet View, this code should take care of the problem. I just added acFormDS to your code. I tried it and the code did display the form in datasheet view when activated from the main form.

I'll have to think about the remainder of your last post.

Private Sub Staff_Injury_Click()
On Error GoTo Err_Staff_Injury_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Staff Injury"

stLinkCriteria = "[Institution]=" & "'" & Me![List85] & "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

Exit_Staff_Injury_Click:
Exit Sub

Err_Staff_Injury_Click:
MsgBox Err.Description
Resume Exit_Staff_Injury_Click

End Sub

 

[skwilliams]

I believe it is possible to combine values from two fields into another field. But I'm not familiar with that procedure.

You may want to check with other members.

 

[JeepsR4Mud]

THANKS!

I was mulling this problem last night while mowing the lawn (I like mowing the lawn because it is about the only time I'm not trying to do 6 things at once and it lets me THINK!). Besides, I have a John Deere mower. <grin>

If I create a unique name for each record, it is really the same as using an employee number, etc. It would probably be easier than thinking of the code (at least for me with my limited skills), that would always link the institution field with the week field.

I can make the ID the primary kep in the primary table, then just create relationships with all of the other subfields.

Sound plausible?

 

[skwilliams]

It sounds like a plan!

 

[JeepsR4Mud]

Do you have any exdperience with SPSS?

 

[skwilliams]

No I don't. You might find someone in this forum with experience.

 

[JeepsR4Mud]

When the subform opens, how can I specify the size, so it isn't full screen?

Thanks.

 

[skwilliams]

You can find some useful information about resizing a form in Access Help.

 

[JeepsR4Mud]

How can I get the fields to record decimals?

I've used the help feature but can't figure out what I'm doing wrong.

I know the property has to be set in the table, and I have 2 decimal places, but they revert to 0. I type 123.45 and 123.00 is what isw displayed.

Thanks.

 

[Pathetique]

open your table in design view and make sure the datatype setting for the field that you want to display 2 decimal places is set to number and not text. Then you should see the Decimal Places property at the bottom under the general properties tab. Set this to 2 and the table should display your numbers with 2 decimal places. does this help?

 

[JeepsR4Mud]

I did all that. It is set to number, and I changed the decimal places from auto to 2.

I've tried fixed, standard, etc. And, I have field size on decimal.

 

[Pathetique]

is the problem that the decimals do not appear when you write to the table from a form?

 

[Pathetique]

if so, go to the form and right-click on the control for that field and choose properties. there is a setting for decimal places under the format tab. this setting could cause your problem. is this the problem?