Setting a Form's recordset at design time

  • Thread starter Thread starter Ed Warren
  • Start date Start date
E

Ed Warren

What I'm trying to do!
I cannot strictly enforce One user one front-end requirement, therefore, I
need to build a tempory table the user(s) can use to edit data then 'post'
back updates (disconnected record set look-alike).

I have the code working to build a query string and execute it against the
database and build a tabledef object that I can use (if I could connect up
to it)

I want each instance of the form to connect to their own temp table, then
when I'm done and close the form, I will post the data to where it goes and
delete the table.

(would really like to do this with ADO (ado.net) recordset, but am brain
limited by knowledge and ado.net vs ado and which access uses, so back to
good ole' dao!!!)

btw:
me.recordset = thisTable -- fails in the same manner.
All suggestions welcome

Ed Warren

------------------------------------------------------------------------
the sub below fails on the step
Me.Recordset = dbs.TableDefs(thisTable)

Error:

Run-time error '91'

Object variable or With block variable not set



----------------------------------------------------------code------------------------------------------------

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
Dim thisTherapist As Long
Dim thisSite As Long
Dim thisDate As Date

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim thisQuery As String
Dim thisTable As String
Dim tableExists As Integer


Set dbs = CurrentDb

'thisQuery = "BuildEncounterList" & [Forms]![frmEditEncounters]![textUser]

thisTable = "EncounterList" & [Forms]![frmEditEncounters]![textUser]

tableExists = IsTableQuery("", thisTable)
If tableExists = True Then
dbs.TableDefs.Delete (thisTable)
End If

thisTherapist =
[Forms]![frmEditEncounters]![TherapistList].[Form]![Therapist_ID]
thisSite = [Forms]![frmEditEncounters]![SiteList].[Form]![SiteID]
thisDate = [Forms]![frmEditEncounters]![DateList].[Form]![Date]

strSQL = "SELECT qryDayEncounters.* INTO " & thisTable
strSQL = strSQL & " FROM qryDayEncounters LEFT JOIN IndividualBilling ON
qryDayEncounters.Sched_Encounter_ID = IndividualBilling.Sched_Encounter_ID"
strSQL = strSQL & " WHERE (((qryDayEncounters.CData) Not Like ""*~*"") AND
((IndividualBilling.Sched_Encounter_ID) Is Null) "
strSQL = strSQL & " AND ((qryDayEncounters.Therapist_ID)= "
strSQL = strSQL & thisTherapist
strSQL = strSQL & ") AND ((qryDayEncounters.Site_ID)= "
strSQL = strSQL & thisSite
strSQL = strSQL & ") AND ((qryDayEncounters.Date)= #"
strSQL = strSQL & thisDate & "#) AND ((qryDayEncounters.Display)=True))"
strSQL = strSQL & "ORDER BY qryDayEncounters.CData;"
dbs.Execute strSQL, dbFailOnError

Set dbs = CurrentDb
dbs.TableDefs.Refresh
Me.Recordset = dbs.TableDefs(thisTable)
End Sub
 
Hi, Ed.

Try:

Me.Recordset = dbs.TableDefs("thisTable")


Ed Warren said:
What I'm trying to do!
I cannot strictly enforce One user one front-end requirement, therefore, I
need to build a tempory table the user(s) can use to edit data then 'post'
back updates (disconnected record set look-alike).

I have the code working to build a query string and execute it against the
database and build a tabledef object that I can use (if I could connect up
to it)

I want each instance of the form to connect to their own temp table, then
when I'm done and close the form, I will post the data to where it goes and
delete the table.

(would really like to do this with ADO (ado.net) recordset, but am brain
limited by knowledge and ado.net vs ado and which access uses, so back to
good ole' dao!!!)

btw:
me.recordset = thisTable -- fails in the same manner.
All suggestions welcome

Ed Warren

------------------------------------------------------------------------
the sub below fails on the step
Me.Recordset = dbs.TableDefs(thisTable)

Error:

Run-time error '91'

Object variable or With block variable not set



----------------------------------------------------------code------------------------------------------------

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
Dim thisTherapist As Long
Dim thisSite As Long
Dim thisDate As Date

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim thisQuery As String
Dim thisTable As String
Dim tableExists As Integer


Set dbs = CurrentDb

'thisQuery = "BuildEncounterList" & [Forms]![frmEditEncounters]![textUser]

thisTable = "EncounterList" & [Forms]![frmEditEncounters]![textUser]

tableExists = IsTableQuery("", thisTable)
If tableExists = True Then
dbs.TableDefs.Delete (thisTable)
End If

thisTherapist =
[Forms]![frmEditEncounters]![TherapistList].[Form]![Therapist_ID]
thisSite = [Forms]![frmEditEncounters]![SiteList].[Form]![SiteID]
thisDate = [Forms]![frmEditEncounters]![DateList].[Form]![Date]

strSQL = "SELECT qryDayEncounters.* INTO " & thisTable
strSQL = strSQL & " FROM qryDayEncounters LEFT JOIN IndividualBilling ON
qryDayEncounters.Sched_Encounter_ID = IndividualBilling.Sched_Encounter_ID"
strSQL = strSQL & " WHERE (((qryDayEncounters.CData) Not Like ""*~*"") AND
((IndividualBilling.Sched_Encounter_ID) Is Null) "
strSQL = strSQL & " AND ((qryDayEncounters.Therapist_ID)= "
strSQL = strSQL & thisTherapist
strSQL = strSQL & ") AND ((qryDayEncounters.Site_ID)= "
strSQL = strSQL & thisSite
strSQL = strSQL & ") AND ((qryDayEncounters.Date)= #"
strSQL = strSQL & thisDate & "#) AND ((qryDayEncounters.Display)=True))"
strSQL = strSQL & "ORDER BY qryDayEncounters.CData;"
dbs.Execute strSQL, dbFailOnError

Set dbs = CurrentDb
dbs.TableDefs.Refresh
Me.Recordset = dbs.TableDefs(thisTable)
End Sub
Click to expand...
 
Thanks, that might work if the name of the table was "thisTable" but what
I'm trying to do is use the variable thisTable = "EncounterListUserName" to
connect to the form.

I've been able to build the table "EncounterListUserName" in the code, just
can't get the program to recognize the new tabledef objec.

Thanks,

Ed Warren

Sprinks said:
Hi, Ed.

Try:

Me.Recordset = dbs.TableDefs("thisTable")


Ed Warren said:
What I'm trying to do!
I cannot strictly enforce One user one front-end requirement, therefore,
I
need to build a tempory table the user(s) can use to edit data then
'post'
back updates (disconnected record set look-alike).

I have the code working to build a query string and execute it against
the
database and build a tabledef object that I can use (if I could connect
up
to it)

I want each instance of the form to connect to their own temp table, then
when I'm done and close the form, I will post the data to where it goes
and
delete the table.

(would really like to do this with ADO (ado.net) recordset, but am brain
limited by knowledge and ado.net vs ado and which access uses, so back to
good ole' dao!!!)

btw:
me.recordset = thisTable -- fails in the same manner.
All suggestions welcome

Ed Warren

------------------------------------------------------------------------
the sub below fails on the step
Me.Recordset = dbs.TableDefs(thisTable)

Error:

Run-time error '91'

Object variable or With block variable not set



----------------------------------------------------------code------------------------------------------------

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
Dim thisTherapist As Long
Dim thisSite As Long
Dim thisDate As Date

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim thisQuery As String
Dim thisTable As String
Dim tableExists As Integer


Set dbs = CurrentDb

'thisQuery = "BuildEncounterList" &
[Forms]![frmEditEncounters]![textUser]

thisTable = "EncounterList" & [Forms]![frmEditEncounters]![textUser]

tableExists = IsTableQuery("", thisTable)
If tableExists = True Then
dbs.TableDefs.Delete (thisTable)
End If

thisTherapist =
[Forms]![frmEditEncounters]![TherapistList].[Form]![Therapist_ID]
thisSite = [Forms]![frmEditEncounters]![SiteList].[Form]![SiteID]
thisDate = [Forms]![frmEditEncounters]![DateList].[Form]![Date]

strSQL = "SELECT qryDayEncounters.* INTO " & thisTable
strSQL = strSQL & " FROM qryDayEncounters LEFT JOIN IndividualBilling ON
qryDayEncounters.Sched_Encounter_ID =
IndividualBilling.Sched_Encounter_ID"
strSQL = strSQL & " WHERE (((qryDayEncounters.CData) Not Like ""*~*"")
AND
((IndividualBilling.Sched_Encounter_ID) Is Null) "
strSQL = strSQL & " AND ((qryDayEncounters.Therapist_ID)= "
strSQL = strSQL & thisTherapist
strSQL = strSQL & ") AND ((qryDayEncounters.Site_ID)= "
strSQL = strSQL & thisSite
strSQL = strSQL & ") AND ((qryDayEncounters.Date)= #"
strSQL = strSQL & thisDate & "#) AND ((qryDayEncounters.Display)=True))"
strSQL = strSQL & "ORDER BY qryDayEncounters.CData;"
dbs.Execute strSQL, dbFailOnError

Set dbs = CurrentDb
dbs.TableDefs.Refresh
Me.Recordset = dbs.TableDefs(thisTable)
End Sub
Click to expand...
Click to expand...
 
Ed,

You can refer to a table object via a string variable by:

Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb()
Set tdf = db.TableDefs(strName)

Sprinks

Ed Warren said:
Thanks, that might work if the name of the table was "thisTable" but what
I'm trying to do is use the variable thisTable = "EncounterListUserName" to
connect to the form.

I've been able to build the table "EncounterListUserName" in the code, just
can't get the program to recognize the new tabledef objec.

Thanks,

Ed Warren

Sprinks said:
Hi, Ed.

Try:

Me.Recordset = dbs.TableDefs("thisTable")


Ed Warren said:
What I'm trying to do!
I cannot strictly enforce One user one front-end requirement, therefore,
I
need to build a tempory table the user(s) can use to edit data then
'post'
back updates (disconnected record set look-alike).

I have the code working to build a query string and execute it against
the
database and build a tabledef object that I can use (if I could connect
up
to it)

I want each instance of the form to connect to their own temp table, then
when I'm done and close the form, I will post the data to where it goes
and
delete the table.

(would really like to do this with ADO (ado.net) recordset, but am brain
limited by knowledge and ado.net vs ado and which access uses, so back to
good ole' dao!!!)

btw:
me.recordset = thisTable -- fails in the same manner.
All suggestions welcome

Ed Warren

------------------------------------------------------------------------
the sub below fails on the step
Me.Recordset = dbs.TableDefs(thisTable)

Error:

Run-time error '91'

Object variable or With block variable not set



----------------------------------------------------------code------------------------------------------------

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
Dim thisTherapist As Long
Dim thisSite As Long
Dim thisDate As Date

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim thisQuery As String
Dim thisTable As String
Dim tableExists As Integer


Set dbs = CurrentDb

'thisQuery = "BuildEncounterList" &
[Forms]![frmEditEncounters]![textUser]

thisTable = "EncounterList" & [Forms]![frmEditEncounters]![textUser]

tableExists = IsTableQuery("", thisTable)
If tableExists = True Then
dbs.TableDefs.Delete (thisTable)
End If

thisTherapist =
[Forms]![frmEditEncounters]![TherapistList].[Form]![Therapist_ID]
thisSite = [Forms]![frmEditEncounters]![SiteList].[Form]![SiteID]
thisDate = [Forms]![frmEditEncounters]![DateList].[Form]![Date]

strSQL = "SELECT qryDayEncounters.* INTO " & thisTable
strSQL = strSQL & " FROM qryDayEncounters LEFT JOIN IndividualBilling ON
qryDayEncounters.Sched_Encounter_ID =
IndividualBilling.Sched_Encounter_ID"
strSQL = strSQL & " WHERE (((qryDayEncounters.CData) Not Like ""*~*"")
AND
((IndividualBilling.Sched_Encounter_ID) Is Null) "
strSQL = strSQL & " AND ((qryDayEncounters.Therapist_ID)= "
strSQL = strSQL & thisTherapist
strSQL = strSQL & ") AND ((qryDayEncounters.Site_ID)= "
strSQL = strSQL & thisSite
strSQL = strSQL & ") AND ((qryDayEncounters.Date)= #"
strSQL = strSQL & thisDate & "#) AND ((qryDayEncounters.Display)=True))"
strSQL = strSQL & "ORDER BY qryDayEncounters.CData;"
dbs.Execute strSQL, dbFailOnError

Set dbs = CurrentDb
dbs.TableDefs.Refresh
Me.Recordset = dbs.TableDefs(thisTable)
End Sub
Click to expand...
Click to expand...
Click to expand...
 
In VBA you need to use Set when assigning to an object ...

"Set Me.Recordset = Whatever" rather than just "Me.Recordset = Whatever"

--
Brendan Reynolds (MVP)

Ed Warren said:
Thanks, that might work if the name of the table was "thisTable" but what
I'm trying to do is use the variable thisTable = "EncounterListUserName"
to connect to the form.

I've been able to build the table "EncounterListUserName" in the code,
just can't get the program to recognize the new tabledef objec.

Thanks,

Ed Warren

Sprinks said:
Hi, Ed.

Try:

Me.Recordset = dbs.TableDefs("thisTable")


Ed Warren said:
What I'm trying to do!
I cannot strictly enforce One user one front-end requirement, therefore,
I
need to build a tempory table the user(s) can use to edit data then
'post'
back updates (disconnected record set look-alike).

I have the code working to build a query string and execute it against
the
database and build a tabledef object that I can use (if I could connect
up
to it)

I want each instance of the form to connect to their own temp table,
then
when I'm done and close the form, I will post the data to where it goes
and
delete the table.

(would really like to do this with ADO (ado.net) recordset, but am brain
limited by knowledge and ado.net vs ado and which access uses, so back
to
good ole' dao!!!)

btw:
me.recordset = thisTable -- fails in the same manner.
All suggestions welcome

Ed Warren

------------------------------------------------------------------------
the sub below fails on the step
Me.Recordset = dbs.TableDefs(thisTable)

Error:

Run-time error '91'

Object variable or With block variable not set



----------------------------------------------------------code------------------------------------------------

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
Dim thisTherapist As Long
Dim thisSite As Long
Dim thisDate As Date

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim thisQuery As String
Dim thisTable As String
Dim tableExists As Integer


Set dbs = CurrentDb

'thisQuery = "BuildEncounterList" &
[Forms]![frmEditEncounters]![textUser]

thisTable = "EncounterList" & [Forms]![frmEditEncounters]![textUser]

tableExists = IsTableQuery("", thisTable)
If tableExists = True Then
dbs.TableDefs.Delete (thisTable)
End If

thisTherapist =
[Forms]![frmEditEncounters]![TherapistList].[Form]![Therapist_ID]
thisSite = [Forms]![frmEditEncounters]![SiteList].[Form]![SiteID]
thisDate = [Forms]![frmEditEncounters]![DateList].[Form]![Date]

strSQL = "SELECT qryDayEncounters.* INTO " & thisTable
strSQL = strSQL & " FROM qryDayEncounters LEFT JOIN IndividualBilling ON
qryDayEncounters.Sched_Encounter_ID =
IndividualBilling.Sched_Encounter_ID"
strSQL = strSQL & " WHERE (((qryDayEncounters.CData) Not Like ""*~*"")
AND
((IndividualBilling.Sched_Encounter_ID) Is Null) "
strSQL = strSQL & " AND ((qryDayEncounters.Therapist_ID)= "
strSQL = strSQL & thisTherapist
strSQL = strSQL & ") AND ((qryDayEncounters.Site_ID)= "
strSQL = strSQL & thisSite
strSQL = strSQL & ") AND ((qryDayEncounters.Date)= #"
strSQL = strSQL & thisDate & "#) AND ((qryDayEncounters.Display)=True))"
strSQL = strSQL & "ORDER BY qryDayEncounters.CData;"
dbs.Execute strSQL, dbFailOnError

Set dbs = CurrentDb
dbs.TableDefs.Refresh
Me.Recordset = dbs.TableDefs(thisTable)
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top