Opening a form from 2 recordsets

[Richey]

I have a form, with a hidden field, showing the current user logged on (cuser). I want another form to open, IF it matches criteria set as strsql (which works) but also where strsql1 criteria is met i.e. the current user = the person logged on. How do I add the criteria (set by strsql1) to the line DoCmd.OpenForm "f_extra_visits" etc?
i.e. the form only opens when criteria for strsql AND strsql1 are met? NOT just strsql as is happening at the moment
Thanks
Richey

Dim dbs As Database, strmsg, strsql, strsql1 As String, rst, rst1 As Recordset
Set dbs = CurrentDb
strsql1 = "SELECT * FROM Inspectors WHERE Inspectors.login = '" & CUser & "';"
Set rst1 = dbs.OpenRecordset(strsql1)
strsql = "SELECT * FROM fextra_visits_q"
Set rst = dbs.OpenRecordset(strsql)
If rst.RecordCount > 0 Then
DoCmd.OpenForm "f_extra_visits", acNormal, strsql, acFormReadOnly
Else
strmsg = "Extra visit dates? "
strmsg = MsgBox(strmsg, vbOKOnly, "No extra visit dates required&quot

Exit Sub
End If

 

[kathryn]

what about

If rst.RecordCount > 0 AND rst1.recordcount>0 Then
DoCmd.OpenForm "f_extra_visits", acNormal, strsql, acFormReadOnly

would that do what you want?


Kathryn

 

[Richey]

Hi Kathryn

Thanks but it didn't work.
The strsql1 line contains the hidden field 'cuser' and field 'login' from table 'Inspectors'
An example would be the 'cuser' John logs in, which is also a login name from the Inspectors table, so they match. Therefore I would want the form to open based on strsql (which it does) and strsql1. I think I need to include it in the line
DoCmd.OpenForm etc etc (I only have strsql in there as a filtername) but I don't know where to place it etc

Richey

 

[kathryn]

OK, I need a bit of clarification. Do you want the second form to open only if the user is logged on, or open to a specific set of records based on the user logged on?

Kathryn

 

[Richey]

well......yeah I only want the 2nd form to open if the user is logged on, but he/she would only see the 2nd form if they had dates o/standing, otherwise they get a mesage "No extra dates"
So, say John & Sarah are both logged on, and John has dates o/standing then he would get the 2nd form whilst Sarah would get the 'No extra dates'message

 

[kathryn]

OK, is there a field value in the strSQL1 recordset (rst1)that you want to use as a criteria for a field in your strSQL? If so,

strsql = "SELECT * FROM fextra_visits_q WHERE YourFieldName =" & rst!FieldValueFromRST1


Kathryn

 

[Richey]

No there is no field value - I've probably gone the wrong way about this, you can tell I'm new to it!

 

[kathryn]

I think we are making this way too complicated. Let's go back to basics. If the user is NOT logged in:

a) how do you know that, is the recordcount of rst1 = 0

b) what do you want to have happen?



Kathryn