How to populate Access 2010 text boxes on a Form using VBA code

Question

I have an Access 2010 Form that has three Combo boxes for Year, Policy Number, and Type_Code. Next to those I have a policy find button, below all of these I have text boxes that will hold policy data. When a user select the year, policy number and type code from the combo boxes, they then click on the policy find button. The policy find button then runs the below code that find the policy data for the policy that was selected in the combo boxes.

Private Sub PolSearch_Click()
On Error GoTo Err_PolSearch_Click

Dim SQL As String

Dim BD, PRC, PP, I, RFD, TAD, CPYMT, LC, PN, TYP, RY As String

PN = Me!cboPN.Value

TYP = Me!cboTYP.Value

RY = Me!cboRY.Value


Dim db As DAO.Database

Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT DISTINCT GA_Loss_Credit.Billing_Date AS BD, 

GA_Loss_Credit.Practice_Code as PRC, GA_Loss_Credit.producer_premium as PP,

GA_Loss_Credit.Interest as I, " & _

" GA_Loss_Credit.Refund_Amount as RFD, GA_Loss_Credit.Balance_Due as TD, 

GA_Loss_Credit.Payment_Amount AS CPYMT, " & _

" GA_Loss_Credit.Loss_Credit as LC " & _

" FROM [GA_Loss_Credit] " & _

(" WHERE (([GA_Loss_Credit].REINSURANCE_YEAR) ='" & [RY] & "')

AND (([GA_Loss_Credit].POLICY_NUMBER) ='" & [PN] & "') 

AND (([GA_Loss_Credit].TYPE_CODE)='" & [TYP] & "')") & _

(" ORDER BY GA_Loss_Credit.Billing_Date ;"))



  While Not rs.EOF

  BDATE.SetFocus
  If IsNull(BDATE.Value) Then
  BDATE.Value = rs![BD]
  ElseIf IsNull(BDate_2.Value) Then
  BDate_2.Value = rs![BD]
  ElseIf IsNull(BDate_3.Value) Then
  BDate_3.Value = rs![BD]
  ElseIf IsNull(BDate_4.Value) Then
  BDate_4.Value = rs![BD]
  ElseIf IsNull(BDate_5.Value) Then
  BDate_5.Value = rs![BD]
  ElseIf IsNull(BDate_6.Value) Then
  BDate_6.Value = rs![BD]
  ElseIf IsNull(BDate_7.Value) Then
  BDate_7.Value = rs![BD]
  ElseIf IsNull(BDate_8.Value) Then
  BDate_8.Value = rs![BD]
  ElseIf IsNull(BDate_9.Value) Then
  BDate_9.Value = rs![BD]
  ElseIf IsNull(BDate_10.Value) Then
  BDate_10.Value = rs![BD]
  End If

  PCode_1.SetFocus
  If IsNull(PCode_1.Value) Then
  PCode_1.Value = rs![PRC]
  ElseIf IsNull(PCode_2.Value) Then
  PCode_2.Value = rs![PRC]
  ElseIf IsNull(PCode_3.Value) Then
  PCode_3.Value = rs![PRC]
  ElseIf IsNull(PCode_4.Value) Then
  PCode_4.Value = rs![PRC]
  ElseIf IsNull(PCode_5.Value) Then
  PCode_5.Value = rs![PRC]
  ElseIf IsNull(PCode_6.Value) Then
  PCode_6.Value = rs![PRC]
  ElseIf IsNull(PCode_7.Value) Then
  PCode_7.Value = rs![PRC]
  ElseIf IsNull(PCode_8.Value) Then
  PCode_8.Value = rs![PRC]
  ElseIf IsNull(PCode_9.Value) Then
  PCode_9.Value = rs![PRC]
  ElseIf IsNull(PCode_10.Value) Then
  PCode_10.Value = rs![PRC]
  End If



  rs.MoveNext
  Wend
  rs.Close


Exit_PolSearch_Click:
    Exit Sub

Err_PolSearch_Click:
    MsgBox Err.Description
    Resume Exit_PolSearch_Click
End Sub

The query that is being run can bring back up to 10 records for Billing Dates and Practice Types.

The above codes works but my question is; is there a better way of doing this instead of having to write ten different IF Then Else statement.

Any help/suggestion would be greatly appreciated

OpiesDad OpiesDad · Accepted Answer · 2015-08-14T16:04:36

There is a better way to do this. What you want to do is use a counter and then call the appropriate control.

First, rename the first box to "BDATE_1" instead of just "BDATE"

Dim controlCounter As Integer
.... other code

controlCounter = 1
While (Not rs.EOF) And controlCounter <= 10 
    Me.Controls("BDATE_" & controlCounter).Value = rs![BD]
    Me.Controls("PCode_" & controlCounter).Value = rs![PRC]
    controlCounter = controlCounter + 1
    rs.MoveNext
Wend

....rest of code

Note that I put in an additional check to make sure the counter doesn't get too big to exceed the comboboxes that you have.

This code will at least populate your boxes. There may be a better way to design the whole form overall such as by using a listbox instead of a bunch of textboxes to hold the info and setting the listbox.recordsource property to the query you used for the recordset, but I don't entirely understand, and didn't take time to figure out, what exactly the point of your form is.

How to populate Access 2010 text boxes on a Form using VBA code

2 Answers