Error Trying To Search For Customerno In Unbounded Form?
Jun 19, 2005
Hi guys. I made a unbounded form that has option to search for customerno .
Whenever I try type a customerno and click on the search button I get the following error.
I be happy if some expert help fix this error. Thanks
Error
Run -time error '2109':
There is no field named 'customerno' in the current record
http://i5.photobucket.com/albums/y180/method007/searcherror.jpg
=====>searcherror pic of the form
Code:Option Compare DatabaseOption ExplicitSub clearTextBoxes()''clearing the tow texboxes txtCompanyName and txtCustomerIdMe.customerNumber.Value = ""Me.customerName.Value = ""End SubSub getReadyForAnAddOperation()Me.cmdSave__.Enabled = TrueMe.cmdSave__.SetFocusMe.cmdCancel.Enabled = TrueMe.cmdAdd__.Enabled = FalseMe.cmdEdit.Enabled = FalseMe.cmdDelete.Enabled = FalseEnd SubSub stateOnLoad()Me.customerName.SetFocusMe.cmdCancel.Enabled = TrueMe.cmdSave__.Enabled = FalseMe.cmdEdit.Enabled = TrueMe.cmdAdd__.Enabled = True'''disableing the cancel and save button on load'''Me.cmdCancel.Enabled = False'''Me.cmdSave__.Enabled = FalseEnd Sub'declaring subrotineSub FillFeilds()Me.customerNumber = myRS.Fields("customerno")Me.customerName = myRS.Fields("customername")End SubPrivate Sub cmdAdd___Click()clearTextBoxesgetReadyForAnAddOperationpbAddingARecord = True'''changing the value of this boolean variablemyRS.AddNewEnd SubPrivate Sub cmdCancel_Click()FillFeildsstateOnLoadEnd SubPrivate Sub cmdDelete_Click()Dim x As Variantx = MsgBox(" You are abut to delete " & Me.customerName & " from this table - proceed ? ", vbOKCancel)If x = 1 ThenWith myRS.Delete.MoveFirstFillFeildsstateOnLoadEnd WithEnd IfEnd SubPrivate Sub cmdEdit_Click()''clearTextBoxesgetReadyForAnAddOperationpbEditingARecord = True'''changing the value of this boolean variableEnd SubPrivate Sub cmdMoveFirst_Click()myRS.MoveFirstFillFeildsEnd SubPrivate Sub cmdMoveLast_Click()myRS.MoveLastFillFeildsEnd SubPrivate Sub cmdMoveNext_Click()If pbAddingARecord = True Or pbEditingARecord = True ThenMsgBox ("Please save or cancel changes first ")Exit SubEnd IfmyRS.MoveNextIf myRS.EOF ThenMsgBox ("Last Record")myRS.MovePreviousEnd IfFillFeildsEnd SubPrivate Sub cmdMovePreviouse_Click()myRS.MovePreviousIf myRS.BOF ThenMsgBox (" First record")myRS.MoveNextEnd IfFillFeildsEnd SubPrivate Sub cmdSave___Click()If pbAddingARecord = True ThenmyRS.AddNewEnd IfIf pbEditingARecord = True ThenmyRS.EditEnd If'''inserting the value of textboxes to the table fields.feeding the date to record setmyRS.Fields("customerno").Value = Me.customerNumber.ValuemyRS.Fields("customername").Value = Me.customerName.Value'''calling update method. it comittes the changesmyRS.UpdatepbAddingARecord = FalsepbEditingARecord = FalsestateOnLoadEnd SubPrivate Sub cmdSearch_Click() Dim strStudentRef As String Dim strSearch As String 'Check txtSearch for Null value or Nill Entry first. If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me![txtSearch].SetFocus Exit SubEnd If'--------------------------------------------------------------- 'Performs the search using value entered into txtSearch'and evaluates this against values in customerno DoCmd.ShowAllRecords DoCmd.GoToControl ("customerno") DoCmd.FindRecord Me!txtSearch '''Me.customerno.SetFocus customerNumber.SetFocus strStudentRef = customerNumber.Text txtSearch.SetFocus strSearch = txtSearch.Text 'If matching record found sets focus in customerNumber and shows msgbox'and clears search control If strStudentRef = strSearch Then MsgBox "Match Found For: " & strSearch, , "Congratulations!" customerNumber.SetFocus txtSearch = "" 'If value not found sets focus back to txtSearch and shows msgbox Else MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus End IfEnd SubPrivate Sub Form_Load()Set db = CurrentDb()'''Set myRS = db.OpenRecordset("select * from customer")Set myRS = db.OpenRecordset("customer", dbOpenTable)''' need to learn how to add index to customer table'''myRS.Index = ("Company Name")'calling subroutinestateOnLoadFillFeildsEnd SubPrivate Sub lblFindIt_Click()With myRSSelect Case Me.lblFindIt.CaptionCase "Company Name"Me.lblFindIt.Caption = " First Name "''' this indexcontactfirstname should already exist in the table''' you can also create index trough code on tables. this method''' not good in multi user evironment . best way to create indexes and''' refere them trough code.Index = "CotactFirstName"Case "First Name"Me.lblFindIt.Caption = " Last Name ".Index = "CotactLastName"Case "Last Name "Me.lblFindIt.Caption = "Company Name ".Index = "CompanyName"End SelectEnd WithEndEnd SubPrivate Sub textFindIt_Change()Dim strSeek As VariantDim posInmyRS As Variant''' feeding it the value from text boxstrSeek = Me![textFindIt].TextWith myRSposInmyRS = myRS.Bookmark.Seek ">=", strSeekIf .NoMatch = True ThenmyRS.Bookmark = posInmyRSExit SubEnd IfFillFeilds End With End Sub
View Replies
ADVERTISEMENT
Jan 21, 2005
I've created an unbounded form because I have user who accidently delete a field without realizing it and ask how come it did that. SO i've created and unbounded form for them to review and save with a command button, but I'm having a problem using the vbaunbound.zip file that i've downloaded from sbaxter. I created the update using DAO and I keep getting "An Error Occurred. The System doesn't know which action to take" I edited all fields that were in the example to point to the correct fields and I Keep getting this message and then the cmdBox gray's out...Any input would be greatly appreciated AGAIN...
Thanks...
Eddie...
View 14 Replies
View Related
Apr 4, 2006
Will some be able to assist me throught this please? I've been tasked (AGAIN ) to add a command feature to a form in access to do an add new record. What the user would like to do is be able to do a search and make a change by adding the existing as new record. For example survey number 3738 is open and they want to make a change by making inspector a different name and then click add and boom it's a new record. I've done this in the past but I cant' rmember how i did it before...
YOUR help is truly appreciated...
View 4 Replies
View Related
Jan 7, 2015
I have a search form that's slightly modified search form modeled after the one available here on this site. It's a main form with a list box. I use the listbox to narrow down the results I want from the search function.
The search details are shown in a sub form that contains sub-sub forms
As seen in the code below, I am using the listbox to set a filter on the subform . Works famously until I search for something that does not exist in the database. I then receive runtime error 3075.
I believe it's related to the way I'm showing the details in the subform (by enabling a filter). If there is no record in the database that match the search criteria, I don't want any error messages or pop-ups and preferably would like the listbox to be blank.
Attached a screenshot of my form.
red = main form
green = subform
blue = sub-subforms
tan = obscuring the search results
Code:
Private Sub SearchFor_Change()
Dim vSearchString As String
vSearchString = SearchFor.Tex
SrchText.Value = vSearchString
Me.SearchResults.Requery
[Code] .....
View 2 Replies
View Related
Sep 21, 2012
I have a form and when it's opened you get 3 different search questions to answer or leave blank. My question is.... If a search parameter is entered but no results are found to match it, how do I create an error message telling the requestor "No Data Found"?
View 7 Replies
View Related
Jun 18, 2014
why the code below is not functioning properly. When I type in an acronym in the textbox, it keeps saying there is an error "Run-time error '3345': Unkown or invalid field reference 'ABO'." I do have ABO in the field.
The dysfunctional code:
Code:
Private Sub btnFind_Click()
If (TxtFind & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Acronym] = " & TxtFind
[Code] .....
The red highlighted line is where the debugger leads me to. Something with identifying the field? I would like to enable the search procedure to search throughout the entire records rather than just a specific field. How may I write such a line or two?
View 5 Replies
View Related
Jan 29, 2006
hi guys, i have created 1 combo box and 1 text field in my form. For my combo box, I managed to connect it to my table. But when i run the form, i can press the combo button BUT cannot select the name displayed. Worst of all, i cant even type anything on the 2 boxes. is there any properties that I got change?
View 2 Replies
View Related
Oct 6, 2014
When I type the first letter I into the search text area I get the following error
Run-time error '2110'
Microsoft Access can't move the focus to the control SearchResults
Most of the code is below
QRY-SearchAll
SELECT Clients.ClientId, Clients.ClientFileNumber, Clients.ClientShortFileNo, Clients.Salutation, Clients.FirstName, Clients.LastName, [Group Branches].BranchCode, Clients.Phone, Clients.Mobile, Clients.Fax, Clients.BpayRef, Clients.TradingAs, Clients.EntityType, Clients.ABN, Clients.ACN, Clients.Address1, Clients.Address2, Clients.Town, Clients.State, Clients.PostCode, Clients.Country, Clients.Email, Clients.ClientGroup, Clients.DateCreated, Clients.Notes, Clients.LastModified, Clients.UserCode, Clients.BdmCode, Clients.CollLongNo, Clients.CollShortNo,
[Code] ....
View 1 Replies
View Related
Oct 24, 2005
Hopefully there's someone who can help me with this!!! I've had several unsuccesfull attempts and don't know what else to try...
We're trying to force users to make entries in a field called txtComments in the frmQuestionnaire form if they provide negative responses and to stay on that record until the comments are changed. Inside the form is an option group (fraResponses) which contains options (OptA, OptB..etc.) valued from 1 through 7, based on a likert 7 scale. We're only using a likert 6 scale, so the respondents will only see 2-7 on the form. If they options B-D (valued 2-4), they are considered negative, so we want to make sure they enter a comment.
I tried to enter the italicized code under the form on BeforeUpdate, AfterUpdate, CmdUp (click to next question) and CmdDown (click to previous question), with frmQuestionnaire but nothing happens:
There is a module called clsUser which contains the following and where I tried to insert the code:
Set myForm = Forms!frmQuestionnaire
' ================================================== ==
' First, set the value of the variables
' ================================================== ==
lngQuestion = arrQ(lngArray, 0)
lngSession = GetCustomInfo("TestSession")
lngUser = UserID
lngBillet = BilletID
strComment = Nz(myForm.txtComment, "")
strComment = "None"
'If user selects negative responses
If Form_frmQuestionnaire.fraResponses.OptionValue = 2 Or 3 Or 4 And strComment = "None" Then
MsgBox "Please explain the problems you encountered with the system which " & _
"caused you to select an unfavorable response."
Form_frmQuestionnaire.txtComment.SetFocus
End If
lngResponse = myForm.fraResponses
When I place the code there, the dialog box appears for all values and goes to the next record. This is what appears for the CmdUp (click to next question):
If cUser.blnDirty = False And Me.fraResponses = 153 (this is default value for the entire option group) And cUser.blnNew = True Then
cUser.blnDirty = True
End If
cUser.CaptureAnswer
If cUser.lngArray < cUser.UBound_ArrQ() Then
cUser.lngArray = cUser.lngArray + 1
Else
cUser.lngArray = cUser.UBound_ArrQ()
End If
cUser.FillQuestions
cUser.blnDirty = False
I've tried to enter the line of code with the form and the module, but no luck.
I also want it to remain on the same record until the field is changed. The form is unbound and has the following:
Public Sub FillQuestions()
'************************************************* ****
' Purpose: Scroll through the questions
'
' Assumptions: N/A
'
' Effects: N/A
'
' Inputs:
' None
'
' Returns:
' None
'************************************************* ****
Dim lngRG As Long
Set myForm = Forms!frmQuestionnaire
myForm.txtQuestion = arrQ(lngArray, 1)
lngRG = arrQ(lngArray, 2)
myForm.prgProgress.Value = lngArray + 1
myForm.lblProgText.Caption = "Question " & (lngArray + 1) & " of " & lngQuestions
myForm.lblTS.Caption = "Test Session: " & arrQ(lngArray, 3)
myForm.lblQID.Caption = "Question ID: " & arrQ(lngArray, 0)
GetResponseSet lngRG
FillAnswers
If lngArray = 0 Then
myForm.txtComment.SetFocus
myForm.cmdDown.Enabled = False
Else
myForm.cmdDown.Enabled = True
End If
If lngArray >= UBound(arrQ) Then
myForm.txtComment.SetFocus
myForm.cmdUp.Enabled = False
Else
myForm.cmdUp.Enabled = True
End If
End Sub
Public Sub FillAnswers()
'************************************************* ****
' Purpose: Fill out the user's previous responses
'
' Assumptions: N/A
'
' Effects: N/A
'
' Inputs:
' None
'
' Returns:
' None
'************************************************* ****
Dim strSQL As String
Dim recAnswer As New ADODB.Recordset
Set myForm = Forms!frmQuestionnaire
strSQL = "SELECT datResponse.reDatQuestionID, datResponse.reDatRespondentID, "
strSQL = strSQL & "datResponse.reDatResponseSetID, datResponse.reComment "
strSQL = strSQL & "FROM datResponse "
strSQL = strSQL & "WHERE datResponse.reDatQuestionID=" & arrQ(lngArray, 0)
strSQL = strSQL & "AND datResponse.reDatRespondentID=" & UserID
recAnswer.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
If Not recAnswer.EOF Then
myForm.fraResponses = recAnswer!reDatResponseSetID
myForm.txtComment = recAnswer!reComment
blnNew = False
Else
If myForm.fraResponses <> 152 Then
myForm.fraResponses = 153
myForm.txtComment = ""
blnNew = True
End If
End If
recAnswer.Close
Set recAnswer = Nothing
End Sub
Thanks in advance for your help!!
View 1 Replies
View Related
Feb 23, 2005
Hi Folks,
I have a search option on my form that allows the user to search a table by either first name or last name. They double click in the list box on the name they want. I am using the exact same code in another one but it doesn't seem to work. The error message I get is " The Microsoft jet database engine doesn't recognise (the name I selected) as a valid field name or expression"
I think that the error is in the double click event in the code. This is as follows:
_____________________________
Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
Dim db As DAO.Database
Dim rst As DAO.Recordset
DoCmd.OpenForm "Priests"
Set rst = Forms![Priests].RecordsetClone
rst.FindFirst "[LastName] = " & Me.SearchList
Forms![Priests].Bookmark = rst.Bookmark
DoCmd.Close acForm, Me.Name
Exit_SearchList_DblClick:
Exit Sub
Err_SearchList_DblClick:
MsgBox Err.Description
Resume Exit_SearchList_DblClick
End Sub
______________________________
Any suggestions greatly appreciated.
Thanks,
COlin.
View 10 Replies
View Related
Jul 30, 2007
In trying to save/revise a record it keeps giving me an error message "The seach key was not found in any record". It won't even let me delete the record. Can anyone tell me why I am getting the error and how to fix it.
View 2 Replies
View Related
Aug 5, 2014
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 Replies
View Related
Jul 10, 2012
i need to create a system that can search data using keyword.so i want to create search button that i will create it at form. currently i have 1 table and in that table i have 10 fields which are project_name, doc_ref_no, doc_title, volume, book_no, author, doc_status, box_barcode, filling_location, doc_availability.
i have create 2 forms, one form for user to add new record and another one is to search record. the user can search by enter project name, doc title, volume and box barcode. for project name and volume, i use combo box and for doc title and box barcode i'm using text box.
i want to create search function that can detect from user keyword and even user did not fill in all the form, the system still can search the record.
View 5 Replies
View Related
Mar 2, 2006
I have a search form, generally it runs fine...until now. When the record contains a ' in it, I get an error. For example, I can search on last name Smith fine, but unable to search on O'Malley.
I get runtime error 3075
Syntax error (missing operator) in query expression 'lstnam LIKE 'O*M*' AND fstnam LIKE 'g*'
It works with other characters, like ? or . or !, just not the apostrophe '.
Any help would be great.
Here is the code I have for the search button.
Private Sub cmdSearch_Click()
Dim LSQL As String
Dim LSearchString As String
Dim LSearchString1 As String
If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "Please enter a last name."
End If
If Len(txtSearchstring1) = 0 Or IsNull(txtSearchstring1) = True Then
MsgBox "Please enter a first name or portion of the first name"
Else
LSearchString = txtSearchString
LSearchString1 = txtSearchstring1
'Filter results based on search string
LSQL = "select * from tblTEMPMEM"
LSQL = LSQL & " where lstnam LIKE ' " & LSearchString & "*'" & " AND fstnam LIKE '" & LSearchString1 & "*'" & ""
Form_frmGroups_sub1.RecordSource = LSQL
lblTitle.Caption = "Member Details: Filtered by '" & LSearchString & " , " & LSearchString1 & "'"
'Clear search string
txtSearchString = ""
txtSearchstring1 = ""
MsgBox "Results have been filtered. All names containing " & LSearchString & "."
Thanks!
View 4 Replies
View Related
May 7, 2013
I have a search combo box to search for a field on my form. But it just goes to that particular record. The combo box is for client id and it has more than one record. How can I have it return just the records pertaining to the client id choosen in the combo box.
View 5 Replies
View Related
Jul 11, 2006
Hello all,
I try to delete a record and i get this error message "The search key was not found in any record". The key of the table is an autonumber but for some reason in these two records i want to delete has value = 0 .
Any suggestion how to delete these two records?
Thank you in advance!
View 3 Replies
View Related
May 25, 2006
I have a fairly complex database which contains a company table and a staff table along with approx 30 other smaller tables.
The staff records have a field called company which is linked in a one to many relationship to the CompanyId field in the Company table. This is an auto-number field and is up to 5467 currently.
I have enforced data integrity when this relationship was created. The users enter new staff members using a sub-form in the Company form.
A user created a new record for a company and while completing the task the text in all field went a bit crazy and appeared in symbols rather than text. The user shut down the database and restarted it.
Now when you open the company form the staff member appears with the correct company but does not appear anywhere else in the database with this company. The correct company id is 3416 but when I looked in the table the staff members record has an id of 36097146 or something similar. There are only 5467 companies in the company table. I tried to amend this figure and got and error which said "The search keys could not be found in any records".
I tried to delete the record and the same error appeared. When I clicked on the Help button of the error I got a message telling me that the error occurs when an ISAM seek operation fails.
Anybody know what is going on here and how I can delete this record or more importantly why this may have happened in the first place.
View 1 Replies
View Related
Nov 15, 2004
I'm getting a Run-time error 2295: Unknown Message Recepients.
I've got a DB of about 2000 clients. In testing I did a test DB with 50 random
clients. Using any search criteria, it would grab those clients and open a new email
with their email addresses no problem.
However when I do this same thing with my complete DB of 2000 clients, I get this run
time error.
I'm pretty confused here, and anxious. It looks like the program is working, I just need it
to run with all my clients. This is the last thing I must conquer to be finished with this project.
View 3 Replies
View Related
Sep 19, 2014
I have a button that opens a form which is based on a query.
The query has a prompt so when you click the button the prompt pops up.
However if nothing is entered you get a VB error 94 error.
How do i get it to show a msgbox if the search is empty?
View 4 Replies
View Related
Dec 19, 2013
I am getting a runtime error '2110' - access can't move the focus to the control SearchResults.
This ONLY happens when the first character I type in the SearchFor field is the letter "I". All other characters do NOT give the error.
Why it only backfires on character "I"?
The Form has Search field [SearchFor] and a List box [Searchresults].
A requery occurs after every new character in [SearchFor].
View 7 Replies
View Related
Jun 24, 2014
I am using John Big Booty's code for narrowing down the content of a listbox. It works beautifully, with the exception of when I type the character "i" into the search box it gives me a Runtime 2110, cannot setfocus error. I have run through the entire alphabet in lower and upper case and consistently get the code failing on lowercase i only.
Here is the code:
Private Sub searchFor_Change()
'Create a string (text) variable
Dim vSearchString As String
'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = searchFor.Text
[Code] ....
Here is the link to the original thread that the code came from. [URL] ....
View 3 Replies
View Related
Nov 19, 2013
I get this strange behavior in multiple forms I have. When I click on a field for the first time, I get a quick popup that immediatly disappear. It only get the popup when I enter fields I added for search purpose (a listbox which has a query as source, which I build with the value from another input field that the user type in).
I get the error when :
-I enter the input field for the first time
-I type in a value for the first time
-I select an element in the listbox
-Also when I open a form for the first time (happens only for the first opened form)
I don't even have time to see what it is in fact. I had to time a screenshot to see what it was.
So, I get an error like this : "Search referenced file : MSOUTL.OLB". No text in the msgbox, only that title.
[URL] .....
I checked google, found various reference to Outlook stuff. Problem is, I don't use any outlook stuff. I don't get any of those popup when I open the projet under Access 2013, but under Access 2010 I get them. I created the project under Access 2013, but users will use Access 2010.
View 1 Replies
View Related
Jul 24, 2015
My database is in database where we do the editing and using Access for query reports.
Upon importing from External Data Link to the data source by creating a linked table: the table was created but running a query yields the above error.
View 1 Replies
View Related
Jan 31, 2006
HI!
I'm trying to figure out, how to make seach form with one text field, that will accept OR statements. I have one table with fields name, category, and description.
Is there any chance to make search like this:
User will put "part of the string" OR "part of the string2".
Result will be the list of the records that include these parts of the strings in any of the given above fields (name,category,description)
I hope i'm clear enough
Thank You in Advance
for any Advice
View 2 Replies
View Related
Aug 21, 2013
I have a copy of the back-end that gets a search key error 3709 on two records. In other words, I can duplicate the problem.
The interesting part is that I can update any other field on both these records and save the record, but when I try to change two specific fields, I get a Search Key Error and have to ESC out to continue (basically UNDO the change). Both fields are text fields with lengths of 7 characters and 255 characters, and both are COMBO Boxes on the form.
I tried to focus on the form think there was an issue in the code. I can definitely TRAP the 3709 error on the ON ERROR event on the form using "if dataerr = 3709", but then I tried something even simplier.
I went directly to the table and to each of the records. Again I can update any other field in the record but these two specific fields. When I try to change either of them and move to another record, you get a Search Key Error 3709.
By going to the table record directly I'm as low level as I can get. There are no validation rules on either field at the database level. If it was truly CORRUPT would it let me update any of the other fields on either of these records? One is an empid (not a primary key but is indexed with duplicates okay and not required), and one is status code (not a primary key but is indexed with duplicates okay and not required) so they're no critical fields, but something is keeping them from CHANGING.
Just tried something else; deleted the INDEXES on both the fields. Now it works! I am completely confused now because it really wasn't a corrupt record, but the indexes are causing the problem. Do I need to update the indexes somehow when the users selects a new empid or status code?
View 6 Replies
View Related
Sep 28, 2006
Help! A very strange record has materialised in my database. Consisting of characters like these:
༏｀䳾䬲No00:00:00佔㌳774400:00:0000:00:00N/AŸÞ呌ㅏＲ㋾〰YesYesNoYes00:00:0000:00:0000:00:0000:00:00YesYesYes00:00:00〶〹Ｓ叾䅔⁂佐䱏删乕丠塅⁔䅄⹙倠䕒䅐䭃匠呅唠⁐义佃剒000:00:00䍅䱔ｙ䓾
The database is linked to an excel spreadsheet which updates the database. When I try to delete the record I get a message;"The search key was not found in any record" I can't even overwrite the field with proper data either. Please can anyone please explain to me how this record has appeared (in simple terms - I am a very simple person) and how do I get rid of it?
View 2 Replies
View Related
