Update form

duke_44 · Post by **duke_44** » 01 Mar 2017, 17:05

Thank you!

I am trying to make a number of fields invisible on the form until my user make a selection, then depending on the selection certain fields will appear. I have accomplished that by setting the visibility settings to FALSE on form load and then a series of IF statements to change the setting as required. I'm having trouble getting the visibility to return to FALSE when my user goes to the next entry.

duke_44 · Post by **duke_44** » 01 Mar 2017, 17:18

While working on it I noticed when moving between entries on the form the visibility is also whatever the last setting was. Is there a way to save the visibility of the fields in a specific record.

Post by **HansV** » 01 Mar 2017, 17:37

You can use code in the On Current event of the form to (re)set the visibility of controls depending on the values of fields.

duke_44 · Post by **duke_44** » 01 Mar 2017, 17:50

Thank you that works perfectly.

How about saving the visible settings on a specific record so that it the proper fields showing when moving between records.

Post by **HansV** » 01 Mar 2017, 19:41

That would be possible, but it would require adding extra fields to the table; I would prefer to set the visibility based on the values of the existing fields.

duke_44 · Post by **duke_44** » 02 Mar 2017, 17:08

How could I code a search button? I would like to filter the sub form by a user selected date then the user can retrieve a record from a specific date to modify, etc.

Post by **HansV** » 02 Mar 2017, 22:33

Let's say you have a text box txtDate in which the user enters a date and a command button cmdFilter. The On Click event procedure of the command button could look like this:

Code: Select all

Private Sub cmdFilter_Click()
    With Me.SubformName.Form
        If IsNull(Me.txtDate) Then
            .FilterOn = False
        Else
            .Filter = "[DateField] = #" & Format(Me.txtDate, "mm/dd/yyyy") & "#"
            .FilterOn = True
        End If
    End With
End Sub

where SubformName is the name of the subform and DateField is the name of the date field.

duke_44 · Post by **duke_44** » 06 Mar 2017, 14:56

Perfect thanks!

I would like to have a button that generates a report. I would like the report to have 2 criteria that my user would enter to draw data from ( I would like DATE and Operator Name), however the only mandatory criteria would be DATE. In the report I would like to have a couple of fields that ID the vehicle for each record(ie. VIN). Attached to each unique VIN I would like the report to display any unresolved issues (if you recall, I have check boxes indicating if an issue has been resolved). Would I require a number if IF statements that identifies if the check box is TRUE or FALSE? Or is there an easier/quicker way for Access to determine what findings fields to include with each record in a report. Once generated I would like the ability to export the data into EXCEL or WORD (or an option for either). Thanks again Hans! I really appreciate all your help.

Post by **HansV** » 06 Mar 2017, 15:21

That is a rather big request! Let's take it step by step.

For viewing within Access and for printing, a report is ideal. A report can also be exported to a PDF file.
Exporting a report to Excel or Word is one of the weaker features of Access, though. It's better to export a query to Excel, and to use mail merge in Word based on a query.

I'd start by creating a query that returns the fields that you need. Don't worry about the criteria - we'll take care of that later on.

Next, create a report based on that query. When you have the report looking the way you want, we can create a form to specify the criteria and to open the report with the criteria as filter.

duke_44 · Post by **duke_44** » 06 Mar 2017, 16:18

Ok I believe I am ready to go forward. This is what I have created:

1. Query returning the fields I would like included
2. Report designed to my liking
3. Form for my user to enter their criteria (at this point only DATE and OPERATOR NAME). I don't think any further criteria would be required for our reporting requirements.

So from here I would like to have my user enter the DATE on the newly made form and return results. The OPERATOR NAME would only come into play if there are different NAMES with the same date.

As for the report I would like the FINDINGS without a check (which means the issue has been resolved) to be displayed in the report.

duke_44 · Post by **duke_44** » 06 Mar 2017, 16:38

As a side note: the VIN field must be 17 characters, what can I put on the AFTER UPDATE event to ensure that?

Post by **HansV** » 06 Mar 2017, 17:05

Let's say the report is imaginatively named rptReport, the text boxes on the form are names txtDate and txtOperator, and the corresponding fields are named DateField (Date/Time) and Operator (Text).

Place a command button cmdReport on the form, with the following On Click event procedure:

Code: Select all

Private Sub cmdReport_Click()
    Dim strWhere As String
    On Error GoTo ErrHandler
    If IsNull(Me.txtDate) Then
        Me.txtDate.SetFocus
        MsgBox "Please enter a date", vbInformation
        Exit Sub
    End If
    strWhere = "[DateField] = #" & Format(Me.txtDate, "mm/dd/yyyy") & "#"
    If Not IsNull(Me.txtOperator) Then
        strWhere = strWhere & " AND Operator = " & Chr(34) & Me.txtOperator & Chr(34)
    End If
    DoCmd.OpenReport "rptReport", acViewPreview, , strWhere
    Exit Sub

ErrHandler:
    If Err <> 2501 Then
        MsgBox Err.Description, vbExclamation
    End If
End Sub

duke_44 · Post by **duke_44** » 07 Mar 2017, 16:22

Hi Hans, what coding can I put in the AFTER UPDATE event to ensure the VIN entry is 17 characters?

Post by **HansV** » 07 Mar 2017, 16:39

I'd use the Before Update event instead:

Code: Select all

Private Sub txtVIN_BeforeUpdate(Cancel As Integer)
    If Len(Me.txtVin & "") <> 17 Then
        MsgBox "VIN should be 17 characters!", vbExclamation
        Cancel = True
    End If
End Sub

duke_44 · Post by **duke_44** » 08 Mar 2017, 14:38

Hi Hans, a couple items:
1. The system asks for the date twice. Once under the text box that I input, once I hit the CREATE REPORT button a box pops up and asks for the date a second time.
2. The report displays all results from the table. Doesn't seem to filter by date.

Does Access have the capability of limiting options in a combo box? On the form I enter the date to generate the report I would like the user to enter the date and then select any options based on that date in the form of a drop down box (essentially enter the date and choose an operator that has had an inspection on that date).

Post by **HansV** » 08 Mar 2017, 14:46

1. Make sure that you refer to the correct name of the text box with the date.
2. The code should filter on the date.
3. Yes.

It'd be best if I could see the database again.

duke_44 · Post by **duke_44** » 08 Mar 2017, 15:35

https://www.dropbox.com/s/df6uhej8x46vz ... accdb?dl=0" onclick="window.open(this.href);return false;

I am happy with the data entry portion of the DB. I would like to work towards a report function.

Ideally I would like:
1. The report to filter and display based on date and if there are 2 operators with the same date have the option to choose which one to display in the report.
2. Have the report display unresolved issues only.
3. Then ultimately to allow my user to export into a PDF to hand over.

Post by **HansV** » 08 Mar 2017, 15:58

You want the report to be filtered on InspDate, but

1) In the code for strWhere, you used

Code: Select all

    strWhere = "Me.txtDate= #" & Format(Me.txtDate, "mm/dd/yyyy") & "#"

Within the quotes, you should use InspDate instead of Me.txtDate:

Code: Select all

    strWhere = "InspDate = #" & Format(Me.txtDate, "mm/dd/yyyy") & "#"

2) You haven't included InspDate in the query that acts as record source for the report, so the query and report cannot be filtered on InspDate. Add this field to the query.

Which field in tblVehRec contains the operator?

duke_44 · Post by **duke_44** » 08 Mar 2017, 16:22

in tblVehRec the ServName contains the Operator.

Is it going to take much to have Access to show just unresolved issues? In the report design should I include text boxes for all possible options (what I mean is that I have 20 text boxes for concerns, with 20 check boxes to indicate resolved). I don't want to have resolved issues displayed as its not relevant.

Those changes work perfectly, sorry I must have mis-typed.

Post by **HansV** » 08 Mar 2017, 17:11

The design flaw now comes to bite you. It would have been much better to place the findings in a separate table, with one record per finding.
With the present setup, you'll have to have a text box for each finding, and use code in the On Format event of the Detail section of the report to hide those text boxes for which the corresponding Yes/No field is True...

Eileen's Lounge

Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form