Opening a form 1

[Buj123]

Hi,
I have a form which will have 4 text boxes where user will type ID,Version,Channel,Date and hit a search button which should open a form for instance "frmEmail" which is already in my database, but it should change the Recordsource query for form "frmEmail" based on my search creiteria.
Currently my code is
Dim SqlEl As String
Dim SqlPs As String
Dim SqlMl As String


If (Me.Text4.Value = "EMAIL&quot Then
SqlEl = "SELECT DISTINCT CampaignID, CampaignVersion, CampaignName, DeliveryChannel, TDNumber, InHomeDate, Notes FROM tblCouponMaintain WHERE DeliveryChannel in('EMAIL') and CampaignID=" & """" & Me.Text0.Value & """" & " and CampaignVersion=" & """" & Me.Text2.Value & """" & " and InHomeDate=" & "#" & Me.Text6.Value & "#"

Forms!frmEmail.RecordSource = SqlEl

Else
If (Me.Text4.Value = "POS&quot Then
SqlPs = "SELECT DISTINCT tblCouponMaintain.CampaignID, tblCouponMaintain.CampaignVersion, tblCouponMaintain.CampaignName, tblCouponMaintain.DeliveryChannel, tblCouponMaintain.TDNumber, tblCouponMaintain.InHomeDate, tblCouponMaintain.Notes FROM tblCouponMaintain WHERE tblCouponMaintain.DeliveryChannel In ('POS')"

Forms!frmPos.RecordSource = SqlPs

Else
If (Me.Text4.Value = "MAIL&quot Then
SqlMl = "SELECT DISTINCT tblCouponMaintain.CampaignID, tblCouponMaintain.CampaignVersion, tblCouponMaintain.DeliveryChannel, tblCouponMaintain.TDNumber, tblCouponMaintain.CampaignName, tblCouponMaintain.InHomeDate, tblCouponMaintain.Notes FROM tblCouponMaintain WHERE tblCouponMaintain.DeliveryChannel in('MAIL')"

Forms!frmMail.RecordSource = SqlMl


End If
End If
End If

But its giving me error at this following line. H
Forms!frmPos.RecordSource = SqlPs

How to slove this?

 

[LNBruno]

I'm assuming you verified that the SQL actually runs? What about replacing the "IN" with an equals sign?

< M!ke >

 

[RoyVidar]

You don't say which errormsg, and where in the code, relating to the openform line this is occuring. Makes it a bit hard to assist.

But - it seems you are using the same Select and From Clause in all statements, would it be an option in stead to just pass the where condition when opening the form (and use the table as recordsource), ie:

[tt]strWhere = &quot;DeliveryChannel In ('POS')&quot;
docmd.openform &quot;frm&quot;,,,strWhere[/tt]

Roy-Vidar

 

[Buj123]

Thank you for your quick response. But that change is not solving my problem, because i need to send that sql to another forms recordsource and i am not sure about the actual syntax to do that.

 

[Buj123]

Hi RoyVidar,
The above one doesnot work for me. I think the reason is because the recordsource of the form &quot;frmEmail&quot; already has where clause in it.
What actually i did was i have written in the query(which is there as recordsource for form &quot;FormEmail&quot to take the ID,Ver from this form Form1. Its working, but what if if that field is blank then it won't work. How to write the query saying if it is not null then only take the value from Form1. How to write the condition in query?

 

[RoyVidar]

Two suggestions:

NZ funtion. It might give an alternate value if a control is empty:

[tt]...where mytext = nz(forms!Form1!mytext, &quot;B&quot...[/tt]

or, you could use the like operator with a wildchard:

[tt]...where mytext like forms!Form1!mytext & &quot;*&quot;...[/tt]

- but I don't quite understand &quot;recordsource alredy having a where clause in it&quot; - you are building it here...

Roy-Vidar

 

[Buj123]

Thanks a lot. The second option has solved my problem. Stars for you.....

 

[Buj123]

Hello Roy,
But the second one how it works for Strings and Date. Because i have one text box that will give String name and one for date. How to deal with that, its working fine for numbers.

 

[RoyVidar]

Those suggestions where for meant for the QBE, and they all work in my setup (dates, text and numbers).

Are you using the QBE or writing them in VBA? Think if you're using vba sql strings, some more testing needs to be done, and probably do isnull checks prior to building the statements. Thanx for the star, going offline now...

Roy-Vidar

 

[Buj123]

Yep, it worked for string and Dates also when i use the Like operator, what about the Between Operator, is there a way to say in Between Operator to take all the dates if that field is blank. Please let me know.

 

[RoyVidar]

Sorry it's taken so long, some work and db corruptions created a distraction/absence...

Did you try out the nz function?
(still having problems with my xp (corruption, or whatever), but the following worked on my a2k machine)

[tt]between nz(forms!frm!txt1, #01.01.1900#) and nz(forms!frm!txt1, #01.01.2100#)[/tt]

- or try some dynamic things, like Date() + N...

Roy-Vidar

 

[Buj123]

Yep, I got it, the Nz() function worked out for me. Thanks a lot.