Form RecordSource Question 3

[fazm]

I have a query that is creating the record source for a form (form_load event):

strSQL = SELECT a.ClientID, a.ClientFirstNm, a.ClientLastNm, b.EmployeeFirstNm,b.EmployeeLastNm, b.CaseLoadID
FROM tblClient AS a, tblEmployee AS b
WHERE a.CaseLoadID=b.CaseLoadID And ClientID=[Enter Social Security Number]

Me.RecordSource = strSQL

This works fine, but I also want to use the a.ClientID portion of the recordset in another query to check something else.

What is the syntax for this, I have been trying:

Me.Recordset(0)
Me.Recordset(ClientID)
Me.RecordSource.Fields(0)
Me.RecordSource(0)
Me.RecordSource(ClientID)

It seems straight forward, but I am having no luck....any ideas?

Thanks!

 

[grtammi]

If you're using DAO, could try the following:

Dim varFieldValue as variant

dim db as dao.database:  set db = currentdb
dim rs as dao.recordset: set rs = db.openrecordset(me.recordsource)

varFieldValue = rs(0)

set rs = nothing
set db = nothing

If you're using ADO, try this:

dim conn as ADODB.Connection:  set conn = currentproject.connection
dim rs as ADODB.Recordset: set rs = createobject("ADODB.Recordset")

Dim varFieldValue as variant

' make sure form is using a SQL recordsource
if instr(1, me.recordsource, "SELECT") then
   ' open recordset
   rs.open me.recordsource, conn
   varFieldValue = rs(0)
end if

All error checking is responsibility of the user!

I think this'll work for you. I also think that the reason it wasn't working for you the other way is that Me.Recordsource is not an object, but a value, and since values don't have fields ... however, the rs object does have fields associated with it, which you can then access as needed.

HTH

Greg

Boss quote from an office meeting: We're going to continue to have these meetings until we figure out why no work is getting done ...

 

[DaltonVB]

Once you have set the record source for the form, make sure one of the controls (hopefully a text box) has tblClient.ClientID as it's control source and reference it to retrieve the id. This will be the first record of the record source so... if you want to have a specific ClientID, indicate that by either using it as a criteria in the SQL statement for the forms record source or use the forms filter. There are many other ways to do this though.

 

[cmmrfrds]

Where are you checking the fields? You can reference the recordset record by relative position or name. You need to use the fields collection of the recordset to use the name.

Me.Recordset(0)
Me.Recordset.Fields("ClientID&quot

 

[fazm]

Thanks for the lightning fast responses, each one was very helpful and now I can tackle this from different angles.

You guys are the best...thanks again!!