TextBox entry populates other fields 1

[kentwoodjean]

I have a form with a list box that upon selection of an item, other fields with customer address information are populated. This works great using the following:

Private Sub List18_Click()
Dim szSQL As String
Dim szContractNum As String

szContractNum = Me.List18.Value

szContractNum = LTrim(RTrim(szContractNum))

szSQL = "select * from qryTransMaticMembers where CONTRACT_NUM = """ & szContractNum & """;"

Me.Form.RecordSource = szSQL

End Sub

Due to the size of the database, my users now only want a text box they can type an entry (Contract_Num) that will do the same as the list box in populating the other fields. I worked so hard to get the list box working correctly and now I have having trouble converting this to the text box only.

I thought I could use the same format and change the List18 to Text20 and rather than Click change that event to After Update. It is a no go. Any ideas on how I can adjust the above VB to fix my text box would be appreaciated. I am in the process of teaching myself VB via a MS book and disk.

 

[Remou]

How about:

Private Sub txtContractNum_AfterUpdate()
  Dim szSQL As String

  szSQL = "select * from qryTransMaticMembers where CONTRACT_NUM = """ & Trim(Me.txtContractNum) & """;"

  Me.RecordSource = szSQL

End Sub

I have called the text box txtContractNum; it is usually best to name controls witha prefix and some name that relates to their contents. You will need some error coding, too.

 

[kentwoodjean]

Thanks for the help. I was close but not quite. Works good.

 

[kentwoodjean]

OK, so I am back. Even though my form is working good, perhaps I will need some help with the error coding to eliminate what is happening. When I open the form, the contract number field is blank but the details of the 1st of 755 records show up. If I type in a contract number and hit enter, the correct record will show up. If I change the "Data Entry" property to Yes so a blank form appears while opening, then entry of the contract number will not produce any records.

So is it the error coding, and I guess I am not clear on what that would be. Sorry I couldn't follow through.

 

[Remou]

You can add coding to the Load event for the form, say:

Private Sub Form_Load()
'Use the lowest number from the recordset
'DMax would get the highest
Me.txtContractNum = DMin("Contract_Num", "qryTransMaticMembers ")
szSQL = "select * from qryTransMaticMembers where CONTRACT_NUM = """ & Trim(Me.txtContractNum) & """;"

  Me.RecordSource = szSQL
End Sub

 

[kentwoodjean]

If I am reading this correctly then each time I open the form, I will see the data for the first record but can re-enter the contractNum for a different member and get their information? That is my end result at this time. Is there anyway that I can pull up this form with tab controls so nothing shows and it is a blank form? Again, changing the Data Entry control on the form to Yes gives me the blank form but eliminates the autopopulation upon entry of the contract num.

Thanks again.

 

[Remou]

You seem to need this form to do two things,
1. Show records already in the system
2. Allow data entry
This is not a problem, but some idea of how you see your users working is necessary. For example, you could change the After Update event:

Private Sub txtContractNum_AfterUpdate()
  Dim szSQL As String
  Me.DataEntry = False
  <...>

Which will get rid of data entry if a Contract_Num is entered. But how will you return to data entry? When the Contract_Num does not exist? When a button is clicked?

Then again, it may be easiest to use the form in two modes from a menu, which is also possible:
[tt]DoCmd.OpenForm "frmForm", acNormal, , , acFormAdd[/tt]
(If you are using a switchboard, it will prompt for whether the form is for editing or adding.) Or you could use an OpenArg, which would allow you to choose which bits of code would run:

Private Sub Form_Load()
If Me.OpenArgs = "Edit" Then
  Me.txtContractNum = DMin("Contract_Num", "qryTransMaticMembers ")
  szSQL = "select * from qryTransMaticMembers where CONTRACT_NUM = """ & Trim(Me.txtContractNum) & """;"

  Me.RecordSource = szSQL
Else
  Me.DataEntry = True
End If
End Sub

As you can see, there are many possibilities that depend on what you think is best. So how would you like the form to work?

 

[kentwoodjean]

Lets say that I am the user. I will open the form which will be blank, no entries in the form fields or fields on the 3 tab controls. There will also be no numbers (main form built on table of membership listing w/ 700) in the record selector area of the form.

On my blank form, I will enter the ContractNum and if it is correct, 2 other fields on my main form and those on my tab controls will auto-populate.

After reviewing the information, I will then enter some customer data based on my call on the 3rd tab control. When done, I will close this form. Upon receipt of another call, I will again have a blank form.

Hope that makes sense and is doable.

 

[Remou]

Ok. You could try this to see if it suits:
1. Create a copy of your table with one blank record. Call it say, tblBlank.
2. Set up your form with the following properties:
Record Source = tblBlank
Allow Edits = Yes
Allow Deletions = No
Allow Additions = No
3. Change your code:

Private txtContractNum_AfterUpdate()
Me.RecordSource = "Select * from qryTransMaticMembers where CONTRACT_NUM = """ & Trim(Me.txtContractNum) & """;"
If Me.RecordsetClone.RecordCount = 0 Then
    MsgBox "No Records Found"
    Me.RecordSource = "tblBlank"
End If
End Sub

 

[kentwoodjean]

Got it. thanks so much.