Please Help : Find methods and recordsets

[Guest]

I have created a form called frmModify that is based on tblModify. Within the
form I have subform(called frmExisting). On the the form Modify, I have a
list box where users can pick a conveyor to modify. The only problem is that
the table Modify has many conveyors with the same name. The way I'm
separating this issue is that a project name and description have to be
entered. Within each unique project, the conveyor name has to be unque. This
works fine.

But all in all in my database i have several conveyor names that are the
same. When using my list box to select the conveyor that I want to modify.
It returns the conveyor BUT the conveyor that it returns is not in the
Project that I'm currently working on.

I think maybe the problem is the recordset. Looking at the code it's using
the FindFirst property. How can I code this to return the conveyor name that
correlates with the project and description that's being worked on?

 

[Rick B]

There is no need to post your issue in every newsgroup. Do you really need
that many responses? Do you really think it is efficient for multiple
people to work on your issue when you may already have an answer in another
group?

 

[Roger Carlson]

The FindFirst method has a parameter where you can specify a search
criteria, basically a WHERE clause without the Where keyword. Since you
didn't list any code, I'll have to illustrate with some of my own:
strStockNumber = "25"
strWidth = "10D"
strCriteria = "[StockNumber] = '" & strStockNumber & "' And [Width]
= '" & strWidth & "'"
rstShoes.FindFirst strCriteria

Of course, you would likely get your variable values from a form or
something, rather than hard-coding them. Also, be aware that because the
variables above are strings, you have to embed quote marks in the criteria
string. If they were both numbers, it would look something like this:
strCriteria = "[StockNumber] = " & intStockNumber & " And [Width] =
" & intWidth
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

I am sorry here's the code that I have so far

Private Sub List1026_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List1026], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

ID is my conveyor name
The fields that also need to be included are Project name and Description

Can you show me the code using the fields listed above

The FindFirst method has a parameter where you can specify a search
criteria, basically a WHERE clause without the Where keyword. Since you
didn't list any code, I'll have to illustrate with some of my own:
strStockNumber = "25"
strWidth = "10D"
strCriteria = "[StockNumber] = '" & strStockNumber & "' And [Width]
= '" & strWidth & "'"
rstShoes.FindFirst strCriteria

Of course, you would likely get your variable values from a form or
something, rather than hard-coding them. Also, be aware that because the
variables above are strings, you have to embed quote marks in the criteria
string. If they were both numbers, it would look something like this:
strCriteria = "[StockNumber] = " & intStockNumber & " And [Width] =
" & intWidth
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have created a form called frmModify that is based on tblModify. Within the
form I have subform(called frmExisting). On the the form Modify, I have a
list box where users can pick a conveyor to modify. The only problem is that
the table Modify has many conveyors with the same name. The way I'm
separating this issue is that a project name and description have to be
entered. Within each unique project, the conveyor name has to be unque. This
works fine.

But all in all in my database i have several conveyor names that are the
same. When using my list box to select the conveyor that I want to modify.
It returns the conveyor BUT the conveyor that it returns is not in the
Project that I'm currently working on.

I think maybe the problem is the recordset. Looking at the code it's using
the FindFirst property. How can I code this to return the conveyor name that
correlates with the project and description that's being worked on?

 

[Roger Carlson]

Something like this:
Dim strCriteria as String
strCriteria = "[ID] = " & Str(Nz(Me![List1026], 0)) & _
" And [Project Name] = '" & Me![Project Name] & _
"' And [Description] = '" & Me!Description & "'"

rs.FindFirst strCriteria

The above assumes that the textboxes on the form are the same as the field
names. If they are not, you will have to change it after the "Me!". I
always put criteria strings in a variable because then you can display the
value for debugging:

msgbox strCriteria
or
debug.print strCriteria

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I am sorry here's the code that I have so far

Private Sub List1026_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List1026], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

ID is my conveyor name
The fields that also need to be included are Project name and Description

Can you show me the code using the fields listed above

The FindFirst method has a parameter where you can specify a search
criteria, basically a WHERE clause without the Where keyword. Since you
didn't list any code, I'll have to illustrate with some of my own:
strStockNumber = "25"
strWidth = "10D"
strCriteria = "[StockNumber] = '" & strStockNumber & "' And [Width]
= '" & strWidth & "'"
rstShoes.FindFirst strCriteria

Of course, you would likely get your variable values from a form or
something, rather than hard-coding them. Also, be aware that because the
variables above are strings, you have to embed quote marks in the criteria
string. If they were both numbers, it would look something like this:
strCriteria = "[StockNumber] = " & intStockNumber & " And [Width] =
" & intWidth
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have created a form called frmModify that is based on tblModify.

Within
the

form I have subform(called frmExisting). On the the form Modify, I have a
list box where users can pick a conveyor to modify. The only problem

is
that

the table Modify has many conveyors with the same name. The way I'm
separating this issue is that a project name and description have to be
entered. Within each unique project, the conveyor name has to be

unque.
This

works fine.

But all in all in my database i have several conveyor names that are the
same. When using my list box to select the conveyor that I want to modify.
It returns the conveyor BUT the conveyor that it returns is not in the
Project that I'm currently working on.

I think maybe the problem is the recordset. Looking at the code it's using
the FindFirst property. How can I code this to return the conveyor

name
that

correlates with the project and description that's being worked on?

 

[Guest]

Hi Roger,

I tried the code, but I'm getting some errors,

Some of the conveyor names in the database are PD1-1,M2-2, and so on

But when I click on the conveyor in the list box I get the error

"The Microsoft Jet database engine does not recognize "M2" as a valid field
name or expression" when I try to choose conveyor M2-2

Something like this:
Dim strCriteria as String
strCriteria = "[ID] = " & Str(Nz(Me![List1026], 0)) & _
" And [Project Name] = '" & Me![Project Name] & _
"' And [Description] = '" & Me!Description & "'"

rs.FindFirst strCriteria

The above assumes that the textboxes on the form are the same as the field
names. If they are not, you will have to change it after the "Me!". I
always put criteria strings in a variable because then you can display the
value for debugging:

msgbox strCriteria
or
debug.print strCriteria

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I am sorry here's the code that I have so far

Private Sub List1026_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List1026], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

ID is my conveyor name
The fields that also need to be included are Project name and Description

Can you show me the code using the fields listed above

The FindFirst method has a parameter where you can specify a search
criteria, basically a WHERE clause without the Where keyword. Since you
didn't list any code, I'll have to illustrate with some of my own:
strStockNumber = "25"
strWidth = "10D"
strCriteria = "[StockNumber] = '" & strStockNumber & "' And [Width]
= '" & strWidth & "'"
rstShoes.FindFirst strCriteria

Of course, you would likely get your variable values from a form or
something, rather than hard-coding them. Also, be aware that because the
variables above are strings, you have to embed quote marks in the criteria
string. If they were both numbers, it would look something like this:
strCriteria = "[StockNumber] = " & intStockNumber & " And [Width] =
" & intWidth
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have created a form called frmModify that is based on tblModify. Within
the
form I have subform(called frmExisting). On the the form Modify, I have a
list box where users can pick a conveyor to modify. The only problem is
that
the table Modify has many conveyors with the same name. The way I'm
separating this issue is that a project name and description have to be
entered. Within each unique project, the conveyor name has to be unque.
This
works fine.

But all in all in my database i have several conveyor names that are the
same. When using my list box to select the conveyor that I want to
modify.
It returns the conveyor BUT the conveyor that it returns is not in the
Project that I'm currently working on.

I think maybe the problem is the recordset. Looking at the code it's
using
the FindFirst property. How can I code this to return the conveyor name
that
correlates with the project and description that's being worked on?

 

[Roger Carlson]

If ID is a text value, then the criteria should look like this:

strCriteria = "[ID] = '" & Str(Nz(Me![List1026], 0)) & _
"' And [Project Name] = '" & Me![Project Name] & _
"' And [Description] = '" & Me!Description & "'"

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi Roger,

I tried the code, but I'm getting some errors,

Some of the conveyor names in the database are PD1-1,M2-2, and so on

But when I click on the conveyor in the list box I get the error

"The Microsoft Jet database engine does not recognize "M2" as a valid field
name or expression" when I try to choose conveyor M2-2

Something like this:
Dim strCriteria as String
strCriteria = "[ID] = " & Str(Nz(Me![List1026], 0)) & _
" And [Project Name] = '" & Me![Project Name] & _
"' And [Description] = '" & Me!Description & "'"

rs.FindFirst strCriteria

The above assumes that the textboxes on the form are the same as the field
names. If they are not, you will have to change it after the "Me!". I
always put criteria strings in a variable because then you can display the
value for debugging:

msgbox strCriteria
or
debug.print strCriteria

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I am sorry here's the code that I have so far

Private Sub List1026_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List1026], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

ID is my conveyor name
The fields that also need to be included are Project name and Description

Can you show me the code using the fields listed above


:

The FindFirst method has a parameter where you can specify a search
criteria, basically a WHERE clause without the Where keyword. Since you
didn't list any code, I'll have to illustrate with some of my own:
strStockNumber = "25"
strWidth = "10D"
strCriteria = "[StockNumber] = '" & strStockNumber & "' And [Width]
= '" & strWidth & "'"
rstShoes.FindFirst strCriteria

Of course, you would likely get your variable values from a form or
something, rather than hard-coding them. Also, be aware that

because
the

variables above are strings, you have to embed quote marks in the criteria
string. If they were both numbers, it would look something like this:
strCriteria = "[StockNumber] = " & intStockNumber & " And [Width] =
" & intWidth
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have created a form called frmModify that is based on tblModify. Within
the
form I have subform(called frmExisting). On the the form Modify,

I
have a

list box where users can pick a conveyor to modify. The only

problem
is

that
the table Modify has many conveyors with the same name. The way I'm
separating this issue is that a project name and description have

to
be

entered. Within each unique project, the conveyor name has to be unque.
This
works fine.

But all in all in my database i have several conveyor names that

are
the

same. When using my list box to select the conveyor that I want to
modify.
It returns the conveyor BUT the conveyor that it returns is not in the
Project that I'm currently working on.

I think maybe the problem is the recordset. Looking at the code it's
using
the FindFirst property. How can I code this to return the

conveyor
name

that
correlates with the project and description that's being worked on?