Can someone steer me in the right direction?

[Minty]

If that query works - (And its a bit odd as it uses FROM ReportTbl but doesn't seem to have any fields from it ?) as a stand alone query then your problem is on your form.

 

[Sam Summers]

I added a field to the 'LeadingZeroQry' which has now changed the error message to see attached.

Could it be the join properties?

Here attached also is the relationship window for the query in case this sheds any further light on it?

 

[Minty]

The error message is telling you exactly what the problem is. You are trying to match the field called ReportRef to a field on the table ReportTbl - and there is no match.

I think you are confusing the fields you want to see on the form with the fields you are trying to pull into the forms. Your subsection prefix for example can be pulled onto a combo box on the form without being in the underlying query, you only need the Subsection in your TOItem table. Build a combobox to show you the prefix on the form.

I also think your use of sub forms is unnecessary for the one way you are using them. You can open the data entry form with the Reportnumber already filtered to the one you select on your main form, without needing the sub form.

 

[Sam Summers]

Ok, Great help Minty! As I say, I am trying to learn but got myself in such a tizz that I was trying anything and everything instead of taking a step back.

I will try and implement all you said and get back to you with the outcome/solution.

 

[Sam Summers]

Ok Minty,

I took all your advice and created a simple form, set the Report number as you suggested and then successfully used the following code to achieve what I need and so far all is good.

Private Sub Form_Current()

    If Me.Ref = 0 Or IsNull(Me.Ref) Then
        Me.Ref = Nz(DMax("Ref", "TOItem"), 0) + 1
    End If

End Sub

and

Private Sub SubSection_AfterUpdate()

Me.Prefix = Me.SubSection.Column(2)

Me.Item = [Prefix] & "" & [Number]

End Sub

and to get the leading zeros I used =Format([Ref],"000")

 

[Minty]

That all sounds a lot simpler and hopefully is easier to work with in the long run - glad you are getting there.