same code for 48 textbox (1 Viewer)

basilyos

Registered User.
Local time
Today, 07:59
Joined
Jan 13, 2014
Messages
246
hello
i have 48 textbox in same form to receive invoices from our collector

i have a code to dlookup the invoice if paid or not


Code:
    Dim InvN As String
    
    If DLookup("ID", "tbl_Clients_Charging", "[ID] = " & Me.txt_01 & " And [AccountPaidStatus] = '" & "Paid" & " '") > 0 Then
        Me.txt_01 = ""
        Me.txt_01_Name = ""
    ElseIf DLookup("ID", "tbl_Clients_Charging", "[ID] = " & Me.txt_01 & " And [AccountPaidStatus] = '" & "Not Paid" & " '") > 0 Then
        InvN = DLookup("ClientName", "tbl_Clients_Charging", "[ID] = " & Me.txt_01 & " And [AccountPaidStatus] = '" & "Not Paid" & " '")
        Me.txt_01_Name = InvN
    Else
        Me.txt_01 = ""
        Me.txt_01_Name = ""
    End If


instead of putting this code 48 times, can anyone help to reduce the codeand work for the 48 textbox

thanks in advance
 

Ranman256

Well-known member
Local time
Today, 10:59
Joined
Apr 9, 2015
Messages
4,003
Put this code into a sub, send the text box as a param.
Code:
Sub MySub(txtBox)
....your code here
end sub
[\code]

then call MySub 48 times
 

basilyos

Registered User.
Local time
Today, 07:59
Joined
Jan 13, 2014
Messages
246
Put this code into a sub, send the text box as a param.
Code:
Sub MySub(txtBox)
....your code here
end sub
[\code]

then call MySub 48 times
thank you sir but do you a link so i can understand more about the parameters and textboxes
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:59
Joined
May 21, 2018
Messages
5,908
Probably something like this. However, I would kind of question the whole design and wonder if this can be done in a simple query. Are these all unbound?

Select the 48 controls all at once. Set the tag property to "INV". Do not type the parentheses.
In a load event

Code:
dim ctl as access.control
for each ctl in me.controls
  if ctl.tag = "INV" then
    ctl.value = getInvoice(Ctl.value)
  end if
next ctl

Code:
Public Function GetInvoice(ID as Long) as String
   If DCount("*", "tbl_Clients_Charging", "[ID] = " & ID & " And [AccountPaidStatus] = ' Not Paid '") > 0 Then
        GetInvoice = DLookup("ClientName", "tbl_Clients_Charging", "[ID] = " & ID & " And [AccountPaidStatus] = 'Not Paid')
   end if
end function
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:59
Joined
May 21, 2018
Messages
5,908
The question I have based on what you posted is that
Txt_01 starts with an ID in it, but ends up with Client name in it. Does not make sense to me.
 

basilyos

Registered User.
Local time
Today, 07:59
Joined
Jan 13, 2014
Messages
246
The question I have based on what you posted is that
Txt_01 starts with an ID in it, but ends up with Client name in it. Does not make sense to me.
the textboxes in unbound
the txt_01 ..... txt_48 just contain the ID
the name is in another textbox name txt_01_name ... txt_48_name

how to declare getinvoice in your solution??
i put this code in new module
Code:
Public Function GetInvoice(ID as Long) as String
   If DCount("*", "tbl_Clients_Charging", "[ID] = " & ID & " And [AccountPaidStatus] = ' Not Paid '") > 0 Then
        GetInvoice = DLookup("ClientName", "tbl_Clients_Charging", "[ID] = " & ID & " And [AccountPaidStatus] = 'Not Paid')
   end if
end function
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:59
Joined
May 21, 2018
Messages
5,908
I see now the ID control and the name controls.

Only tag the controls that hold the ID not the ones holding the Name.
For the ones that are fully paid do you really want to clear out the ID?
If so maybe more like
Code:
Public Sub UpdateControls
dim ctl as access.control
dim Inv as string

for each ctl in me.controls
  if ctl.tag = "INV" then ' tag only the ID controls
    inv = getInvoice(ctl.value)
    me.controls(ctl.name & "_name" ) = inv
    if inv = "" then ctl.value = inv
  end if
next ctl

Then on the forms Onload event call
UpdateControls
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:59
Joined
May 21, 2018
Messages
5,908
Can you post an example db? It may make more sense to do this different. Having 48 unbound controls does not sound like a good design especially since they hold IDs. I am sure there is a smarter design.
 

basilyos

Registered User.
Local time
Today, 07:59
Joined
Jan 13, 2014
Messages
246
here is a copy of my database
 

Attachments

  • MiniDatabase.accdb
    2.1 MB · Views: 83

basilyos

Registered User.
Local time
Today, 07:59
Joined
Jan 13, 2014
Messages
246
Can you post an example db? It may make more sense to do this different. Having 48 unbound controls does not sound like a good design especially since they hold IDs. I am sure there is a smarter
i uploaded a sample my database
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:59
Joined
May 21, 2018
Messages
5,908
I replaced all your code with this. You are definitely brute forcing it.


Code:
Private Sub Form_Load()
  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
    If ctrl.Tag = "ID" Then
      ctrl.OnDblClick = "=EditInvoice()"
      ctrl.AfterUpdate = "=UpdateInvN()"
    End If
    If ctrl.Tag = "Name" Then ctrl.Locked = True
  Next ctrl
End Sub
Public Function EditInvoice()
  Dim ctrl As Access.Control
  Set ctrl = Me.ActiveControl
  ctrl.Value = ctrl.Text
  If Not (ctrl.Value & "") = "" Then
    DoCmd.OpenForm "frm_Invoices_edit", , , "ID = " & ctrl.Value
  Else
    MsgBox "No ID entered"
  End If
End Function
Public Function UpdateInvN()
  Dim ctrl As Access.Control
  Dim targetCtrl As Access.TextBox
  Dim InvN As String
  Set ctrl = Me.ActiveControl
  Set targetCtrl = Me.Controls(Me.ActiveControl.Name & "_Name")
    
   If DCount("*", "tbl_Clients_Charging", "[ID] = " & ctrl.Value & " And AccountPaidStatus = 'Not Paid'") > 0 Then
        InvN = DLookup("ClientName", "tbl_Clients_Charging", "[ID] = " & ctrl.Value & " And [AccountPaidStatus] = 'Not Paid'")
        targetCtrl.Value = InvN
    Else
      targetCtrl = Null
      ctrl = Null
    End If
End Function
 

Attachments

  • MiniDatabase.accdb
    2.6 MB · Views: 69

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:59
Joined
May 21, 2018
Messages
5,908
Read this to save yourself hours and hours of work
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:59
Joined
Feb 19, 2002
Messages
34,437
I don't understand why you would use an unbound form to do this. You can use a bound form with no code at all except for validation which should be done regardless of the process. You've got nearly a thousand lines of code when less than 50 would do the job????
 

Users who are viewing this thread

Top Bottom