DoCmd.Requery
Me.RecordsetClone.FindFirst " CompanyName = '" & Me.SearchBox & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![SearchBox] & "]"
End If
End Sub
I have been trying to figure what it actually means. I found it on a premade search tool and have been trying to customise it for a while now (as you could probably tell from any previous posts you may have read).
I cannot find a VB dictionary anywhere that explains what RecordsetClone is, the FindFirst function and Bookmark function?
Has this not been looked at yet?
Are you actually receiving an error - or just the code is giving you your failing condition?
If you check for a value in Me![SearchBox] before proceeding then you can prevent the code from going further.
Code:
If IsNull(Me!SearchBox) Then
Msgbox "No value to locate"
Exit Sub
End If
DoCmd.Requery
Me.RecordsetClone.FindFirst "CompanyName = '" & Me.SearchBox & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me!SearchBox & "]"
End If
End Sub
The reason for the failure is that, if there's no value entered (i.e. SearchBox is Null) then your search criteria looks like
Me.RecordsetClone.FindFirst "CompanyName = ''"
i.e. you're searching for
CompanyName = ''
You're less likely to have such entries. So no match is found.
Or are you saying that you're actually receiving an error?
That can happen on datatypes that aren't delimited (i.e. numeric types) as the search then becomes
FieldName =
which fails.
To explain just a little about your question on the code...(which is Wizard generated by Access):
RecordsetClone is a property of the form which exposes a recordset - which is a direct clone of the data in the form. You can use this property to return a fixed recordset to navigate or update as you see fit. You can use the properties and methods of a recordset upon it - in this case the "FindFirst" method.
FindFirst is a method (or action) of a DAO recordset. It allows you to navigate to a record within the recordset based upon specified criteria.
Bookmark is a property of a recordset and a form. It simple holds data (it's a byte array but you don't need to worry about that at all) which identifies a given row in that recordset. It identifies it only for that current recordset. If you opened the form again - different Bookmark properties would be assigned.
As the form and its recordset share the same bookmark - you can use the bookmark property of the recordset to specify the record position of the form. (Setting the bookmark property of the form causes the form to navigate to that matching bookmark location).
Ah thankyou. It is access showing a failing condition, basically my list is full of contact details... and though we mainly work with companies, we do deal with some single people, so it's not abnormal for a contact to not have a company name.
It's bad data entry when some of them don't even have a last name either?
You were much help in breaking down the commands for me...
I have just had an epipheny... I'll see if it works and report back!
Private Sub SearchBox_AfterUpdate()
DoCmd.Requery
Me.RecordsetClone.FindFirst " CompanyName = '" & Me.SearchBox.Column(1) & "'"
Me.RecordsetClone.FindFirst " FirstName = '" & Me.SearchBox.Column(2) & "'"
Me.RecordsetClone.FindFirst " LastName = '" & Me.SearchBox.Column(3) & "'"
Me.RecordsetClone.FindFirst " Address = '" & Me.SearchBox.Column(4) & "'"
Me.RecordsetClone.FindFirst " [Address 2] = '" & Me.SearchBox.Column(5) & "'"
Me.RecordsetClone.FindFirst " [Post Code] = '" & Me.SearchBox.Column(6) & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![SearchBox] & "]"
End If
End Sub
I discovered that by repeating the initial search code, telling access where to find the "first" line of the record set, but with different criteria, it would then perhaps find an entry for everything,
Guess what... It works!?
Sort of.
It goes through it all now... but errors if there isn't a postcode... because it's the last thing on the list. I tried putting CustomerID next, but access couldn't recognise it? Possible because all the other fields are text and this one is an autonumber.
That's your actual code?
First of all - it hadn't been clear that this was a combo control that you were using.
(Hence the wizard generated code I guess - used verbatim originally - I thought it had been copied and used for a textbox instead).
You're repeating a FindFirst operation for every field?
That you only take action after the final FindFIrst means that is the only action that is being used for navigation.
You're performing a new and different search for each other the fields which are returned as columns in your combo - but only navigating the form based on the final (Postcode) search.
However every table should have a Primary Key (without exception IMO).
It seems (based on it being an autonumber) that yours is CustomerID.
Since selection is via a list control (and you're therefore selecting an existing entry rather than typing for a prospective search) then you can and should be using this field.
The only issue you've found is with delimitation. (There is none for numeric data types - of which autonumber is one. It's type is Long).
Presumably, all you'd need would be the single method of
Incidentally - the
DoCmd.Requery
Is there a specific reason you've included it? (New records are common?)
Unrequired requeries are all hits on the database, reloading the recordsource.
Not necessarily harmful in itself - but cumulatively with other users it becomes so.
Private Sub SearchBox_AfterUpdate()
DoCmd.Requery
Me.RecordsetClone.FindFirst " CompanyName = '" & Me.SearchBox.Column(1) & "'"
Me.RecordsetClone.FindFirst " FirstName = '" & Me.SearchBox.Column(2) & "'"
Me.RecordsetClone.FindFirst " LastName = '" & Me.SearchBox.Column(3) & "'"
Me.RecordsetClone.FindFirst " Address = '" & Me.SearchBox.Column(4) & "'"
Me.RecordsetClone.FindFirst " [Address 2] = '" & Me.SearchBox.Column(5) & "'"
Me.RecordsetClone.FindFirst " [Post Code] = '" & Me.SearchBox.Column(6) & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![SearchBox] & "]"
End If
End Sub
I discovered that by repeating the initial search code, telling access where to find the "first" line of the record set, but with different criteria, it would then perhaps find an entry for everything,
Guess what... It works!?
Sort of.
It goes through it all now... but errors if there isn't a postcode... because it's the last thing on the list. I tried putting CustomerID next, but access couldn't recognise it? Possible because all the other fields are text and this one is an autonumber.
Private Sub SearchBox_AfterUpdate()
Me.RecordsetClone.FindFirst " [CustomerID] = ' " & Me.SearchBox.Column(0) & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![SearchBox] & "]"
End If
End Sub
Run-time error: 3070, does not recognize 'CustomerID' as a valid field name or expression.
I tried removing the square brackets, adding Me!, removing the (0)... nothing removed the error? I have tripple checked the spelling and it is correct.
I even tried WIS's method, and I got the same message!?
I think I tried this before a long time ago, and assumed it was becasue of the autonumber field type and just stopped using it?
Well the code assumes that CustomerID is included in your form's recordsource!
If it's a query which doesn't include it then it will fail.
It's certainly not failing due to the autonumber datatype.
It's equally not due to the combo - as it must have a 0 column if it's bound to other data too.
Ah, I did something and I now keep getting error 3464: which is a data type error.
Perhaps thats why i initially thought it was a autonumber problem, as everything else is text?
Im not ignoring you, but I just wanted to try something, and found that the query listbox I'm using, "SearchBox" has an option "BoundColumn"
I then found out that it was set to "2", which is the column in the query where the Company Name is kept.
I experimented, and 3 was first name and 4 was Last name, (CustomerID was 1, and I did try to use the CustomerID where bouncolumn is 1, and I still received error 3464) and I intergrated it:
Code:
Private Sub SearchBox_AfterUpdate()
Dim SearchOption As String
Dim BoundColumn As Integer
DoCmd.Requery
Me.SearchBox.BoundColumn = 2
Me.RecordsetClone.FindFirst " [COLOR=DarkRed][B]CompanyName[/B][/COLOR] = '" & Me.SearchBox & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
Me.SearchBox.BoundColumn = 4
Me.RecordsetClone.FindFirst " [COLOR=Navy][B]LastName[/B][/COLOR] = '" & Me.SearchBox & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
Me.SearchBox.BoundColumn = 3
Me.RecordsetClone.FindFirst " [COLOR=Green][B]FirstName[/B][/COLOR] = '" & Me.SearchBox & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Iuno!?"
End If
End If
End If
End Sub
I have basically used the else statement (which initially returned the error "Could not find []") But instead of this, I replace the Bound Column with the right one and use a different FindFirst statement.
Then if all else fails an error box appears.
Now Completely random errors occur?
For example, on my list, CustomerID 1 : "A B Commercial", Mr. B Thomas when I click on it it will jump to entry 1275 (no company) Mrs Katheryn Thomas.
And If there isnt a last name, it will output the msgBox ("Iuno!?")
This has, somehow, moved into the category of "far, far harder than it needs to be".
The problem you initially had was with the FieldName in your form's recordsource.
Your datatype mismatch has been caused by something that's been done since then.
For something so standard as wizard generated code I'm reluctant - but attach the MDB with the table and form. (I feel it will be quicker that way).
No need to apologise. Just one of those things. All part of the learning process.
(i.e. It's not Microsoft's fault either per se).
Your example seemed involved/confused in trying to achieve two things at once.
Filtering (by the query having criteria refering to the list control) and navigation - the code you've been battling with here.
You actually want only one or the other.
As I sign off I've include three copies of your original form.
Simple1 is the navigation as it should be (I've removed the search textbox - this is just an example of navigation using the FindFirst method).
Simple2 is a similar example but with Filtering instead of navigation (you'll notice that the form always has a rowsource of one record at a time).
SimpleLess is a less simple example. It includes the original Search textbox which is filtering the Listbox. Selection from the listbox then Filters the main Form. Note that the Search Textbox actually feeds values into a hidden textbox - which is actually used in the query for filtering. (That is because you can't reference the Text property externally).
See how you get on.
If I can offer one piece of advice it's don't leap in and start changing things.
Understand it first as much as possible.
Remember it's progressive. Textbox filters Listbox then Listbox filters form.
All queries are implemented as private SQL statements in the forms and controls (i.e. there are no new query objects in the DB - as the number of them for the three different examples might confuse the issue).
I got it, lookin at the structure of Sampleless, I noticed what you have done to fix it.
I was basing mine on queries, yet what you have corrected is the source code for the form was actually taken from the search bar, relying on the customerID to connect them both.
Ingeneous!
I can explain it properly in words but I understand what went wrong now!
