Forms

I have a form that my guys input into.  I put 2 command buttons on the switchboard one for new data and one to edit old data.  For the edit data form it has the query with a criteria box where it asks for the primary key of the table (ex. K6-003-2015).  If I put that into the box the record comes up for editing but when I have this data in my primary key (ex. K7-003-2015A) nothing comes up.  There is an input mask involved which is A0\-000\-0000C.  Im not sure if that has something to do with this problem.  It is a text field.  Any comments or thoughts on what may be the issue.

thanks for any help
jpb12345Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Input masks have nothing to do with the data that is stored - they simply force the user to input data in a specific way.

If the value you enter doesn't bring up a record, then it's a good bet that you've entered data incorrectly, or your data is stored incorrectly. You can determine this by going directly to the table, locating the trouble row (the one with K7-003-2015A) and copying that value. Then run your query again and paste it directly into the input box.

As an aside, using text values like this for a Primary Key is often troublesome. Not to say you cannot do it, but often we find that those values have some sort of meaning - that is, the "K7" part means something, the "003" part means something, etc etc. That's okay until one of those parts needs to change (i.e. someone enters K8 instead of K7). When you do this, you must insure that all "downstream" data gets this modification as well. IMNSHO you should use surrogate keys  in your tables. A surrogate key is one that never changes, and is used strictly for linking and such. The Access Autonumber field is a very good example of a surrogate key.
Helen FeddemaCommented:
No need for a separate form for adding or editing (as on old word processors I used back in the 1980's) -- you can use the same form for both.  I recommend placing an unbound combo box in the form header, with a row source of the form's record source (or a cut-down version of it) with the key field showing in the drop-down list.  This will avoid problems with incorrect entry into a text box.  Here is some boilerplate code for a record selector combo box:

Private Sub cboSelect_AfterUpdate()
'Created by Helen Feddema 29-Apr-2011
'Last modified by Helen Feddema 29-Apr-2011

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

============Subform version=============

Private Sub cboSelectOrder_AfterUpdate()
'Created by Helen Feddema 7-Mar-2012
'Last modified 7-Mar-2012

On Error GoTo ErrorHandler

   Dim strSearch As String
   Dim frmSub As Access.Form
   
   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control on the subform
   Set frmSub = Me![sub_____________].Form
   frmSub.Recordset.FindFirst strSearch
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window


and here is what one looks like in action (in this case, the more common situation where an AutoNumber ID is the bound field, but its column width is set to zero so it won't show in the drop-down list):

Record Selector Combo Box
For editing, just select the record in the combo box; for adding a record, go to a new record with the little control in the navigation bar, or add a more noticeable New Record button.
jpb12345Author Commented:
I am using one form but the 2 command buttons one for edit and one for adding.  I already went to the table and copied the data and then put it into the input box and nothing came up
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Then I'd suspect something is wrong with your query. You'd have to upload your database here for us to help further. If you do, be sure to obfuscate any sensitive data.
jpb12345Author Commented:
I found out the problem.  By having an input mask the users werent putting the dashes and it was stored with dashes in the table.  When started looking more into it the input field wanted no dashes and everything seemed to work.  It was because I added the input mask after some of the data was in.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jpb12345Author Commented:
I just finally started digging into the database and finally I saw the issue
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.