Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 07-19-2013, 11:51 AM   #1
Cirrostratus
Newly Registered User
 
Join Date: May 2013
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
Cirrostratus is on a distinguished road
find data using field in form

I have a form that is bound to a query. The user inputs their id in a text box and the form should find the corresponding record with 5 different pieces of data displayed in separate text boxes. There is at any given time only one record set per id. The query has in the EmpID the criteria: [Forms]![frmProdStopEntryNew]![EmpID] to reference back the EmpID as the filter.

On the form, after the employee enters their ID, the afterupdate should pull-up the record that corresponds to that id. The code for the afterupate is:

Code:
Private Sub Emp_ID_AfterUpdate()
Me.Filter = "EmpID = '" & Me.Emp_ID & "'"
 DoCmd.RunCommand acCmdApplyFilterSort
Me.StopTime = Now()
ReasonID.SetFocus
End Sub
I also have a form load to go to a new record just so that the form is blank when the user enters it.

Code:
Private Sub Form_Load()
On Error GoTo Form_Load_Err
 
    DoCmd.SetWarnings False
    DoCmd.GoToRecord , "", acNewRec
Form_Load_Exit:
    Exit Sub
Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit
End Sub
The problem I'm experience is that the query to find the record has a 50/50 hit and miss success rate. I think this is because the form load gets activated each time the query is refreshed afterupdate. And the form load has the new record syntax so it sort of nullifies it each time. How do I fix that?

Any kind of help would be great!

Cheers!

Cirrostratus is offline   Reply With Quote
Old 07-19-2013, 01:26 PM   #2
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: find data using field in form

Is EmpID a text field? The quotes should not be used for a numeric field.
ie
Private Sub Emp_ID_AfterUpdate() Me.Filter = "EmpID = " & Me.Emp_ID me.FilterOn= true
End SubYou don't need to go to a new record on opening the form. You could put a non existent filter record in the Form Load event.
Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
Cirrostratus (07-22-2013)
Old 07-22-2013, 04:24 AM   #3
Cirrostratus
Newly Registered User
 
Join Date: May 2013
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
Cirrostratus is on a distinguished road
Re: find data using field in form

Yes, EmpID is a text field (don't ask why)..

I'm not sure what you mean by a non existent filter record? Can you help on that a little more?

Cirrostratus is offline   Reply With Quote
Old 07-22-2013, 11:41 AM   #4
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: find data using field in form

Use Me.FilterOn = True instead of
DoCmd.RunCommand acCmdApplyFilterSort

Also, in the Form Load, instead of going to a new record to display a blank form, add your filtering code to go to a non existent record
Me.Filter="EmpID = ''"
Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
Cirrostratus (07-22-2013)
Old 07-22-2013, 07:05 PM   #5
Cirrostratus
Newly Registered User
 
Join Date: May 2013
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
Cirrostratus is on a distinguished road
Re: find data using field in form

For some reason, that still doesn't work. I've now got:

Code:
Private Sub Emp_ID_AfterUpdate()
Me.Filter = "EmpID = '" & Me.Emp_ID & "'"
Me.FilterOn = True
DoCmd.Requery
End Sub
When the user enters the EmpID, it just basically reloads the form but nothing populates in the fields. Its really weird..

The form load is now like you suggested.

?!?
Cirrostratus is offline   Reply With Quote
Old 07-22-2013, 11:10 PM   #6
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: find data using field in form

What happens if you take out the line
DoCmd.Requery

Um, why did you add it? It wasn't in your original post.
Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
Cirrostratus (07-23-2013)
Old 07-23-2013, 04:28 AM   #7
Cirrostratus
Newly Registered User
 
Join Date: May 2013
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
Cirrostratus is on a distinguished road
Re: find data using field in form

I've been trying different things so that was one of them. It makes no difference if its there or not. After you enter the EmpID in the form, the form "flashes" and then it clears out the EmpID and you get a blank form. The query itself works great, just for some reason the form is not populating what's in the query. I'm up for trying anything right now..

Thanks again for all the help so far!

Cirrostratus is offline   Reply With Quote
Old 07-23-2013, 12:07 PM   #8
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: find data using field in form

Time to post your database.
Cronk is offline   Reply With Quote
Old 07-25-2013, 09:29 AM   #9
Cirrostratus
Newly Registered User
 
Join Date: May 2013
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
Cirrostratus is on a distinguished road
Re: find data using field in form

Ok, I managed to solve the issue.

The problem was that my query had the reference to the Form in the criteria for EmpID field. Apparently the code referencing to the query and the query referencing to the form caused some strange circular effect where it sort of nullified itself. When I took that out, the issue went away and it started working.

So my form load looks like this:

Code:
Private Sub Form_Load()
On Error GoTo Form_Load_Err
 
    Me.Filter = "EmpID = ''"
    Emp_ID.SetFocus
 
Form_Load_Exit:
    Exit Sub
Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit
End Sub
And my afterupdate on the EmpID fields looks like this:

Code:
Private Sub Emp_ID_AfterUpdate()
Dim strFilter As String
strFilter = "EmpID = '" & Me.Emp_ID & "'"
Me.FilterOn = True
Me.Filter = strFilter
Me.StopTime = Now()
ReasonID.SetFocus
End Sub
And it works.. thanks Cronk for all the input, I appreciate it!

Cirrostratus is offline   Reply With Quote
Reply

Tags
after update , bound query , form filter , form load , new record

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find Blanks Spaces in Field using FIND Keyword JohnLee Queries 14 03-31-2010 02:24 AM
Find table and field for a form control Pauldohert General 3 01-07-2008 08:24 AM
Find a field in a form itlearner Forms 1 08-28-2006 07:45 PM
open form find specific data Milothicus Modules & VBA 1 01-13-2005 12:40 AM
go to record in a form - find by field value cargobay 69 Forms 2 05-10-2001 04:36 PM




All times are GMT -8. The time now is 06:56 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World