By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,327 Members | 2,661 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,327 IT Pros & Developers. It's quick & easy.

More help with form RecordSource

P: n/a
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
Oct 20 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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
Oct 20 '06 #2

P: n/a
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

Oct 20 '06 #3

P: n/a
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
Oct 20 '06 #4

P: n/a
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
Oct 23 '06 #5

P: n/a
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
Oct 24 '06 #6

P: n/a
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
Oct 25 '06 #7

P: n/a
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
Oct 25 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.