Print Preview Issues

[newaccess user]

Hello,

I have VBA code for a Print Preview button on the main form that previews the current record in a separate Report using this code:

Private Sub cmdPrintRecord_Click()
    Dim strWhere As String

    If Me.Dirty Then    'Save any edits.
        Me.Dirty = False
    End If

    If Me.NewRecord Then 'Check there is a record to print
        MsgBox "Select a record to print"
    Else
        strWhere = "[ID_Soldier] = " & Me.[ID_Soldier]
        DoCmd.OpenReport "rptRecordPrint", acViewPreview, , strWhere
    End If
End Sub

I use this Print Preview code on a Button in the Form Header. The Main form lists head of household information. I also have a tabbed control with three tabs that have SubForms for Address, Children and Contact details.

The problem I have is that if any of the subforms are left blank I get a Run-Time Error '3021': No Current Record. If I Debug (press the button), it takes me straight to this line:

varAge = DateDiff("yyyy", varBirthDate, Now)

highlighted in Yellow.

Anyone on my database must have an address, should have contact details and could have children. If any one of these is blank then the print preview will not work.

I would like to know how to stop this error happening for blank records in my subforms.

I am a complete novice BTW and I would appreciate any help you may be able to provide.

Thanks in Advance.

 

[sensetech]

Sorry if I'm stating the obvious but the problem lies within the data / code behind rptRecordPrint. Try working on that direct rather than invoking it via your command button i.e. put the report into design mode then go into print preview mode.

If you're still stuck, post the database up here as a zip file and I'll take a look.

 

[newaccess user]

Thank you for your response.

I have been working on it, but i will go back and recheck or make another form from scratch to see what happens.

If i get no joy i will be happy to zip and upload for your advice.

Thanks again.

 

[sensetech]

I don't think the problem lies within the form but in the the report. In which module is the "failing" code?

(Given the UK forecast for tomorrow, I think I'll be available here most of the day!)

 

[newaccess user]

Apologies,

I understood your response, I meant Report not Form.

The one liner of Code in my original post is in a Module i have called "DateYearAndMonth"

And fails in the Age Function at:

varAge = DateDiff("yyyy", varBirthDate, Now)

I have attached a Jpeg showing the Module and where it is failing and I hope this helps. I believe the clue is in the tool tip text you can see in the picture.

Thanks again and i will do some more work on this tomorrow.

 

[newaccess user]

@AccessBlaster.

Sorry for any confusion, these fields are required, i was referring to the Human element of the people and information i enter into the Database.

The head of household must live in house, It is 2014 and therefore should have some contact details, Home tel, Mob, email address.

But he/she may not have any children.

so the original question still remains. if i leave any of the subform information blank in the main form the print preview will not work.

I hope that clears it up a bit.

I should have explained myself better before.

 

[sensetech]

I get the feel of what's going wrong i.e. it's going into that module to work out a Child's age even though the Soldier doesn't actually have a Child. I think I need the database to get to the root of the problem.

 

[newaccess user]

As requested I have attached a stripped down version of the DB that still creates the same error.

I have left to fictional records 1 without children and 2 with.

record 2 works ok, however record 1 does not.

Any advice will be greatly appreciated.

 

[sensetech]

In the report's Data properties tab, the Record Source isn't quite correct. You've used an INNER join between the Soldier table and the Children table which means that only Soldiers who have at least one child will appear on the report. You need to change it to a LEFT join which will include all Soldiers whether they have children or not.

 

[newaccess user]

Thank you very much. It is very easy to get bogged down in an issue only to find the problem is an easy fix.

I appreciate you looking into it.

Thanks Again.

 

[jdraw]

You have used many field Lookups at the table level. Most advise against this. I'm not saying that this is definitely part of the issue, but it may be.

 

[newaccess user]

@JDaw,

Thank you for your advice, a very good point, I will now go and remove them.