below i've included the code for you to see. believe it
or not, it is formatted neatly. Just paste it into word
pad and it should look fine. i've included some comment
lines just to give you a rough idea of what's going on.
thanks.
Kevin
'---Begin Code---'
'The following code occurs in the On Current event
property of a form called 'group'
'The intially checks the action field of each record. If
the action field is true, then variable 'selected' is
incremented.
'The next section of code checks the final value of
variable 'selected'. A new variable YorN is assigned a
string value based on the value of 'selected'
'The string stored in YorN is the last part of the SQL
statement refered to in qryTest2.SQL.
Dim number, selected, qty As Integer
Dim phrase1, phrase2, YorN As String
Dim dbs As Database
Dim rst1 As DAO.Recordset
Dim qryTest1, qryTest2 As QueryDef
Set dbs = CurrentDb
Set rst1 = Forms!home!project!group.Form.RecordsetClone
selected = 0
If rst1.RecordCount <> 0 Then
rst1.MoveLast
Do Until rst1.BOF
If rst1!action = True Then
selected = selected + 1
End If
rst1.MovePrevious
Loop
End If
If selected = 0 Then
YorN = ") ORDER BY estimateLabor.groupLineItem"
Else
YorN = " And ((group.action) = Yes)) ORDER BY
estimateLabor.groupLineItem"
End If
Set qryTest2 = dbs.QueryDefs("laborQuery")
'The following SQL statement DOES execute without error.
qryTest2.SQL = "SELECT estimateLabor.recordID,
estimateLabor.recordType, estimateLabor.projectRecordID,"
& _
"estimateLabor.descriptionOfLabor,
estimateLabor.unitCost, estimateLabor.extendedPrice,
estimateLabor.quantity," & _
"estimateLabor.lineItem, estimateLabor.group,
estimateLabor.extendedTime, estimateLabor.unitTime," & _
"estimateLabor.groupLineItem,
estimateLabor.changeOrderNumber, estimateLabor.memo,
estimateLabor.action As laborAction," & _
"estimateLabor.pendingAction, project.actionAll,
estimateLabor.invoiceNumber, group.action" & _
" FROM [group] Right JOIN (estimateLabor INNER
JOIN project" & _
" ON
estimateLabor.projectRecordID=project.projectRecordID)" &
_
" ON group.recordID=estimateLabor.group" & _
" WHERE (((estimateLabor.recordType) = Forms!home!
project!recordType)" & _
" And((estimateLabor.projectRecordID)=[Forms]!
[home]![project]![projectNumber])" & _
" And ((estimateLabor.changeOrderNumber) = Forms!
home!project!corNumber)" & _
" And ((estimateLabor.invoiceNumber) = Forms!home!
project!invoiceID)" & YorN & ";"
rst1.close
'The estimateLabor form refered to in the following
statement does NOT requery properly.
'When I leave the 'group' form after all of the above
code executes, and view the following form, the viewable
recordest is the same as before the code executed.
'If I open the properties window, click on the '...'
button to view the record source, then close it, and then
click on the form, the recordset immediately updates.
Forms!home!project!estimate!estimateLabor.Requery
'---End Code---
-----Original Message-----
Perhaps you could post the code which re-creates the
recordset.
- Turtle
message
i have definately tried that.
i've also tried:
Forms!FormB.Refresh
Me.requery
out of desparation, i've also tried .repaint
i've tried requerying form A from form B. i've tried
requerying Form A with an On Click event. On Activate.
everything after the initial Forms!FormB.Requery was
done
out of desparation, as I did not really expect certain
things to make a difference.
got any other ideas?
thanks.
Kevin
-----Original Message-----
You haven't said what is in front of the dot in all
these things you've
tried.
I'd suggest Forms!FormB.Requery
HTH
- Turtle
in message
i'm having a similar problem as well. i'm using a
form
(form A) to select certain groups of records in
another
form (form B) based on a grouping mechanism i have
created. sorry if that doesn't make sense right
now.
once the 'criteria' has been set in form A, it
creates
an
SQL statement and sets it to the recordsource of
form
B.
i have tried using every combination
of .requery, .refresh, and .update i can think of to
force form B to reflect the new recordset... to no
avail.
the only way form B will reflect the new records is
if
i
either close it and reopen it or if i simply view
the
SQL
statement in design mode, exit out of it, and the
click
the form.
somebody... please tell me what's going on... i'm
at my
wits end with this. thanks.
-----Original Message-----
Perhaps Me.Refresh after you save the data?
- Turtle
"Lloyd" <
[email protected]>
wrote in
message
I tried docmd.saverecord and got an error message
that
it
was not supported for this object (using
Access97). I
then tried the SaveRecord under docmd.MenuItem.
I
am
confident that I am getting the data saved, it
simply
isn't displaying on my form. I can simply
minimize
and
restore the form and the data is displayed.
Any additional thoughts?
Thanks, Lloyd
-----Original Message-----
Ah! The oh-so-common mistake of thinking that
DoCmd.Save
will save data!
It actually saves design changes.
Try DoCmd.SaveRecord
"Lloyd" <
[email protected]>
wrote in
message
[email protected]...
I have created a command button and written a
series of
If
ISNull statements to update address data from
one
table
to
another. (The information is often, but not
always
the
same.) A sample is shown below.
When I click the button, the data seems to be
updated,
but
does not show up on the current form until I
close
out
of
the record and reopen it. Can someone suggest
the
function I need to use to get the form to
display
the
new
data when the command button has been used?
Thanks, Lloyd
If IsNull(Forms![frmGrantSumDE].
[tblGrantSum.City])
Then
[tblGrantSum.City] = [Forms]!
[frmGrantSumDE].
[tblGrantee.City]
End If
If IsNull(Forms![frmGrantSumDE].
[tblGrantSum.Zip])
Then
[tblGrantSum.Zip] = [Forms]!
[frmGrantSumDE].
[tblGrantee.Zip]
End If
DoCmd.Save
.
.
.
.
