More help with form RecordSource

[Alan]

With earlier help from this group (thanks Ben!) I've ended up with this
OnLoad code for my popup form:

Private Sub Form_Load()
If IsNull([Forms]![frmCustomerOrders]![MarketPlaceID]) = True Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Else
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]![MarketPlaceID]=[Forms]![frmCustomerOrders]![MarketPlaceID]"
End If
End Sub

So, if MarketPlaceID is null on the parent form, the popup form displays
records where there is no OrderID, otherwise it displays records where the
MarketPlaceID matches.

I need to tweak it because there are some instances where the MarketPlaceID
exists on the parent form but may not match any of the MarketPlaceID in the
tempOrders table. I've looked at unmatched queries but that only seems to
cater for unmatches netween to tables and not a table and a form value. I've
briefly looked at WHERE NOT EXISTS and wonder if that is the way forward? If
so I'm a bit sketchy as to how to insert it into the code above?

Thanks

Alan

[Michael Theymann]

Hi, Alan,

"Alan" wrote...

With earlier help from this group (thanks Ben!) I've ended up with this
OnLoad code for my popup form:

Private Sub Form_Load()
If IsNull([Forms]![frmCustomerOrders]![MarketPlaceID]) = True Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Else
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]![MarketPlaceID]=[Forms]![frmCustomerOrders]![MarketPlaceID]"
End If
End Sub

So, if MarketPlaceID is null on the parent form, the popup form displays
records where there is no OrderID, otherwise it displays records where the
MarketPlaceID matches.

I need to tweak it because there are some instances where the
MarketPlaceID
exists on the parent form but may not match any of the MarketPlaceID in
the
tempOrders table. I've looked at unmatched queries but that only seems to
cater for unmatches netween to tables and not a table and a form value.
I've
briefly looked at WHERE NOT EXISTS and wonder if that is the way forward?
If
so I'm a bit sketchy as to how to insert it into the code above?

I think it should look something like this:

---------------Code
Private Sub Form_Load()
If IsNull([Forms]![frmCustomerOrders]![MarketPlaceID]) = True Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Elseif DCount("OrderID","tempOrders","OrderID = " &
[Forms]![frmCustomerOrders]![MarketPlaceID])=0 Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Else
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]![MarketPlaceID]=[Forms]![frmCustomerOrders]![MarketPlaceID]"
End If
End Sub
---------------Code

regards

Michael

[Alan]

I think it should look something like this:

---------------Code
Private Sub Form_Load()
If IsNull([Forms]![frmCustomerOrders]![MarketPlaceID]) = True Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Elseif DCount("OrderID","tempOrders","OrderID = " &
[Forms]![frmCustomerOrders]![MarketPlaceID])=0 Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Else
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]![MarketPlaceID]=[Forms]![frmCustomerOrders]![MarketPlaceID]"
End If
End Sub
---------------Code

regards

Michael

Thanks for the quick reply Michael,

The code appears to work for blank MarketPlaceID on the form but for
non-existent or existing MarketPlaceIDs, when I load the form I get:

Run-time error 64479
The expression you entered as a query paramater produced this error. 'The
object doesn't contain the automation object ' <<it lists the entry for
MarketPlaceID here>>

When I debug it's highlighting:

Elseif DCount("OrderID","tempOrders","OrderID = " &
[Forms]![frmCustomerOrders]![MarketPlaceID])=0 Then

I'm on Access 97 by the way, in case it's one of those version related
syntax issues!

Thanks again

Alan

[Alan]

Run-time error 64479
The expression you entered as a query paramater produced this error. 'The
object doesn't contain the automation object ' <<it lists the entry for
MarketPlaceID here>>

When I debug it's highlighting:

Elseif DCount("OrderID","tempOrders","OrderID = " &
[Forms]![frmCustomerOrders]![MarketPlaceID])=0 Then

I'm on Access 97 by the way, in case it's one of those version related
syntax issues!

Thanks again

Alan

Actually, could it be to do with the fact that OrderID is a long integer and
MarketPlaceID (which is sometimes alphanumeric) is text?

Thanks

Alan

[Michael Theymann]

Hi Alan,

"Alan" wrote...

>
Actually, could it be to do with the fact that OrderID is a long integer
and
MarketPlaceID (which is sometimes alphanumeric) is text?

yup, that should be the error.

If it's alphanumeric, you should put " & chr(34) & " in front of and behind
the [Forms]![frmCustomerOrders]![MarketPlaceID].

Regards

Michael

[Alan]

yup, that should be the error.

If it's alphanumeric, you should put " & chr(34) & " in front of and

behind

the [Forms]![frmCustomerOrders]![MarketPlaceID].

Regards

Michael

Thanks for the response Michael,

Like this?

Elseif DCount("OrderID","tempOrders","OrderID = " & chr(34) &
[Forms]![frmCustomerOrders]![MarketPlaceID] & chr(34))=0

I get a data type mismatch on that one. Have I keyed it in wrong?

Thanks

Alan

[Michael Theymann]

Hi Alan,

"Alan" wrote...

>
Like this?

Elseif DCount("OrderID","tempOrders","OrderID = " & chr(34) &
[Forms]![frmCustomerOrders]![MarketPlaceID] & chr(34))=0

I get a data type mismatch on that one. Have I keyed it in wrong?

coding is completely correct. The data type mismatch is happening 'cause you
try to search for a string in an integer data type field. You should change
the OrderID to Text if possible, as is the MarketPlaceID. Otherwise you'd
have to drop the "chr(34)" again and first try to find out if
"MarketPlaceID" is not a string. If it is one, you cannot query your OrderID
with your MarketPlaceID.

Yours,

Michael

[Alan]

Thanks Michael,

"Michael Theymann" <el*@maithai.dewrote in message
news:eh*************@news.t-online.com...

Like this?

Elseif DCount("OrderID","tempOrders","OrderID = " & chr(34) &
[Forms]![frmCustomerOrders]![MarketPlaceID] & chr(34))=0

I get a data type mismatch on that one. Have I keyed it in wrong?

coding is completely correct. The data type mismatch is happening 'cause

you

try to search for a string in an integer data type field. You should

change

the OrderID to Text if possible, as is the MarketPlaceID. Otherwise you'd
have to drop the "chr(34)" again and first try to find out if
"MarketPlaceID" is not a string. If it is one, you cannot query your

OrderID

with your MarketPlaceID.

OK, I see what the problem is and what the code is trying to do. Indeed, the
OrderID is numeric and the marketpaceid is text (as it meant to be linked to
ebay user names). I'll do as you say and change the OrderID to text.

Thanks for all your help on this, this has helped save a lot of time for me
and for the people who will be using this form!

Alan