Different record sources for one form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Angi - 29 Apr 2005 16:25 GMT

I have a form that displays a list of quotes and invoices from a
company. I'm trying to set the record source of my form through code,
so I can use the same form. I thought it would consist of a simple sql
statement that would match the fields with the controls, but it's not
working. I've set the control sources for the controls the same as the
sql stmt. I've tried the form_load, form_current and form_open events.
If I copy and paste this into the forms record source, it works fine
(after del the _& " of course). Can someone please tell me what I need
to do??? TIA!

Here's my first sql statment:

Private Sub Form_Load()
Dim QuoteSql As String
QuoteSql = "SELECT QuoteMain.QuoteID AS DocNo, QuoteMain.CoID AS
CoCOID, QuoteMain.OrderDate AS OrderDate, QuoteMain.FreightAmt AS
FreightAmt, DSum('ExtPrice','quotedetails','quoteid = ' &
[DocNo])+[freightamt] AS OrderTotal " _
& "FROM QuoteMain ORDER BY QuoteMain.QuoteID;"

CurrentDb.Execute QuoteSql

End Sub

Reply to this Message

Klatuu - 29 Apr 2005 17:20 GMT

No, you have to change the record source for the form. Change the code where
you open the form:

docmd.OpenForm "MyForm",acNormal,,,,acHidden
forms!MyForm.recordsource = "MyTable"
forms!MyForm.visible = True

> I have a form that displays a list of quotes and invoices from a
> company. I'm trying to set the record source of my form through code,
[quoted text clipped - 19 lines]
>
> End Sub

Reply to this Message

Angi - 29 Apr 2005 18:06 GMT

Thank you very much!!! That did it.

Reply to this Message

Angi - 29 Apr 2005 18:39 GMT

I just realized I have one small problem...I was opening my form with a
Where and after doing it this way, it's not working. It's giving me
ALL the records and not filtering. If I put the Where int he sql
statement, I'm going to have multiple sql stmts. I need to set the
CoCOID as I open the form. Here's the relevant pieces of what I have:

QuoteSQL = "SELECT QuoteMain.QuoteID AS DocNo, QuoteMain.CoID AS
CoCOID, QuoteMain.OrderDate AS OrderDate, QuoteMain.FreightAmt AS
FreightAmt, DSum('extprice','quotedetails','quoteid=' &
[docno])+[freightamt] AS OrderTotal " _
& "FROM QuoteMain ORDER BY QuoteMain.QuoteID;"

DoCmd.OpenForm "frmdisplayres", acNormal, , , ,
acHidden
Forms!frmdisplayres.RecordSource = QuoteSQL
Forms!frmdisplayres!CoCOID =
Forms!frmSearch!cboCoName.Column(0)
Forms!frmdisplayres.Visible = True

Reply to this Message

Angi - 02 May 2005 04:01 GMT

Any ideas?

Reply to this Message

Angi - 03 May 2005 02:24 GMT

OK...I feel like I'm talking to myself, but I really need some help on
this one. I've tried all of the following and it's still giving me all
the records instead of the ones for the CoID. How do I change the
record source through code and still have a filter?

DoCmd.OpenForm "frmdisplayres", acNormal, , "coid="
& Me.txtCoCOID, , acHidden
Forms!frmdisplayres.RecordSource = QuoteSQL
Forms!frmdisplayres.Visible = True
AND
DoCmd.OpenForm "frmdisplayres", acNormal, , , ,
acHidden
Forms!frmdisplayres.RecordSource = QuoteSQL
Forms!frmdisplayres!CoCOID =
Forms!frmSearch!cboCoName.Column(0)
Forms!frmdisplayres.Visible = True
AND
DoCmd.OpenForm "frmdisplayres", acNormal, , "coid="
& Me.txtCoCOID, , acHidden
Forms!frmdisplayres.RecordSource = QuoteSQL
Forms!frmdisplayres.Visible = True
'Forms!frmdisplayres!CoCOID =
Forms!frmSearch!cboCoName.Column(0)

Reply to this Message

Ken Snell [MVP] - 03 May 2005 03:34 GMT

I think the problem is that you're changing the frmdisplayres form's
recordsource after you open it. That will negate the WhereCondition that you
passed to the form when you opened it.

Any reason you can't include the WHERE statement for the coid filter in the
QuoteSQL string?

Signature

Ken Snell
<MS ACCESS MVP>

> OK...I feel like I'm talking to myself, but I really need some help on
> this one. I've tried all of the following and it's still giving me all
[quoted text clipped - 19 lines]
> 'Forms!frmdisplayres!CoCOID =
> Forms!frmSearch!cboCoName.Column(0)

Reply to this Message

Angi - 03 May 2005 04:01 GMT

Ken,
I understand what you're saying about changing the recordsource after
it's open, but that's what I was told to do earlier in this thread. Is
there another way I should be doing it?? (Please read my initial post
if I'm not explaining enough).

As far as why I can't include the WHERE statement in the sql string...I
can, but I'll explain why I don't want to. My code is made up of
Select Cases that does different things depending on an option group
(which has 5 options). I have 2 sql statements...one for quotes, one
for invoices. Which means I will have to write 10 separate sql
statements to match the case. It's easier for me to apply the filter I
need.

Am I going about this wrong?? Thanks for replying!!! Was having a
panic attack! :)

Reply to this Message

Ken Snell [MVP] - 03 May 2005 04:50 GMT

When you change the recordsource of a form, it starts all over....

Assuming that each SQL statement that you build will have an ORDER BY
clause, you could add a WHERE statement to the query just before setting the
form's recordsource to the query:

Dim lngLoc As Long
lngLoc = InStr(QuoteSQL, "ORDER BY")
QuoteSQL = Left(QuoteSQL, lngLoc -1) & _
"WHERE coid=" & Me.txtCoCOID & " " & _
Mid(QuoteSQL, lngLoc)
Forms!frmdisplayres.RecordSource = QuoteSQL

Or you could set the Filter property of the form after you set the
recordsource:

Forms!frmdisplayres.RecordSource = QuoteSQL
Forms!frmdisplayres.Filter = "coid=" & Me.txtCoCOID
Forms!frmdisplayres.FilterOn = True

Or you could split the SQL string building into parts. Put one part in
"SELECT CASE" code step -- this first part would probably put the field
names into the SQL string. Then, after that block, add the WHERE clause.
Then do another SELECT CASE to add the ORDER BY clause.

Signature

Ken Snell
<MS ACCESS MVP>

> Ken,
> I understand what you're saying about changing the recordsource after
[quoted text clipped - 12 lines]
> Am I going about this wrong?? Thanks for replying!!! Was having a
> panic attack! :)

Reply to this Message

Angi - 03 May 2005 05:07 GMT

Ken, Ken, Ken.....you are the man!!!

I used

Forms!frmdisplayres.RecordSource = QuoteSQL
Forms!frmdisplayres.Filter = "coid=" & Me.txtCoCOID
Forms!frmdisplayres.FilterOn = True

quite honestly, because it sounded easier and IT WORKS!!!! I've never
used the filter property, so I didn't even think of it. Thank you so
very much!!!!!

gratefully,
ang

Reply to this Message

Ken Snell [MVP] - 03 May 2005 05:18 GMT

< chuckle > Glad it worked!

Signature

Ken Snell
<MS ACCESS MVP>

Reply to this Message