acViewPreview clearing form of data in textboxes

[Markb384]

Hi guys, I originally posted this over on stackoverflow, but no replies, so I thought I would try in a more specialist forum. Link is here though... http://stackoverflow.com/questions/2...m-unexpectedly



To summarise my problem.

I use acViewPreview to open up my form in print preview mode after the textboxes within the form have been populated, ready for the user to print (I know about reports etc but a form was easier at the time). The code used to work perfectly, and I don't know what changed. Now, when the command runs the print preview mode has cleared all of the data from the form. Using the debug "meatball", I have narrowed it down to the use of the acViewPreview, and if I comment this line out, the data remains within the textboxes even after manually print previewing.

Anyone have any idea why this is suddenly happening or how I can fix it? A sample of my code is shown below

Private Sub generate_report_Click()'a subroutine to calculate the results of the selected date
Dim sdate As Date
Dim equipID As String, testString As String, msgString As String
Dim msgResponse As Integer
Dim hd_boo As Boolean


'check a date has been selected and if one has, inform the user of which tests were   performed on that date
If IsNull([Forms]![Choose Calculation]!
[List_date].Column(0)) Then
    MsgBox ("Please enter a valid date"): Exit Sub
Else
    'set the string for the multiple criteria using information from the form
    sdate = [Forms]![Choose Calculation]!
[List_date].Column(0)
    equipID = [Forms]![Choose Calculation]![Equipment_ID]
    'use format to confirm that the date types are correct
    testString = "[Equipment ID] = '" & equipID & "' AND Format([Survey Date],""ddmmyyyy"") = " & Format(sdate, "ddmmyyyy")


    If Not IsNull(DLookup("[ID]", "[Results_Hutt_Dig]", testString)) Then msgString = msgString & "- Digital Huttner" & vbCrLf: hd_boo = True


    'confirm at least one test was found and inform user if not
    If msgString = "" Then
        MsgBox ("No tests results were found for the date selected, please select another date")
        Exit Sub
    Else
        'ask user if they are happy with the tests performed
        msgResponse = MsgBox("The following tests were performed on that date:" & vbCrLf & vbCrLf & msgString & vbCrLf & _
                        "Do you wish to proceed?", vbYesNo, "Tests Performed")
        'if user says no, exit the sub
        If msgResponse = 7 Then Exit Sub
    End If
End If


'open the form to dump data in and clear any existing data
DoCmd.OpenForm "Results"
Call clear_resultsSheet


'dump generic data
[Forms]![results]![Text_date] = sdate
[Forms]![results]![Text_equipID] = equipID


'populate the report with the appropriate test results
If hd_boo = True Then Call huttdig_calculation(testString, equipID, sdate)


'open report page in print preview mode
DoCmd.OpenForm "Results", acViewPreview


End Sub

Thank you

[trevor40]

If Not IsNull(DLookup("[ID]", "[Results_Hutt_Dig]", testString)) Then msgString = msgString & "- Digital Huttner" & vbCrLf: hd_boo = True

The above could have a typo, try below. I've never seen colon used like this anybody else? compile seems ok as well?

If Not IsNull(DLookup("[ID]", "[Results_Hutt_Dig]", testString)) Then msgString = msgString & "- Digital Huttner" & vbCrLf & hd_boo = True

[Markb384]

Hi Trevor

I've used the colon to append two lines onto the same line for conciseness and readability, wouldn't using an ampersand try to concatenate 'hd_boo = true' to the rest of msgString? The code compiles and runs okay, and worked well previously.

Edit: Also, hd_boo does indeed get changed to True and the calculation runs, i.e. it is visible on the form if I comment out the acViewPreview line.

[trevor40]

I've never seen colon used like this anybody else? compile seems ok as well?

that's why I posed the question, I've only ever seen and used the " _ " to do this.

Can't see what this is doing - Call huttdig_calculation(testString, equipID, sdate)

try to requery the open form prior to print preview

[Markb384]

Ahh I see. Yes, I've use : to join two small lines of related code together to make things concise etc and _ to split something over two lines so that it is readable without scrolling.

The functions that are called (there are quite a few, but I slimmed the code down for readability here) only take values from the form, perform some calculations and lookups and dump new values in other textboxes on the form. If I comment out the acViewPreview line, the values appear as expected.

I did wonder briefly if the print preview was calling an older, unpopulated version of the form, before everything was updated. I haven't populated it with a query though, but rather through judicious use of DLookup (I need small bits from a single record from several different tables and I found it easier this way). How do I go about refreshing the page if I have done it this way.

Thank you

EDIT: I tried Forms!Results.Repaint and Requery, both to no avail

[Markb384]

Hmm, thinking about it, I'm wondering if the line DoCmd.OpenForm "Results", acViewPreview is reopening the form instead of just switching the current form to print preview as I have some code which runs on form load to empty the textboxes. I shall pursue this line of thought and reply back.

[trevor40]

I think that's whats going on with it too.
try from access help...

Private Sub UserForm_Click()
UserForm1.PrintForm
End Sub

Or

DoCmd.PrintOut acPages, 1, 4, , 2

never used these my self, allways used reports only. I found that the formatting for forms is mainly screen oriented.

[Markb384]

Using printout prints the form out directly, without showing a preview unfortunately
And PrintForm isn't recognised as a command either.

I did find DoCmd.RunCommand acCmdLayoutPreview on the Internet, but it appears that this is legacy code and is no longer available in 2010.

[trevor40]

O well, make a report that looks like your form, if you have to have the report fields = the form fields, that's about all I can think to do, unless sombody else has some idea.

[Markb384]

Looks like I will have to do something along those lines unfortunately. I have approximately 150 textboxes to transfer and tie up though