jpb12345
asked on
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
thanks for any help
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:
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):
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.
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 Suband 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):
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.
ASKER
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I just finally started digging into the database and finally I saw the issue
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.