Setting a Form's recordset at design time

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
 
G

Guest

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...
 
E

Ed Warren

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...
 
G

Guest

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...
 
B

Brendan Reynolds

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update the subform or refresh based on multiple selection in a lis 1
Setting a combo Rowsource from SQL server 2
Setting the RecordSource dynamically with a global variable 3
checking data in a field and then setting focus 1
RecordSet Clone 7
How to creat a Query in Code 20
multi select list box in a form as a query parameter 3
Using a recordset as a reports data source 3

Top