Clear Text from bound textboxes? 1

[mdaniele]

I need to automatically clear?/reset? text fields whenever I either select one of my combo boxes. I don't want to delete any the values in the text fields.
Row 1 of my data table is a blank row, can someone tell me the correct syntax to use in the "ON Focus" event of my combo box that will just go to row 1 ?
Please advise if there's a simpler or smarter method to do this.

 

[Eupher]

I'm afraid I don't understand what you are trying to do. You want to clear a textbox field, but not delete the value in it? How/why is the first row of your table a blank row? When the combo box gains focus, what is it that you want to go to row 1? Please post back with more details of what you're trying to accomplish.

Ken S.

 

[mdaniele]

Thanks for the help, since 1st posting, re-created the form, here's what's going on: It's a pretty simple form,
_______________________________________________________
|
| [NPN btn] [NPN combobox] [OPN textbox]
|
| [OPN btn] [OPN combobox] [NPN textbox]
|
| [ PROJINFO(textbox) ]
|
| [CLIENTINFO(textbox)] [MANAGERINFO(textbox)]
|_______________________________________________________

Our company recently changed accounting systems,the idea is to for the users to be able to cross reference a new project number to an old one by clicking on the NewProjNum button, enter in or choose from the NPN combo, and voila! the other data appears. Conversely, it is sometimes necessary to select the OldProjNum in order to retrieve the NewProjNum. Either search method produces the same PROJINFO, CLIENTINFO, and MANAGERINFO data because all 5 fields reside in the same record.
The only things I haven't been able to resolve are:
once I use either button, the text boxes retain the prior info.

i.e. let's say NPN = 123456 OPN = ABCDEF

I select NPN and enter into the NPNcombo 123456, the OPNtextbox displays ABCDEF (that part's perfect), but (because I use the same field) the OPN textbox also displays ABCDEF.
If I then use the OPN btn and enter GHIJKL, 123456 remains in NPN Combo until I execute another search.
Although it's working, the form data becomes confusing.

So, all I need to do is automatically clear all the fields whenever I either select either one of the buttons or combo boxes.

The reason for the blank record is that I thought I could have the boxes BLANK by having the on focus event of both comboboxes just go to the first record, thereby clearing all the boxes.

I think the bookmark function in the after update event of the combo's is whats creating my problem.

Ideally, and if not pressing my luck even further, I'd like to make it even simpler by doing away with both the OPNtextbox and NPNtextbox, and have just the combo boxes show the NPN and OPN.

 

[Eupher]

Okay, I think I'm getting a better idea of what's going on, but need a little more info. Are the combo boxes bound or unbound?

Ken S.

 

[Eupher]

OK, never mind my last. Here's how I would handle your problem.

1) Delete the NPN and OPN buttons, and NPN and OPN combo boxes.

2) Put an option group on your form. Let the option group wizard step you through it.
a) at the first wizard screen, create names for the 2 options, something like "Search by NPN" and "Search by OPN"
b) on the next screen, select "Search by NPN" as the default choice
c) leave the values as they are on the next screen
d) on the next screen, select "Save the value for later use"
e) take your pick on the next screen, however, I used toggle buttons in my test
f) on the next screen, name the frame whatever you want or just accept the default, then you're finished

3) Put a new combo box on your form. Let the combo wizard step you through it
a) at the first wizard screen, select "Find a record on my form based on the value I selected in my combo box"
b) on the next screen, select your data source (should be the same one as your form)
c) on the next screen select "NPN" (or the field name that corresponds to NPN)
d) on the next screen, if your table has a primary key such as an autonumber field, leave the "Hide key column" box checked, otherwise uncheck it
e) on the next screen enter a name for your combo box, then you're finished

4) Select the option group frame that you created earlier. Create an event procedure in the After Update event and insert code that looks something like this:

Select Case Me!grpMyOptionGroup
    Case 1
        Me!cmbSearchCombo.RowSource = "SELECT RecID, NPN FROM tblMyProjects ORDER BY NPN"
    Case 2
        Me!cmbSearchCombo.RowSource = "SELECT RecID, OPN From tblMyProjects ORDER BY OPN"
End Select
Me!cmbSearchCombo.Requery
Me!cmbSearchCombo = Null

... where "grpMyOptionGroup" is the name of your option group control, "cmbSearchCombo" is the name of your combo box, "RecID" is the name of the primary key field (autonumber?) in your table (if it exists); "NPN" is the name of your new project number field, and "OPN" is the name of your old project number field.

This way you can search your table either by NPN or OPN, and since both NPN and OPN text boxes are still on the form, cross-referencing is easy. If you don't want users to edit the OPN, I'd set its Locked property to "yes".

Post back if you have questions.

HTH...

Ken S.

 

[mdaniele]

Thanks for the input,
I have:
1 option group, 2 selections
1 combobox
1 textbox (npn)
1 textbox (opn)
6 textboxes (various project details)

All Existing Code is:
*******************************************
Option Compare Database

'Automatically generated by the Combo wizard

Private Sub cmbSearchCombo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[RecID] = " & Str(Me![cmbSearchCombo])
Me.Bookmark = rs.Bookmark
End Sub



Private Sub grpMyOptionGroup_AfterUpdate()
Select Case Me!grpMyOptionGroup
Case 1
Me!cmbSearchCombo.RowSource = "SELECT RecID, NPN FROM tblMyProjects ORDER BY NPN"
Case 2
Me!cmbSearchCombo.RowSource = "SELECT RecID, OPN From tblMyProjects ORDER BY OPN"
End Select
Me!cmbSearchCombo.Requery
Me!cmbSearchCombo = Null
End Sub
************************************

here's what occurs:

The initial selection using the NPN option produces an accurate result. Subsequent selections also produce an accurate result as long as I don't try to use the other option.
Once I select the other option, the combo box will not retreive any data from OPN, but the former data in the NPN & OPN textboxes remains visible.
Then when I select back to the NPN option, the combo box will not retrieve data anymore, (the former data remains visible in the texboxes.
Case 2 never occur. Is this because of the bookmark code related to the combobox?

 

[Eupher]

My first guess (just a guess!) is that the field names in the code don't exactly match the field names in your table. Can you tell me the name of your table, all the field names in the table, and the field names on your form?

Also, what is the data type of the RecID field?

Ken S.

 

[mdaniele]

May thanks once again,
After you first posted code, in frustration, I changed names and created a new form bassed on the code you provided.
Since then I've looked at it over and over and can't see a discrepency. Here's what I've got:

Form ("ProjDataRef" has:
Option group "grpMyOptionGroup"
Combo box "cmbSearchCombo"
Text Boxes:
NPN, OPN, OFFICE, OFFICEOU, JOBDATE, JOBSTATUS, CUSTOMERNAME, PROJDESCR.

Table "MyProjects" has fields:
RECID(NUMBER)(PRIMARY KEY), NPN, OPN, OFFICE, OFFICEOU, JOBDATE, JOBSTATUS, CUSTOMERNAME, PROJDESCR.

 

[Eupher]

Is the name of your table "tlbMyProjects" or "MyProjects"?

Ken S.

 

[mdaniele]

I should have remembered that seasoned programmers preface their items with a descriptor like tbl or qry, I mistook it as part of the code and not as part of the item name.

So, ok...oops...minor glitch, sometimes you just need those extra pair of eyes...it is now called tblMyProjects, which has resolved 1/2 of the remaining problem.
Now when I try to search by OPN I get a blank list in the combo selection, but none of the OPN's appear, but, I can at least go back to the NPN option and search by NPN which was not working earlier.

The only differences I can see in the option buttons is their names and values: Option11 (value 1) Option13 (value 2) and of course their TOP (position)

 

[mdaniele]

WHOA, IT WORKS, IT WORKS!

After renaming the table, it actually does work using the OPN option. The reason that I didn't think so was that there are so many null records in that column compared to the NPN's(appx 3500 blank entries before the OPNs begin), that I didn't realize the OPN's were actually way down that far.

Is there a method to query the list in such a way that only the records that are not not blank in that field appear in the combo selection list?

 

[Eupher]

Sure...

Me!cmbSearchCombo.RowSource = "SELECT RecID, OPN From tblMyProjects WHERE OPN Is Not Null ORDER BY OPN"

Ken S.

 

[mdaniele]

Thanks for all the help, I've given you a star for each post, well deserved!

I'd like to make this a small executable program, but the database will be changing so I'm going to now dabble with making this a webpage app.

Once again, thanks

 

[Eupher]

You're welcome, glad to be of assistance!

Ken S.