Refreshing subform

[accesskastle]

Ok folks- I’m a first time poster, and I need help, because this problem is
driving me bonkers. I’ve tried DoCmd, SendKeys and requery method (over and
over), reviewing all the previous posts, and all does not seem to be working.
My subform is not refreshing with a button press on the main form. The
mainform’s source is tblWGBRESID, the subform (subform control name
sfrmLINECOVER, sourceobject is sfrmLINECOVERID). Clicking the button is
supposed to cause the addition of whatever is looked up in the third table
(tblLINKLINEID) to the subform. The SQL works; when I open the subform’s
associated table, the data is added to the table. When I close and re-open
the main form, the new data is added to the subform.

But I want the user to see the records in the subform they’ve added after
clicking the command button. I do not want to use DAO. My table setup and
my code for the command button in the main form is posted below. I’m
operating on Access XP, and pretty new to ADO. Please help, and forgive any
breaches in netiquette.

My table setup:
tblLINECOVERID
LINECOVERID-PK
WGBRESID-FK (from tblWGBRESID)
LINKLINEID-FK (from tblLINKLINEID)

tblWGBRESID
WGBRESID-PK
WGBDATE
PEOPLE
HRS
LINKWGBID

tblLINKLINEID
LINKLINEID-PK
LINKWGBID
LINENOID

Private Sub cmdFILLLINES_Click()
Dim CurrConn As New ADODB.Connection
Dim P As String
Dim strSQL As String

Set CurrConn = New ADODB.Connection
P = “my pathwayâ€

With CurrConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source = " & P
.Open
End With

strSQL = "INSERT INTO tblLINECOVERID ( LINKLINEID, WGBRESID )"
strSQL = strSQL & " SELECT tblLINKLINEID.LINKLINEID, "
strSQL = strSQL & [Forms]![frmWGBRESID]![WGBRESID] & " AS WGBRESID "
strSQL = strSQL & "FROM tblLINKLINEID WHERE (((tblLINKLINEID.LINKWGBID) = "
strSQL = strSQL & [Forms]![frmWGBRESID]![LINKWGBID] & "))"
strSQL = strSQL & "GROUP BY tblLINKLINEID.LINKLINEID;"

Debug.Print "strSQL = " & strSQL
CurrConn.Execute strSQL, , adExecuteNoRecords
Debug.Print "Executed strSQL."

CurrConn.Close
Set CurrConn = Nothing

Debug.Print "Set connection to nothing."
Forms("frmWGBRESID")("sfrmLINECOVER").Form.Controls("LINKLINEID").Requery
Debug.Print "Requeried " &
Forms("frmWGBRESID")("sfrmLINECOVER").Form.Controls("LINKLINEID").Name

cmdFILLLINES_Exit:
Exit Sub

cmdFILLLINES_Err:
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume cmdFILLLINES_Exit
End Sub

 

[Dirk Goldgar]

Ok folks- I'm a first time poster, and I need help, because this
problem is driving me bonkers. I've tried DoCmd, SendKeys and
requery method (over and over), reviewing all the previous posts, and
all does not seem to be working. My subform is not refreshing with a
button press on the main form. The mainform's source is tblWGBRESID,
the subform (subform control name sfrmLINECOVER, sourceobject is
sfrmLINECOVERID). Clicking the button is supposed to cause the
addition of whatever is looked up in the third table (tblLINKLINEID)
to the subform. The SQL works; when I open the subform's associated
table, the data is added to the table. When I close and re-open the
main form, the new data is added to the subform.

But I want the user to see the records in the subform they've added
after clicking the command button. I do not want to use DAO. My
table setup and my code for the command button in the main form is
posted below. I'm operating on Access XP, and pretty new to ADO.
Please help, and forgive any breaches in netiquette.

My table setup:
tblLINECOVERID
LINECOVERID-PK
WGBRESID-FK (from tblWGBRESID)
LINKLINEID-FK (from tblLINKLINEID)

tblWGBRESID
WGBRESID-PK
WGBDATE
PEOPLE
HRS
LINKWGBID

tblLINKLINEID
LINKLINEID-PK
LINKWGBID
LINENOID

Private Sub cmdFILLLINES_Click()
Dim CurrConn As New ADODB.Connection
Dim P As String
Dim strSQL As String

Set CurrConn = New ADODB.Connection
P = "my pathway"

With CurrConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source = " & P
.Open
End With

strSQL = "INSERT INTO tblLINECOVERID ( LINKLINEID, WGBRESID )"
strSQL = strSQL & " SELECT tblLINKLINEID.LINKLINEID, "
strSQL = strSQL & [Forms]![frmWGBRESID]![WGBRESID] & " AS WGBRESID "
strSQL = strSQL & "FROM tblLINKLINEID WHERE
(((tblLINKLINEID.LINKWGBID) = " strSQL = strSQL &
[Forms]![frmWGBRESID]![LINKWGBID] & "))"
strSQL = strSQL & "GROUP BY tblLINKLINEID.LINKLINEID;"

Debug.Print "strSQL = " & strSQL
CurrConn.Execute strSQL, , adExecuteNoRecords
Debug.Print "Executed strSQL."

CurrConn.Close
Set CurrConn = Nothing

Debug.Print "Set connection to nothing."
Forms("frmWGBRESID")("sfrmLINECOVER").Form.Controls("LINKLINEID").Requer
y
Debug.Print "Requeried " &
Forms("frmWGBRESID")("sfrmLINECOVER").Form.Controls("LINKLINEID").Name

cmdFILLLINES_Exit:
Exit Sub

cmdFILLLINES_Err:
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume cmdFILLLINES_Exit
End Sub

I see two separate possible problems. The first one to address is that
you are not in fact requerying or refreshing the subform. You are
requerying a specific control on that form. Try this:

Forms("frmWGBRESID").Controls("sfrmLINECOVER").Form.Requery

Although I would just write it ...

Forms!frmWGBRESID!sfrmLINECOVER.Form.Requery

or even ...

Me!sfrmLINECOVER.Form.Requery

if this code is running on form frmWGBRESID.

Note that calling the subform's Refresh method won't work, because that
will only refresh the records currently displayed by the form; it won't
add records that have since been added to the form's recordsource.

The second possible problem I see is that you are opening a separate
connection to the database and adding your record via that connection.
I don't see a good reason for doing that, and it may conceivably add a
latency period such that the current connection used Access for the form
and subform don't know about the added record yet. Why not just use the
same connection Access is using? Like this:

'----- start of suggested code -----
Private Sub cmdFILLLINES_Click()

Dim CurrConn As ADODB.Connection
Dim strSQL As String

Set CurrConn = CurrentProject.Connection

strSQL = _
"INSERT INTO tblLINECOVERID ( LINKLINEID, WGBRESID )" & _
" SELECT tblLINKLINEID.LINKLINEID, " & _
[Forms]![frmWGBRESID]![WGBRESID] & " AS WGBRESID " & _
"FROM tblLINKLINEID WHERE (((tblLINKLINEID.LINKWGBID) = " & _
[Forms]![frmWGBRESID]![LINKWGBID] & ")) " & _
"GROUP BY tblLINKLINEID.LINKLINEID;"

Debug.Print "strSQL = " & strSQL
CurrConn.Execute strSQL, , adExecuteNoRecords
Debug.Print "Executed strSQL."

Set CurrConn = Nothing ' don't close current connection!

Forms!frmWGBRESID!sfrmLINECOVER.Form.Requery
Debug.Print "Requeried"

cmdFILLLINES_Exit:
Exit Sub

cmdFILLLINES_Err:
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume cmdFILLLINES_Exit
End Sub
'----- end of suggested code -----

If frmWGBRESID is, in fact, the form where this code is running, then
all references to "Forms!frmWGBRESID" can be replaced by "Me!".

I don't know for sure if either of these issues is the source of your
problem, but that's how I'd go about it.

By the way, why don't you want to use DAO?

 

[accesskastle]

Thanks for the suggestions; I'll give them a shot. The main reason I didn't
want to use DAO is because I'm trying to ween myself off it, and making a
sincere effort not to use it in this database. Although I like DAO's fairly
intuitive structure, I've been hearing that ADO is the thing of the future,
and this mdb will be used to collect data for a 10 year goal. Upsizing, if
necessary, may also be easier managed if I'm more familiar with ADO.

accesskastle

Ok folks- I'm a first time poster, and I need help, because this
problem is driving me bonkers. I've tried DoCmd, SendKeys and
requery method (over and over), reviewing all the previous posts, and
all does not seem to be working. My subform is not refreshing with a
button press on the main form. The mainform's source is tblWGBRESID,
the subform (subform control name sfrmLINECOVER, sourceobject is
sfrmLINECOVERID). Clicking the button is supposed to cause the
addition of whatever is looked up in the third table (tblLINKLINEID)
to the subform. The SQL works; when I open the subform's associated
table, the data is added to the table. When I close and re-open the
main form, the new data is added to the subform.

But I want the user to see the records in the subform they've added
after clicking the command button. I do not want to use DAO. My
table setup and my code for the command button in the main form is
posted below. I'm operating on Access XP, and pretty new to ADO.
Please help, and forgive any breaches in netiquette.

My table setup:
tblLINECOVERID
LINECOVERID-PK
WGBRESID-FK (from tblWGBRESID)
LINKLINEID-FK (from tblLINKLINEID)

tblWGBRESID
WGBRESID-PK
WGBDATE
PEOPLE
HRS
LINKWGBID

tblLINKLINEID
LINKLINEID-PK
LINKWGBID
LINENOID

Private Sub cmdFILLLINES_Click()
Dim CurrConn As New ADODB.Connection
Dim P As String
Dim strSQL As String

Set CurrConn = New ADODB.Connection
P = "my pathway"

With CurrConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source = " & P
.Open
End With

strSQL = "INSERT INTO tblLINECOVERID ( LINKLINEID, WGBRESID )"
strSQL = strSQL & " SELECT tblLINKLINEID.LINKLINEID, "
strSQL = strSQL & [Forms]![frmWGBRESID]![WGBRESID] & " AS WGBRESID "
strSQL = strSQL & "FROM tblLINKLINEID WHERE
(((tblLINKLINEID.LINKWGBID) = " strSQL = strSQL &
[Forms]![frmWGBRESID]![LINKWGBID] & "))"
strSQL = strSQL & "GROUP BY tblLINKLINEID.LINKLINEID;"

Debug.Print "strSQL = " & strSQL
CurrConn.Execute strSQL, , adExecuteNoRecords
Debug.Print "Executed strSQL."

CurrConn.Close
Set CurrConn = Nothing

Debug.Print "Set connection to nothing."
Forms("frmWGBRESID")("sfrmLINECOVER").Form.Controls("LINKLINEID").Requer
y
Debug.Print "Requeried " &
Forms("frmWGBRESID")("sfrmLINECOVER").Form.Controls("LINKLINEID").Name

cmdFILLLINES_Exit:
Exit Sub

cmdFILLLINES_Err:
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume cmdFILLLINES_Exit
End Sub

I see two separate possible problems. The first one to address is that
you are not in fact requerying or refreshing the subform. You are
requerying a specific control on that form. Try this:

Forms("frmWGBRESID").Controls("sfrmLINECOVER").Form.Requery

Although I would just write it ...

Forms!frmWGBRESID!sfrmLINECOVER.Form.Requery

or even ...

Me!sfrmLINECOVER.Form.Requery

if this code is running on form frmWGBRESID.

Note that calling the subform's Refresh method won't work, because that
will only refresh the records currently displayed by the form; it won't
add records that have since been added to the form's recordsource.

The second possible problem I see is that you are opening a separate
connection to the database and adding your record via that connection.
I don't see a good reason for doing that, and it may conceivably add a
latency period such that the current connection used Access for the form
and subform don't know about the added record yet. Why not just use the
same connection Access is using? Like this:

'----- start of suggested code -----
Private Sub cmdFILLLINES_Click()

Dim CurrConn As ADODB.Connection
Dim strSQL As String

Set CurrConn = CurrentProject.Connection

strSQL = _
"INSERT INTO tblLINECOVERID ( LINKLINEID, WGBRESID )" & _
" SELECT tblLINKLINEID.LINKLINEID, " & _
[Forms]![frmWGBRESID]![WGBRESID] & " AS WGBRESID " & _
"FROM tblLINKLINEID WHERE (((tblLINKLINEID.LINKWGBID) = " & _
[Forms]![frmWGBRESID]![LINKWGBID] & ")) " & _
"GROUP BY tblLINKLINEID.LINKLINEID;"

Debug.Print "strSQL = " & strSQL
CurrConn.Execute strSQL, , adExecuteNoRecords
Debug.Print "Executed strSQL."

Set CurrConn = Nothing ' don't close current connection!

Forms!frmWGBRESID!sfrmLINECOVER.Form.Requery
Debug.Print "Requeried"

cmdFILLLINES_Exit:
Exit Sub

cmdFILLLINES_Err:
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume cmdFILLLINES_Exit
End Sub
'----- end of suggested code -----

If frmWGBRESID is, in fact, the form where this code is running, then
all references to "Forms!frmWGBRESID" can be replaced by "Me!".

I don't know for sure if either of these issues is the source of your
problem, but that's how I'd go about it.

By the way, why don't you want to use DAO?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[accesskastle]

Thanks Dirk! It worked! You were right about the delay caused by opening a
new connection; it must have been preventing the subform from refreshing in a
timely manner.

 

[Dirk Goldgar]

Thanks for the suggestions; I'll give them a shot. The main reason I
didn't want to use DAO is because I'm trying to ween myself off it,
and making a sincere effort not to use it in this database. Although
I like DAO's fairly intuitive structure, I've been hearing that ADO
is the thing of the future, and this mdb will be used to collect data
for a 10 year goal. Upsizing, if necessary, may also be easier
managed if I'm more familiar with ADO.

ADO is not actually the thing of the future -- it has already been
supplanted in that role by ADO.Net, which may sound the similar but
which I gather is substantially different than ADO. Since DAO is still
the optimal object library for working with Jet databases -- and note
that DAO has been restored as a default library in Access 2003 -- while
ADO has been deprecated in favor of ADO.Net, I'd say that you might do
better to teach yourself ADO.Net and continue using DAO in Access work
at least until support for ADO.Net is incorporated in Access. But
that's just my personal opinion, and shouldn't be taken as an
authoritative pronouncement.

 

[Dirk Goldgar]

Thanks Dirk! It worked! You were right about the delay caused by
opening a new connection; it must have been preventing the subform
from refreshing in a timely manner.

Great! I have a very similar circumstance causing trouble in another
newsgroup thread I'm participating in.