Display table data in form text box

[Craig M]

Hi,

Im pretty stuck. This is probably simple as, but I can't do it for the life
of me.

I have a form, frmRetailOrders with subform frmRetailOrderL ine.

On the retailorders form, i simply want a text box to equal a "shipping"
rate, and another for the "VAT" rate. I am trying to pull these 2 values
from a table named "tblVariabl es", as the user needs to be able to change
them.

Any help would be appreciated.

Regards
Craig

[James Fortune]

Re: Display table data in form text box

"Craig M" <NoSp@mPlz.Th x> wrote in message news:<408afdce$ 0$20513$cc9e4d1 f@news-text.dial.pipex .com>...[color=blue]
> Hi,
>
> Im pretty stuck. This is probably simple as, but I can't do it for the life
> of me.
>
> I have a form, frmRetailOrders with subform frmRetailOrderL ine.
>
> On the retailorders form, i simply want a text box to equal a "shipping"
> rate, and another for the "VAT" rate. I am trying to pull these 2 values
> from a table named "tblVariabl es", as the user needs to be able to change
> them.
>
> Any help would be appreciated.
>
> Regards
> Craig[/color]

Note: I'm just taking a guess at what you're trying to do.
I'm assuming tblVariables has only one record and that
the same value of ShippingRate and VATRate is used for
every customer until it gets changed. If this is not the
case then you'll need to omit the save part to keep the
same default values coming up. I couldn't tell if the user
being able to change them meant changing them in
tblVariables or changing them on the form for that order.

In your sub in frmRetailOrders put...

....current Dim statements
Dim MyDB As Database
Dim VarRS As Recordset
Dim strSQL As String

Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblVariables;"
Set VarRS = MyDB.OpenRecord set(strSQL, dbOpenSnapshot)
VarRS.MoveFirst
txtShippingRate .Value = VarRS("Shipping Rate")
txtVATRate.Valu e = VarRS("VATRate" )
VarRS.Close
Set VarRS = Nothing
Set MyDB = Nothing
....

To save changed values back to tblVariables, create a new
command button called cmdSaveNewRates and in its OnClick
event paste the following code:

cmdSaveNewRates _Click()
Dim MyDB As Database
Dim VarRS As Recordset
Dim strSQL As String

Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblVariables;"
Set VarRS = MyDB.OpenRecord set(strSQL, dbOpenDynaset)
VarRS.MoveFirst
VarRS.Edit
VarRS("Shipping Rate") = txtShippingRate .Value
VarRS("VATRate" ) = txtVATRate.Valu e
VarRS.Update
VarRS.Close
Set VarRS = Nothing
Set MyDB = Nothing
MsgBox("Shippin gRate and VATRate were updated successfully.")
End Sub

Post again explaining what you need to do in more detail if this
is not what you need.

James A. Fortune

[Craig M]

Re: Display table data in form text box

"James Fortune" <jafortun@oakla nd.edu> wrote in message
news:a6ed3ce7.0 404250025.7f61b 0c6@posting.goo gle.com...[color=blue]
> "Craig M" <NoSp@mPlz.Th x> wrote in message[/color]
news:<408afdce$ 0$20513$cc9e4d1 f@news-text.dial.pipex .com>...[color=blue][color=green]
> > Hi,
> >
> > Im pretty stuck. This is probably simple as, but I can't do it for the[/color][/color]
life[color=blue][color=green]
> > of me.
> >
> > I have a form, frmRetailOrders with subform frmRetailOrderL ine.
> >
> > On the retailorders form, i simply want a text box to equal a "shipping"
> > rate, and another for the "VAT" rate. I am trying to pull these 2 values
> > from a table named "tblVariabl es", as the user needs to be able to[/color][/color]
change[color=blue][color=green]
> > them.
> >
> > Any help would be appreciated.
> >
> > Regards
> > Craig[/color]
>
> Note: I'm just taking a guess at what you're trying to do.
> I'm assuming tblVariables has only one record and that
> the same value of ShippingRate and VATRate is used for
> every customer until it gets changed. If this is not the
> case then you'll need to omit the save part to keep the
> same default values coming up. I couldn't tell if the user
> being able to change them meant changing them in
> tblVariables or changing them on the form for that order.
>
> In your sub in frmRetailOrders put...
>
> ...current Dim statements
> Dim MyDB As Database
> Dim VarRS As Recordset
> Dim strSQL As String
>
> Set MyDB = CurrentDb
> strSQL = "SELECT * FROM tblVariables;"
> Set VarRS = MyDB.OpenRecord set(strSQL, dbOpenSnapshot)
> VarRS.MoveFirst
> txtShippingRate .Value = VarRS("Shipping Rate")
> txtVATRate.Valu e = VarRS("VATRate" )
> VarRS.Close
> Set VarRS = Nothing
> Set MyDB = Nothing
> ...
>
> To save changed values back to tblVariables, create a new
> command button called cmdSaveNewRates and in its OnClick
> event paste the following code:
>
> cmdSaveNewRates _Click()
> Dim MyDB As Database
> Dim VarRS As Recordset
> Dim strSQL As String
>
> Set MyDB = CurrentDb
> strSQL = "SELECT * FROM tblVariables;"
> Set VarRS = MyDB.OpenRecord set(strSQL, dbOpenDynaset)
> VarRS.MoveFirst
> VarRS.Edit
> VarRS("Shipping Rate") = txtShippingRate .Value
> VarRS("VATRate" ) = txtVATRate.Valu e
> VarRS.Update
> VarRS.Close
> Set VarRS = Nothing
> Set MyDB = Nothing
> MsgBox("Shippin gRate and VATRate were updated successfully.")
> End Sub
>
> Post again explaining what you need to do in more detail if this
> is not what you need.
>
> James A. Fortune[/color]


That worked first time :) I had worked around it by placing another sub-form
based on tblVariables; it was a bit messy.

Thanks a ton, buddy. Great help :D

Craig