By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Requery a form on recordchange?

P: n/a
Hi,

I currently have a form, tblRetailOrders, with subform, tblRetailOrderLine.

In tblRetailOrders, I have a Yes/No for attrib "Complete".

On the tblRetailOrders form, I had a text label, which says "complete" or
"incomplete" depending on the status of an order.

When I first open the form, the code/sql executes, and sets the correct
value of the text label. However, I need to be able to re-run that code, and
change the value of the text label, depending on the 'status' of the new
record.

Is this possible?

Regards
Craig Mason

Current code:
-----------------------------------------------
Private Sub Form_Load()

Dim MyDB As Database
Dim VarRS As Recordset
Dim strSQL As String

Set MyDB = CurrentDb
strSQL = "SELECT * FROM Variables;"
Set VarRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
VarRS.MoveFirst
shippingrate.Value = VarRS("Shipping")
vatrate.Value = VarRS("VAT")
VarRS.Close
Set VarRS = Nothing
Set MyDB = Nothing

'################################################# ###########
Dim cDB As Database
Dim MyRecordset As Recordset
Dim MyQuery As QueryDef

strSQL = "PARAMETERS OrderID Long; SELECT tblretailorders.Pending,
tblretailorders.OrderID, tblretailorders.Complete FROM tblretailorders WHERE
(((tblretailorders.OrderID)=[OrderID]));"

Set cDB = CurrentDb
Call Tidy("Query2")
'^^ delete a previously created query

Set MyQuery = cDB.CreateQueryDef("Query2", strSQL)
MyQuery.Parameters("OrderID") = Me!orderid

Set MyRecordset = MyQuery.OpenRecordset

With MyRecordset
If !Complete = True Then
Me!thestatus.Caption = "Completed"
Else
Me!thestatus.Caption = "Incomplete"
End If
End With

End Sub

-----------------------------------------------
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Craig M wrote:
Hi,

I currently have a form, tblRetailOrders, with subform, tblRetailOrderLine.

In tblRetailOrders, I have a Yes/No for attrib "Complete".

On the tblRetailOrders form, I had a text label, which says "complete" or
"incomplete" depending on the status of an order.

When I first open the form, the code/sql executes, and sets the correct
value of the text label. However, I need to be able to re-run that code, and
change the value of the text label, depending on the 'status' of the new
record.

Is this possible?

Regards
Craig Mason

Current code:
-----------------------------------------------
Private Sub Form_Load()

Dim MyDB As Database
Dim VarRS As Recordset
Dim strSQL As String

Set MyDB = CurrentDb
strSQL = "SELECT * FROM Variables;"
Set VarRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
VarRS.MoveFirst
shippingrate.Value = VarRS("Shipping")
vatrate.Value = VarRS("VAT")
VarRS.Close
Set VarRS = Nothing
Set MyDB = Nothing

'################################################# ###########
Dim cDB As Database
Dim MyRecordset As Recordset
Dim MyQuery As QueryDef

strSQL = "PARAMETERS OrderID Long; SELECT tblretailorders.Pending,
tblretailorders.OrderID, tblretailorders.Complete FROM tblretailorders WHERE
(((tblretailorders.OrderID)=[OrderID]));"

Set cDB = CurrentDb
Call Tidy("Query2")
'^^ delete a previously created query

Set MyQuery = cDB.CreateQueryDef("Query2", strSQL)
MyQuery.Parameters("OrderID") = Me!orderid

Set MyRecordset = MyQuery.OpenRecordset

With MyRecordset
If !Complete = True Then
Me!thestatus.Caption = "Completed"
Else
Me!thestatus.Caption = "Incomplete"
End If
End With

End Sub

-----------------------------------------------

Put your code in the OnCurrent event instead on OnLoad.

Or....you could make this a subroutine...maybe call it SetComplete, and
call it where you want. For example, you may set a value in your form
or subform where you want to call the routine again.

Nov 12 '05 #2

P: n/a

"Salad" <oi*@vinegar.com> wrote in message
news:fC*****************@newsread2.news.pas.earthl ink.net...
Craig M wrote:
Hi,

I currently have a form, tblRetailOrders, with subform, tblRetailOrderLine.
In tblRetailOrders, I have a Yes/No for attrib "Complete".

On the tblRetailOrders form, I had a text label, which says "complete" or "incomplete" depending on the status of an order.

When I first open the form, the code/sql executes, and sets the correct
value of the text label. However, I need to be able to re-run that code, and change the value of the text label, depending on the 'status' of the new
record.

Is this possible?

Regards
Craig Mason

Current code:
-----------------------------------------------
Private Sub Form_Load()

Dim MyDB As Database
Dim VarRS As Recordset
Dim strSQL As String

Set MyDB = CurrentDb
strSQL = "SELECT * FROM Variables;"
Set VarRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
VarRS.MoveFirst
shippingrate.Value = VarRS("Shipping")
vatrate.Value = VarRS("VAT")
VarRS.Close
Set VarRS = Nothing
Set MyDB = Nothing

'################################################# ###########
Dim cDB As Database
Dim MyRecordset As Recordset
Dim MyQuery As QueryDef

strSQL = "PARAMETERS OrderID Long; SELECT tblretailorders.Pending,
tblretailorders.OrderID, tblretailorders.Complete FROM tblretailorders WHERE (((tblretailorders.OrderID)=[OrderID]));"

Set cDB = CurrentDb
Call Tidy("Query2")
'^^ delete a previously created query

Set MyQuery = cDB.CreateQueryDef("Query2", strSQL)
MyQuery.Parameters("OrderID") = Me!orderid

Set MyRecordset = MyQuery.OpenRecordset

With MyRecordset
If !Complete = True Then
Me!thestatus.Caption = "Completed"
Else
Me!thestatus.Caption = "Incomplete"
End If
End With

End Sub

-----------------------------------------------

Put your code in the OnCurrent event instead on OnLoad.

Or....you could make this a subroutine...maybe call it SetComplete, and
call it where you want. For example, you may set a value in your form
or subform where you want to call the routine again.


Thanks bud, didn't realise what On Current did :)

Craig
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.