Different record sources for one form

[Angi]

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

 

[Klatuu]

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

 

[Angi]

Thank you very much!!! That did it.

 

[Angi]

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

 

[Angi]

Any ideas?

 

[Angi]

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)

 

[Ken Snell [MVP]]

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?

 

[Angi]

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!

 

[Ken Snell [MVP]]

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.

 

[Angi]

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

I used

Forms!frmdisplayres.RecordSour­ce = 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

 

[Ken Snell [MVP]]

< chuckle > Glad it worked!

--

Ken Snell
<MS ACCESS MVP>

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

I used

Forms!frmdisplayres.RecordSour­ce = 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