Real time Query

[BartK]

I have a question and I don't know if it is even possible in a query or not.

I have a form and on the form I have a spot for an Employee Number, I will have a table with nothing but Last,First and then Employee Number.

My question is, is there a way that whenever I type their number in on the form that it will automatically narrow down until I type in their last number and then have it populate that information from the table into the form?

There are a maximum of 999 employees and so if I type in a number it will narrow it down to that hundreds range, the next number would then narrow it down to the tens range then finally the last number would pinpoint the employee. I might be barking up the wrong tree here, but many thanks.

 

[jdraw]

You want the OnChange event of the textbox.

 

[Galaxiom]

Use the OnChange Event to apply a Filter to the form.

Me.Filter = "[Employee Number] Like """ & Me.searchbox.Text & "*"""
Me.FilterOn = True

 

[BartK]

I tried putting that in the on change and it is calling an event procedure. It gives me this: Compile error and it highlights .searchbox

Should searchbox be what the box is named??? I don't do anything with VBA, Thanks for the patience. In addition to this, it is attempting to add new records within my table, is this normal???

 

[Galaxiom]

Substitute searchbox with the name of the textbox on your form.

There is nothing in the code that would be adding new records to the table.

 

[BartK]

Here is what I have in the code now, it is not giving me any errors, however it is not matching up the employee with the numbers that I am inputting in. Any thoughts?


Private Sub Text6_Change()
Me.Filter = "[Employee Number] Like """ & [Employee Number] & "*"""
Me.FilterOn = True
End Sub

 

[Galaxiom]

The Text property is only available when the control has focus. However it should still have focus if the OnChange Event is being used.

Does the filter actually apply after the first character is entered?

A filter with Like can work against a numeric field.

 

[BartK]

I'll attach my Database and let you all look at it. The form where I am trying to get the bugs worked out is the update form. And for some reason whatever number I type in, lets say I want to look up someones employee number 265, If I type in 2 first then it comes up with some wacky stuff. Also everytime I try it it adds a new record. I think it needs a priest.

 

[Galaxiom]

Also everytime I try it it adds a new record.

The search textbox needs to be unbound. Delete its ControlSource.

 

[BartK]

Did that, it seems to be working ok. However whenever I try to type in that employees full number it only limits me to 1 number that I can type in. Any thoughts? Thanks once again.

 

[David R]

Can you be more specific? Maybe a screenshot? I don't understand what 'limits me to 1 number' means here...

 

[Galaxiom]

Add this to the end of the procedure:

With Me.Text6
     TextLength = Len(.Text)
        
     If TextLength <> 0 Then
         .SelStart = TextLength
     End If
End With

 

[BartK]

Added that code and it does allow me to type in the 3 digit employee number, however it doesn't match employee number in the table. EX. I will type in the number 5 and it spits out employee number 201. It does pull the information along with that record out correctly, just the wrong record. I will attach it again. I can't thank you enough for all your help regarding this.