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]![frmCustomerOrde rs]![MarketPlaceID]) = True Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Else
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]![MarketPlaceID]=[Forms]![frmCustomerOrde rs]![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]

Re: More help with form RecordSource

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]![frmCustomerOrde rs]![MarketPlaceID]) = True Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Else
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]![MarketPlaceID]=[Forms]![frmCustomerOrde rs]![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]![frmCustomerOrde rs]![MarketPlaceID]) = True Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Elseif DCount("OrderID ","tempOrders", "OrderID = " &
[Forms]![frmCustomerOrde rs]![MarketPlaceID])=0 Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Else
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]![MarketPlaceID]=[Forms]![frmCustomerOrde rs]![MarketPlaceID]"
End If
End Sub
---------------Code

regards

Michael

[Alan]

Re: More help with form RecordSource

I think it should look something like this:
>
---------------Code
Private Sub Form_Load()
If IsNull([Forms]![frmCustomerOrde rs]![MarketPlaceID]) = True Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Elseif DCount("OrderID ","tempOrders", "OrderID = " &
[Forms]![frmCustomerOrde rs]![MarketPlaceID])=0 Then
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]!OrderID = Null"
Else
Me.RecordSource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
[tempOrders]![MarketPlaceID]=[Forms]![frmCustomerOrde rs]![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]![frmCustomerOrde rs]![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]

Re: More help with form RecordSource

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]![frmCustomerOrde rs]![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]

Re: More help with form RecordSource

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]![frmCustomerOrde rs]![MarketPlaceID].

Regards

Michael

[Alan]

Re: More help with form RecordSource

yup, that should be the error.
>
If it's alphanumeric, you should put " & chr(34) & " in front of and

behind

the [Forms]![frmCustomerOrde rs]![MarketPlaceID].
>
Regards
>
Michael
>
>

Thanks for the response Michael,

Like this?

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

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

Thanks

Alan

[Michael Theymann]

Re: More help with form RecordSource

Hi Alan,

"Alan" wrote...

>
Like this?
>
Elseif DCount("OrderID ","tempOrders", "OrderID = " & chr(34) &
[Forms]![frmCustomerOrde rs]![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
"MarketPlac eID" is not a string. If it is one, you cannot query your OrderID
with your MarketPlaceID.

Yours,

Michael

[Alan]

Re: More help with form RecordSource

Thanks Michael,

"Michael Theymann" <elo@maithai.de wrote in message
news:ehnf84$7dp $01$1@news.t-online.com...

Like this?

Elseif DCount("OrderID ","tempOrders", "OrderID = " & chr(34) &
[Forms]![frmCustomerOrde rs]![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
"MarketPlac eID" 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