×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Bizarre Behavior in Simple Combo Box

Bizarre Behavior in Simple Combo Box

Bizarre Behavior in Simple Combo Box

(OP)
I wonder if anyone has any idea what could be causing this. Basically all the code below is designed to do is to populate three controls (text boxes) assigned to fields when the user selects a name displayed by the combobox (cboConsumer) which it does BUT---

If the user begins typing a name and it appears anywhere in the combobox display list by clicking the mouse on it the controls are populated and the user is returned to the form to continue filling it out. However, if the user begins typing enough of a name so that the correct name is highlighted and the selection process is completed by pressing the ENTER key instead, the controls get populated as before BUT the user is returned to a new record instead and so they have to navigate back to the previous record to finish completing it. (The original version of this code which behaved the same was even simpler in that the textboxes were populated directly. The variables were only added to aid in debugging the issue.

A couple of related points of interest are that when a break point was set on the "Exit Sub" line and the form checked before executing that line the relevant controls were populated correctly and the form was still displaying the correct record. Also if the three lines of code that assign values to the textboxes were commented out the code returned the form to the current record regardless whether Click or Enter was used to select from the combobox.

I can't imagine how the means of selecting an entry from the combobox (mouse click or Enter key) would trigger a new record being created (or not) but somehow it is.

**************************************
Private Sub cboConsumer_Click()

On Error GoTo cboConsumer_Click_Error

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngCId As Long
Dim strPlna As String
Dim strApt As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Qry_LU_Consumer_NewEntry", dbOpenDynaset)

If Not (rst.EOF And rst.BOF) Then 'rst is not empty

rst.FindFirst "[CId] =" & Me.cboConsumer
lngCId = rst("CId").Value
strPlna = rst("plna").Value
strApt = rst("Apt").Value[/indent]

If rst.NoMatch Then
'Nothing to do
End If
Else
'no records = no match = should never happen
End If

rst.Close
Set rst = Nothing
Set dbs = Nothing

Me.txtCId.Value = lngCId
Me.txtplna.Value = strPlna
Me.txtApt = strApt

Me.cboConsumer.Value = Null

On Error GoTo 0
Exit Sub

cboConsumer_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboConsumer_Click of VBA Document Form_Master_Consumer_New_Entry"
End Sub

RE: Bizarre Behavior in Simple Combo Box

I am curious why you wouldn't include CID, plna, and Apt in the columns of the combo box and then have code in the after update even of the combo box like:

CODE --> vba

Me.txtCId = Me.cboConsumer.Column(x)
Me.txtplna = Me.cboConsumer.Column(y)
Me.txtApt =  Me.cboConsumer.Column(z) 

Where x, y, and z are the column numbers of from the combo box. These begin numbering with 0.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Bizarre Behavior in Simple Combo Box

(OP)
Good question. That would certainly make the code more concise. Thanks for the suggestion. I have no idea what I was thinking when when I did it like I did. It was at least a long time ago and no one complained until now, Lol. I'll definitely give it a try when I get back to this next week but based on what I've seen so far I'm not sure it will make any difference regarding the Click=Current Record, Enter=New Record phenomena. I am pretty fixated on trying to understand how two selection methodologies each of which bring exactly the same data into the procedure result in different records appearing when the procedure exits. I thought it might be the result of some kind of weird corruption but I've decompiled, compacted and repaired and imported everything into a fresh database to no effect.

RE: Bizarre Behavior in Simple Combo Box

I would also check the tab order and cycle property of the forms.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Bizarre Behavior in Simple Combo Box

(OP)
Thanks. I had looked at the tab order previously but I'll be sure to checkout the .Cycle property as well when I make your other suggested changes.

RE: Bizarre Behavior in Simple Combo Box

(OP)
I implemented both of dhookom's suggestion. First I simplified the code behind the cboConsumer_Click event to...

CODE --> vba

Private Sub cboConsumer_Click()

   On Error GoTo cboConsumer_Click_Error

   Me.txtCId =  Me.cboConsumer.Column(3)
   Me.txtplna = Me.cboConsumer.Column(4)
   Me.txtApt  = Me.cboConsumer.Column(6)

   Me.cboConsumer.Value = Null
   
   On Error GoTo 0
   Exit Sub

 cboConsumer_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboConsumer_Click of VBA Document Form_Master Client New Accident"
 End Sub 
(where the column numbers coincide to the columns from the combobox's row source that contain the correct values to assign to the 3 controls)

The issue was not resolved by this change, which was not unexpected but it did help in paring the code to the bare essentials. Next I moved on to look at the tab order and the .Cycle property of the form. The reason that these two suggestions are relevant is that something is triggering the creation of a new record and one of the actions that could so would be if the user were to tab past the last control on the form which would cause a fresh record to be created. The tab order was something I'd looked at previously for that reason but the .Cycle property was one I never knew existed before Duane mentioned it. After rechecking that the tab order didn't appear to be a factor I experimented with the .Cycle property. This property alters the what occurs if the user tabs past the last control on a form. (Possible .Cycle values are 0,1,2) where the results of assigning a value to the property is effects the Tab key behavior in the form as follows.

Pressing the Tab key from the last control..
All Records 0 (Default) ...on a form moves the focus to the first control in the tab order in the next record.
Current Record 1 ...on a record moves the focus to the first control in the tab order in the same record.
Current Page 2 ...on a page moves the focus back to the first control in the tab order on the page.

What fixed the new record problem was adding a line to set the .Cycle property to 1 the On Load event of the form

CODE --> vba

Me.Cycle = 1 
Once that line was added the new record was no longer being created via CLICK or ENTER. The only remaining issue for me is that although the changing the .Cycle property setting stopped the new record from being created I see no obvious connection between the .Cycle property and the actions of the user (they are not using the tab key at all they are merely selecting from a dropdown by either a mouse click or pressing the ENTER key).

That aside though, thanks Ddhookom it's good to have form working as intended.

RE: Bizarre Behavior in Simple Combo Box

If the comb box is the last control in the tab order then I expect the tab order can be an issue.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Bizarre Behavior in Simple Combo Box

(OP)
Just to wrap this one up. Dhookom hit the nail on the head. The main issue with the odd behavior was due to the combobox's position in the tab order but there was a complicating issue that I didn't realize until the very end. The database was one I constructed a long time ago when I was pretty new to Access (it actually was a rebuild of an Rbase database that goes back to the mid 80's). Since user's entered information from a long paper form the original Rbase version (Rbase being DOS based) broke the entry up into a number of sections(via forms) displaying a single screen worth of data and in doing the rebuild I decided to make use of a tab control to replicate and simplify that entry process. Unfortunately this being probably the first time I'd ever used a tab control I somehow managed to place the dropdown on the tab control instead of on the Page control it should have been on. Since a control placed like this "bleeds" through in both design and form view the issue wasn't apparent. It wasn't until I happened to look at one of the other pages and wonder why the combobox was appearing on them that I found that error.

So since I wrongly assumed that the control was located on the page control when I checked to see if any of the involved controls (the combobox or the textboxes being updated by the comboboxes On Click code) none of them appeared at the end of the tab order list. BUT the combo was the last (and only) control located on the tab control itself.

The good news is that this explains why the form behaved differently depending on whether the combo selection was made via the mouse or ENTER key. Actually the original code itself was not even a factor (although the revised version is definitely an improvement). The short story is that pressing the ENTER key executed the code but also caused the form to move beyond the last control and the default .Cycle action is to move to the next record when that occurs. Changing the .Cycle property value prevented the form from moving to a new record and so that fixed the problem but placing the combo on the "Page 1" control as it should have been in this case and making sure it was not the last control in the tab order would have enabled the original code (confirmed in testing) to work as intended as well.

Thanks again Duane for all the help. It would have bugged me endlessly not to have a full explanation for what was going on here.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close