findfirst access 2000 Results

Page 2 of 2.
Results 21...36 of 36

Sponsored Links:

strCustomerID = InputBox("Please enter Customer number ")
Me.RecordsetClone.FindFirst "CustomerID = " & strCustomerID
If Me.RecordsetClone.NoMatch Then
MsgBox "CustomerID " & strCustomerID & " Not Found!!"
Me.Bookmark = strBookmark
Exit Sub
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

I have a small code that asks for the Customer number through an input box. When i click the event,
a dialog box appears with the words "Please enter Customer Number). Than the input box contains the options Ok and Cancel.
If i click OK then the code wroks, but when i click cancel i receive the message "Syntax error, missing operator"
runtime error 3007.I want instead to close exit the sub but i do not know how.For a greater clarityu here is my code:
strCustomerID = InputBox("Please enter Customer number ")
Me.RecordsetClone.FindFirst "CustomerID = " & strCustomerID
If Me.RecordsetClone.NoMatch Then
MsgBox "CustomerID " & strCustomerID & " Not Found!!"
Me.Bookmark = strBookmark
Exit Sub
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

I would like to imrove the cancel event of the dialog in the input box .Maybe it is possible ?

I'm using this VBA script to navigate a recordset:

rs.FindFirst "[Name_last] = '" & Me![list] contains an appostrophe.

Is there an easy solution, or should I simply exclude all "O'Connors" from the database??!!



Can somebody help me with the code for finding a record by typing its number.I have a table of products.
and each product has an unique number.The first Record is called ProductID.What i need is a command aksing me the number of the product (ProductID) and then finding it. I have looked through some examples of FindFirst in the books,but i am afraid they are already obsolete.

I have a ListBox on my form and i want by double clicking on the contactid in the list box to open the contactid of the form.Both the form and the listbox are based on the table TblClients.Which is the best way to do that ? I know there are several way as FindiFisrt , Search, etc.
I have the following available function :

Dim f As Form
Set f =Forms!frmClients
f.RecordsetClone.FindFirst "ContactID = " & strContactID
If f.RecordsetClone.NoMatch Then
MsgBox "not available"
f.Bookmark = strBookmark
f.Bookmark = f.RecordsetClone.Bookmark
End If

I am haveing a problem getting FindFirst to work with a text field. I have an inventory table that has a numeric ID field and a description. I want to be able to use a combo box using the invdesc and FindFirst. Here is the code I am using:

Private Sub cmbSKUDescription_AfterUpdate()
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "tblinventory.invdesc = ' " & Me![cmbSKUDescription] & "'"
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
End Sub

I am not getting errors, it just doesn't work. I am using DAO and Access 2000

I find the record on a form with the help of a combo box
as follows:
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[CustomerID] = " & Me![Combo47]
Me.Bookmark = Me.RecordsetClone.Bookmark

Also, i have an Option box that filters the records within a given
conditionlike that

Private Sub FilterOptions_AfterUpdate()

' Apply or remove the filter for the option the user chose.

Select Case Me![FilterOptions]
Case 2 ' Berlin
Me.Filter = "afid = 1 "
Me.FilterOn = True ' Apply the filter.
Case 3 ' Paris
Me.Filter = "afid = 2 "
Me.FilterOn = True ' Apply the filter.
Case 4 ' London
Me.Filter = "afid = 3 "
Me.FilterOn = True ' Apply the filter.

ALl that works well.However i want to refine my form and allow searching for a record within the filterred records.And i cannot do it.
For example, if i choose option Number 2, Berlin, i can see in the combo box only those records correspond to afid = 1, however i

cannot firnd the record i want,the combo box just doesnt work.
Can sombeody help me ?

I am trying some of the code from one of Woodys writers for Record Selector combo boxes as shown below:

Private Sub Combo14_AfterUpdate()
Dim strSearch As String

'For numeric IDs
strSearch = "[Emp No] = " & Me![cboSelect]

'Find the record that matches the control.
Me.RecordsetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

I have applied this to the after update event of my form field, and put the combo box into the form header as she suggested. What it does is go directly to the Emp No in the form for the name that I select in the combo box, but the rest of the form fields remain on the current record. Lets say, I choose Diana Smith who is Emp 101 from the combo box. The form goes to Emp 101 in the Emp No field, but all other fields remain on the current record say for instance Bob Brown, whose record I happened to be on before I made my choice in the combo box. I am attaching my database in hopes that you can look at the employee form, and look at the field in question (within the header), and tell me what I am missing in the VBA after update code.
Thank you,
Nannette M. Padgett

Hi folks, I was hoping someone could help.
I'm relatively inexperience with coding. What I'm trying to do is copy a record from an existing one in an Events table (tblEvents) that is selected from a list box on a different form. I've gotten the code to work in finding the appropriate record and populating a set of variables. For some reason, though, the .Update command isn't updating the record.

The pertinent section of code is below (sorry in advance if I should have attached it as a file):

Private Sub lstChoices_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo Err_KeyDecision
Dim rsEvent As Recordset
Set rsEvent = CurrentDb.OpenRecordset("tblEvents")

Dim intEventID As Integer, strEvent As String
Dim strEventDesc As String, booAnnual As Boolean
Dim strPreface As String, mHeader As String, mFooter As String
Dim mHeader2 As String, mFooter2 As String
Dim strfilter As String
If ((KeyCode = vbKeyReturn) And (Me!lstChoices.ItemsSelected.count 0)) Then
strfilter = strfilter & "[EventID] = " & Me!lstChoices.Column(0, Me!lstChoices.ItemsSelected(0))

With rsEvent
.FindFirst strfilter
intEventID = !EventID
strEvent = !Event
strEventDesc = "" & !EventDesc
booAnnual = !Annual
strPreface = "" & !Preface
mHeader = "" & !Header
mFooter = "" & !Footer
mHeader2 = "" & !Header2
mFooter2 = "" & !Footer2
!Event = strEvent
!EventDesc = strEventDesc
!Annual = booAnnual
!Preface = strPreface
!Header = mHeader
!Footer = mFooter
!Header2 = mHeader2
!Footer2 = mFooter2
MsgBox "Record has been updated if you can see this."
End With

I inserted message boxes all the way down the line, and everything executes except for the .Update statement. I can't figure out why.

Thanks in advance for your help.

Charlie T.

Is there any other way to use my code shown below without the use of DAO or ADO? It is because we have different computers and we have to add manually the reference to the DAO library.SInce the computers are of different ages, to me the best solution would be to avoid the dependance of the library of the DAO.Is it possible to rewrite my code avoiding the use of DAO.I beg to be apolgized in case my question sounds stupid.
My code is the the following:

Dim intAnswer As Integer
Dim stLinkCriteria As String
Dim rst As DAO.Recordset
strOrderId = InputBox("Enter Order number You Want to Locate")
If strOrderId = "" Then
Exit Function
End If
DoCmd.Close acForm, "frmCustomerOrders"
DoCmd.OpenForm "FOrderinformation", acNormal, "", "", , acNormal
Set rst = main.RecordsetClone
rst.FindFirst "orderid = " & strOrderId
If main.RecordsetClone.NoMatch Then
MsgBox "

I need some help in rewriting a working function using a string into a function based on the choice from a list box.
The function that i use now successfully is the following
Public Function ftransform()
Dim f As Form
Set f = Forms![FOrderInformation]
Dim strOrderID As String
strOrderID = InputBox("Enter order number ")
If strOrderID = "" Then
Exit Function
End If
DoCmd.OpenForm "FOrderinformation", acNormal, , "OrderID=" & strOrderID
f.RecordsetClone.FindFirst "orderid = " & strOrderID
If f.RecordsetClone.NoMatch Then
MsgBox "

I have created a form which filters out certain data for printing bar codes. I have a continous form, where the user can input the [Number] of multiple labels required with the associated information. On the click of a command button, I run a Make Table Query that deletes the records in the table and then populates it with the new records. This then opens up the report format in hidden preview and prints out using the DoCmd PrintOut, with the field [Number] set as the required labels to print. No problem there, if the user only selects one entry. If more than one entry is created, then the set [Number] of labels printed will default to the first record. So if they have record 1 with 10 labels needed, it will print. But if they add an additional record, with only 5 labels required, they would also get 10. I could use this as a single issue, but I would like the option of allowing multiple entries, if required.

I require a loop to go through the current records in the table and print out according to the [Number] field for each distinct record. Can anyone help me with the code, I can't seem to get the syntax correct. I have tried, FindFirst, and then MoveNext, but its not working properly.

I'm lucky enough to be upgrading someone else's database from Access 97 to 2000, but am having problems with some of the VBA code. The de###### is giving me a "Method or Data Member Not Found" error on recordset .Findfirst and .nomatch methods. Which libraries need to be referenced to clear these errors. I've tried the Microsoft DAO's, but can't seem to find the right combination.



Can anyone help me by explaining the difference between to following two bits of code please?

In both instances you type a few letters into the combo box. It guesses the name and refines it the more letters you type. Hit enter and the form displays that record.

I suspect that the second example is a leftover from Access 97, but I would be very grateful if someone could confirm if that is the case and if not the advantages of using one or the other.

Thanks in anticipation

Michael Peak
Morecambe, England

Private Sub Combo25_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Album_Name] = '" & Me![Combo25] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo25_AfterUpdate()
' Find the record that matches the control.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.RecordsetClone.FindFirst "[Refno] = " & Me![Combo25]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I have a form that contains a combo box that is used for finding records. This form also contains a command button which duplicates the record. When I click the duplicate button, it gives me a run-time error '3058' Index or primary key cannot contain a Null value. When I click debug on this message it takes me to the VB code for the combo box and highlights Me.Bookmark = rs.Bookmark.

It duplicates, but gives me this message everytime. If I remove the primary key combination from the underlying table this problem disappears. However, I do need the primary keys for other forms to operate as desired.

The complete code for the combo box is :

Private Sub Combo230_AfterUpdate()
'Find the record that matches the control
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Search Field]="""&Me![Combo230]&""""
If Not rs.EOF Then Me.Bookmark=rs.Bookmark
End Sub

I greatly appreciate any guidance.

I have a form with four controls on it: three text boxes and one listbox.

The listbox contains two columns: An EventID and a Description. The data for the listbox is gathered from a separate table (ListboxInfo) using a lookup. The table contains 36 static lookup records.

The text boxes are bound to fields in a table (EventLog) containing five fields: ContactID, EventID, DueDate, DoneDate, and Notes. The form is also bound to EventLog.

When the form is opened, it is opened from another form with contact data, from which I obtain the linking ContactID, thereby limiting records to a single person. I now need to create/edit/update records in EventLog anytime I enter data into one of the three text boxes and move to another item in the listbox.

I have tried placing code in the BeforeUpdate event procedure to change/add data to the current record but receive the following error: 'This action was cancelled by an associated object.' Here's the code I've tried.

Private Sub lstBox_BeforeUpdate(Cancel As Integer)
If Me.Recordset.NoMatch Then
Me.Recordset.AddNew 'I tried .Edit here also
Me.Recordset.Fields("ContactID") = Forms!frmEscrow.ContactID
Me.Recordset.Fields("EventID") = lstDescription
If Len(Due) > 0 Then Me.Recordset.Fields("Due") = Due
If Len(Done) > 0 Then Me.Recordset.Fields("Done") = Done
If Len(Notes) > 0 Then Me.Recordset.Fields("Note") = Notes
End If
End Sub

Private Sub lstBox_Click()

Me.Recordset.FindFirst "EventID=" & lstDescription

If Me.Recordset.NoMatch Then
'Unlink the table for proper display of data fields when no records present
Due.ControlSource = ""
Done.ControlSource = ""
Notes.ControlSource = ""
Due = ""
Done = ""
Notes = ""
'Link the textboxes to the table
Due.ControlSource = "Due"
Done.ControlSource = "Done"
Notes.ControlSource = "Note"
End If
End Sub

I've tried this with and without the .AddNew statement but get an error 'Cancel or CancelUpdate without AddNew or Edit.' whenever I access the .Fields() statements.

I need to know if it's possible to update/add records in this manner and if I'm on the right track?

W98, Office 97

My office is migrating over to MSOffice from WordPerfect and I am converting a number of databases from Paradox to Access 97. Lets not even go into why we aren't using Office 2000.

A common technique in Paradox is to use temp tables, since each table exists as an independent object, unlike Access. If you add an underscore before a table name, Paradox treats the table like a temp system table and handles your cleanup.

Does Access have any construct like this? I build a number of temp tables to create complex reports and would like to do the same in Access. But this store everything in one file greatly hinders this. While testing a temp table scenario in Access, I quickly exceeded some memory or storage limits, until I compacted the database. Given what I've read on corruption when compacting, I would like to reserve this for more judicious usage on my main database. I am getting around this concern by writing the temp table out to another database designed for this purpose, but this seems asinine.

Why do I need to have temp tables? Primarily because I build up report fields that are concatenated versions of data in the same field. For example, I have a name field that contains all the names of all people. Some are related to each other. When I print a report I generate several fields that contain concatenations of the names, depending on their relationships. So you might see "surname, name1 & name2" or "surname1, name1 & surname2, name2" in one field and "name1 age; name2 age" in another field. There is also quite a bit of formatting that gets added.

Also, I tried a test doing all of this with an array and it locks the system. System PIII/800MHz-256MB ram, dedicated 3GB SCSI swap drive, 2x40GB IDE's. So plenty of horseys here. There seems to be some Access memory limit I am hitting. I am processing around 400K records. I just set a bookmark to a record, determine the person's type, use findfirst/next to loop through the other records to determine their status, make some decision based on the status & copy the data to the appropriate location, set a handled flag, and move on to the next record. Each record only gets handled once, beyond testing the handled flag. Once the array is build, I write it out to the temp table (in PDOX-doesn't work yet in Access).

Page 2 of 2.
Results 21...36 of 36