Using OpenArgs to Pass a Variable's content

[GrahamK]

Dear All,

I appear to be struggling to get the OpenArgs property of a DoCmd.OpenForm statement to work. The "calling" form's code is:

Public Function lstUser2_Click()
Dim intuserid As Integer
intuserid = Forms!frmUsrQualSearch1.List0.Column(0)
DoCmd.OpenForm ("frmUsrQualSearch2",,,,,,intuserid)
DoCmd.Close acForm, "frmUsrQualSearch1"
End Function

The Receiving Code is:

Private Sub Form_Load()
Dim intuserid As Integer
Dim strsql As String
strsql = "SELECT tblStaffQual.StaffQualID, tblQuals.Qual_Name " & _
        "FROM tblATFStaff RIGHT JOIN (tblQuals RIGHT JOIN tblStaffQual ON tblQuals.Qual_ID = tblStaffQual.ATFQual_ID) ON tblATFStaff.ATFStaffID = tblStaffQual.ATFStaff_ID " & _
        "WHERE (((tblStaffQual.ATFStaff_ID)=" & intuserid & "));"
Debug.Print strsql
intuserid = Me.OpenArgs
List0.RowSourceType = "Table/Query"
List0.RowSource = strsql
End Sub

What I am trying to do is pass the value in intuserid through to input it into the SQL statement. Please can anyone help?

Many Thanks
Graham

 

[namliam]

Private Sub Form_Load()
Dim intuserid As Integer
Dim strsql As String
strsql = "SELECT tblStaffQual.StaffQualID, tblQuals.Qual_Name " & _
        "FROM tblATFStaff RIGHT JOIN (tblQuals RIGHT JOIN tblStaffQual ON tblQuals.Qual_ID = tblStaffQual.ATFQual_ID) ON tblATFStaff.ATFStaffID = tblStaffQual.ATFStaff_ID " & _
        "WHERE (((tblStaffQual.ATFStaff_ID)=" & [U][B]intuserid [/B][/U]& "));"
Debug.Print strsql
[B][U]intuserid = Me.OpenArgs[/U][/B]
List0.RowSourceType = "Table/Query"
List0.RowSource = strsql
End Sub

Or is that too obvious?? I think you have to fill the intuserid before you use it??

Happy coding

 

[GrahamK]

Hi Mailman,

Well that s what I thought, hence why I wrote it - but it's returning errors relating to a null value when the second form loads, so nothing's actually being ported between the forms...

Many Thanks
Graham

 

[namliam]

Yes that is RIGHT!
your intuserid is still NULL when you insert it into the SQL STRING.

You are inserting the intuserid into the strSQL first, then loading it with the Openargs... this has to be reversed...

Private Sub Form_Load()
Dim intuserid As Integer
Dim strsql As String
[U][B]intuserid = Me.OpenArgs[/B][/U]
strsql = "SELECT tblStaffQual.StaffQualID, tblQuals.Qual_Name " & _
        "FROM tblATFStaff RIGHT JOIN (tblQuals RIGHT JOIN tblStaffQual ON tblQuals.Qual_ID = tblStaffQual.ATFQual_ID) ON tblATFStaff.ATFStaffID = tblStaffQual.ATFStaff_ID " & _
        "WHERE (((tblStaffQual.ATFStaff_ID)=" & intuserid & "));"
Debug.Print strsql
[U][B]' intuserid = Me.OpenArgs ' Removed from here, its useless... And moved up[/B][/U]
List0.RowSourceType = "Table/Query"
List0.RowSource = strsql
End Sub

 

[GrahamK]

Hi Mailman,

OK, I've moved those lines around and now am getting a syntax error on the line

DoCmd.OpenForm ("frmUsrQualSearch2",,,,,,intuserid)

in the "transmitting" form. I thought I could declare a variable in the [OpenArgs] part of DoCmd.OpenForm.....????

Many Thanks
Graham

 

[namliam]

Try without the ()

You cannot declare a variable in the Openargs, it has to be declared using a DIM command.

But if intUserID is DIM-ed then you can do it this way....

 

[SOS]

Just a note -

OpenArgs is a STRING so you need to pass it as a string (not an integer) and then you can convert it when using it.

 

[GrahamK]

Try without the ()

You cannot declare a variable in the Openargs, it has to be declared using a DIM command.

But if intUserID is DIM-ed then you can do it this way....

Many Thanks Mailman, I took the () out and all appears to work..... Strange..............

 

[namliam]

() Denotes it is a function and then wants to send back a value....
Typicaly
SomeVariable = Docmd.openform ()
Or
SomeVar = ThisSub (a,b)

While
Thissub a,b
will execute just fine as well..