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

Batch updates based on form data entries

P: n/a
Hi,

I have a from called frmBatchUpdate. On it I have created 10 text
boxes in one column txtTRN1, txtTRN2 etc.. (Tenancy Reference Numbers)
and another 10 to the right txtRPA1, txtRPA2 etc..
(RegularPaymentAmount) and one on it's own txtNewRPA. The left hand
column is there to allow a user to enter a Tenancy Reference Number
and when the focus is lost, the text box to the right of it displays
the Regular Rent payment for that tenant.

I have used the following and it works OK.

Private Sub txtTRN1_LostFocus()

Dim rst As Recordset
Dim strSQL As String
Dim strWhere As String, strWhereTRN As String
Dim strRPA As String

strWhereTRN = [Forms]![frmBatchUpdate]![txtTRN1]

strWhere = " WHERE TenantRentAccountRefNo = " & "'" & strWhereTRN
& "'"

strSQL = "SELECT tblTenantPayments.RegularPaymentAmount" _
& " FROM tblTenantPayments" _
& strWhere

Set rst = CurrentDb.OpenRecordset(strSQL)
strRPA = rst.Fields(0)

txtRPA1.SetFocus
txtRPA1.Text = strRPA

rst.Close
Set rst = Nothing
End Sub

Now I obviously need to do this for each textbox for the txtTRN?s. My
first question, is there any easier way to do this in one program
rather than writing 10 individual lost focus procedures (one for each
text box).

When all the required Tenancy Reference Numbers have been entered (not
all 10 have to be complete) there is another textbox that is to be
filled in by the user. This textbox is called txtNewRPA. The user
types in a new Regular Payment Amount and when they press a command
button I want all the existing Regular Payment Amounts for the entered
Tenancy Reference Numbers to be updated to the value the user entered
into txtNewRPA.

What is the best way to have all the Tenancy Reference Numbers brought
together from 10 textboxes and have their Regular Payment Amount field
updated.

Any help would be gratefully appreciated.

Regards.

Carl J Barrett
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
ca**********@newcastle.gov.uk (Carl) wrote in message news:<ab*************************@posting.google.c om>...
Hi,

I have a from called frmBatchUpdate. On it I have created 10 text
boxes in one column txtTRN1, txtTRN2 etc.. (Tenancy Reference Numbers)
and another 10 to the right txtRPA1, txtRPA2 etc..
(RegularPaymentAmount) and one on it's own txtNewRPA. The left hand
column is there to allow a user to enter a Tenancy Reference Number
and when the focus is lost, the text box to the right of it displays
the Regular Rent payment for that tenant.

I have used the following and it works OK.

Private Sub txtTRN1_LostFocus()

Dim rst As Recordset
Dim strSQL As String
Dim strWhere As String, strWhereTRN As String
Dim strRPA As String
strWhereTRN = [Forms]![frmBatchUpdate]![txtTRN1]

strWhere = " WHERE TenantRentAccountRefNo = " & "'" & strWhereTRN
& "'"

strSQL = "SELECT tblTenantPayments.RegularPaymentAmount" _
& " FROM tblTenantPayments" _
& strWhere

Set rst = CurrentDb.OpenRecordset(strSQL)
strRPA = rst.Fields(0)

txtRPA1.SetFocus
txtRPA1.Text = strRPA

rst.Close
Set rst = Nothing
End Sub
strip this out to a separate subroutine and pass in the name of the
control.
Then loop through the controls...

for intCounter = 1 to whatever
strControlName=txtWhatever & intCounter
call MySub Me.Controls(strControlName), someotherargument
next intCounter
Now I obviously need to do this for each textbox for the txtTRN?s. My
first question, is there any easier way to do this in one program
rather than writing 10 individual lost focus procedures (one for each
text box).

When all the required Tenancy Reference Numbers have been entered (not
all 10 have to be complete) there is another textbox that is to be
filled in by the user. This textbox is called txtNewRPA. The user
types in a new Regular Payment Amount and when they press a command
button I want all the existing Regular Payment Amounts for the entered
Tenancy Reference Numbers to be updated to the value the user entered
into txtNewRPA.
Use an inputbox for force the user to give a value for this.

Dim varValue as variant
do until varValue is not null
varValue=InputBox("Hey, gimme a value...")
loop
What is the best way to have all the Tenancy Reference Numbers brought
together from 10 textboxes and have their Regular Payment Amount field
updated.

if you opened a recordsaet of values for this, you could just populate
your form with them. Sounds like seriously denormalized data,
though...
Any help would be gratefully appreciated.

Regards.

Carl J Barrett

Nov 13 '05 #2

P: n/a
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
ca**********@newcastle.gov.uk (Carl) wrote in message news:<ab*************************@posting.google.c om>...
Hi,

I have a from called frmBatchUpdate. On it I have created 10 text
boxes in one column txtTRN1, txtTRN2 etc.. (Tenancy Reference Numbers)
and another 10 to the right txtRPA1, txtRPA2 etc..
(RegularPaymentAmount) and one on it's own txtNewRPA. The left hand
column is there to allow a user to enter a Tenancy Reference Number
and when the focus is lost, the text box to the right of it displays
the Regular Rent payment for that tenant.

I have used the following and it works OK.

Private Sub txtTRN1_LostFocus()

Dim rst As Recordset
Dim strSQL As String
Dim strWhere As String, strWhereTRN As String
Dim strRPA As String


strWhereTRN = [Forms]![frmBatchUpdate]![txtTRN1]

strWhere = " WHERE TenantRentAccountRefNo = " & "'" & strWhereTRN
& "'"

strSQL = "SELECT tblTenantPayments.RegularPaymentAmount" _
& " FROM tblTenantPayments" _
& strWhere

Set rst = CurrentDb.OpenRecordset(strSQL)
strRPA = rst.Fields(0)

txtRPA1.SetFocus
txtRPA1.Text = strRPA

rst.Close
Set rst = Nothing
End Sub


strip this out to a separate subroutine and pass in the name of the
control.
Then loop through the controls...

for intCounter = 1 to whatever
strControlName=txtWhatever & intCounter
call MySub Me.Controls(strControlName), someotherargument
next intCounter
Now I obviously need to do this for each textbox for the txtTRN?s. My
first question, is there any easier way to do this in one program
rather than writing 10 individual lost focus procedures (one for each
text box).

When all the required Tenancy Reference Numbers have been entered (not
all 10 have to be complete) there is another textbox that is to be
filled in by the user. This textbox is called txtNewRPA. The user
types in a new Regular Payment Amount and when they press a command
button I want all the existing Regular Payment Amounts for the entered
Tenancy Reference Numbers to be updated to the value the user entered
into txtNewRPA.


Use an inputbox for force the user to give a value for this.

Dim varValue as variant
do until varValue is not null
varValue=InputBox("Hey, gimme a value...")
loop
What is the best way to have all the Tenancy Reference Numbers brought
together from 10 textboxes and have their Regular Payment Amount field
updated.


if you opened a recordsaet of values for this, you could just populate
your form with them. Sounds like seriously denormalized data,
though...
Any help would be gratefully appreciated.

Regards.

Carl J Barrett

Hi Pieter,

Could you elaborate more on how I would pass the name of the control
through the function.

The purpose of this form is to simply update values in the main table
and nothing else. I originally started out with update queries, and
got more adventurous with the example of code above but can't grasp
how to do what you have recommended with functions (but I would like
to know how to do this)

Should I create a new module and if so could you be so kind as to tell
me how the module should be structured and what it's contents should
be. How do a force a text box name from the form to run through a
function whenever it loses the focus.

I apologise if I'm asking too much, but this will be a great help and
will provide me with an understanding of passing information through
functions in the future.

Thanks again.

Carl J Barrett
Nov 13 '05 #3

P: n/a
code on your FORM (behind a button or some such...)

Private Sub Command6_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If Left$(ctl.Name, 4) = "Text" Then
ctl = GetRent(ctl)
End If
Next ctl
End Sub

This code could be either in your form or in a separate module...

Option Compare Database
Option Explicit

Public Function GetRent(ByVal ctl As Control) As Currency
Dim curRent As Currency

curRent = Nz(ctl.Value)
GetRent = 100 + curRent
End Function

Yes, it's a really stupid function, but it's just to show you that a
data manipulation is going on...

Okay, now to call it from a button on my form...

Private Sub Command6_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If Left$(ctl.Name, 4) = "Text" Then
ctl = GetRent(ctl) '<---updating the control with the
result of a user-defined function.
End If
Next ctl
End Sub

all the controls I want to update are named "Text0", "Text1",...
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.