Recordset Problems..I think

fibayne

Registered User.
Local time
Today, 14:28
Joined
Feb 6, 2005
Messages
236
Hi
I have a DB that i use to record Purchase Orders each PO can have many Bookings and each booking can have many Invoices logged against it, this has been working as I wanted it to until I started to build reports from the info entered. The main thing the reports do is show the liability against each booking however if no data has been entered against the Invoices subform then the report cannot calculate the liability as it cannot subtract a blank field from the PO value. To try and get round this I added a tick box to table Invoices, made the join type the second one and added this tick box to bookings so that when a booking was entered it could be ticked and this would create a record in invoices and a value of zero in invoice value as this field is set to default to zero. This works fine for bookings that have already been created but if I try to create a new booking and tick the box it tells me 'join key of tble invoices not in recordset'
I have looked through the forums but cant find (or understand what I'm looking for...) I'm sure there is a far better way to do what I m am trying to do but my Access skills are fairly limited......if anyone could give me any help on this it would be greatly appreciated
thanks in advance..... Fi
 
Hi Rural Guy

No but I will do now ..thanks for the pointer ..Fi
 
Nz() Function

Hi
I have had a look at the function and understand what it will do I believe but am unsure where to put it ..I tried it on the Invoices form but it seemed to have no obvious effect, which Im sure will be clear to you why when you see what i tried below.

Is what it should be doing, add a zero, 0, value to 'InvoiceValue' on the reports..am I going in the right direction ??

thanks again Fi

Option Compare Database
Public Sub CheckValue()

Dim frm As Form
Dim ctl As Control
Dim varResult As Variant

' Return Form object variable
' pointing to Orders form.
Set frm = Forms!sf_VMDeliveredDetails

' Return Control object variable
' pointing to ShipRegion.
Set ctl = frm!InvoiceValue

' Choose result based on value of control.
varResult = IIf(Nz(ctl.InvoiceValue) = vbNullString, _
"No value.", "Value is " & ctl.InvoiceValue & "0")

' Display result.
MsgBox varResult, vbExclamation

End Sub
 
The way you are using Nz(), the IsNull() function would be better.
Code:
varResult = IIf(IsNull(ctl.InvoiceValue), _
   "No value.", "Value is [" & ctl.InvoiceValue & "]")
 
My thought in suggesting the Nz() function was that rather than adding the extra field to the table you simply use the Nz() function to turn your Null field into a zero.
 
Hi
I tried the Nz() function examples in Help and the one you have give above using IsNull used them as expression in the query that the report is based on they all gave a result but only in 'InvoiceValue' already had a value in it ie that some data entry had been done in the frm/tbl Invoices, I am going to try adding the example into the Liability expression on the query and see if it will work this way...however any more suggestions would be greatly appreciated ..thanks again for your time Rural Guy
 
It could be the type of join you are using. A Left join will give you all of the records in the Left table which would yield Null values in the Right table if no record exists. That would be a good place to use the Nz() function.
 
Hi Rural Guy

This seems to be working -

varResult: IIf(IsNull([InvoiceValue]),"£0.00",[InvoiceValue])

and

Liability: [NetToVM]-[varResult]

Thanks you so much for your help on this I was going about it in a very long winded way...I will of course get back to you if it isnt working as well as it seems to be at the moment !!! cheers Fi
 
Hi
This seems to work equally well, is their either one you would recommend to be better to use for any reason ??
Thanks again you have helped me in the past and it is much appreciated
Cheers Fi
 
varResult: Nz([InvoiceValue],0) will probably execute faster but it is really splitting hairs.
 

Users who are viewing this thread

Back
Top Bottom