HELP WITH UPDATE QUERY

[Guest]

I am not sure is this is possible since i am fairly new to this. I have a
linked table to an xl table and i was to do couple of things with it.
--One is that i have another table that i compare it to and get the unmatch
records...it's there a way to do this without going through the whole process
of creating the unmatch query to update the current table. the xl table needs
to me compared at all times because it might have new records that come from
a different program.

 

[Michel Walsh]

You can create a table and define an index, on one of its field, not
allowing duplicated values into it.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

got it...thanks a lot...can you help with this?
i have a form with a subform in it in a datasheet format. on a click event
of one of the fields, i like to open another form and i like to pass the
object that i just clicked on to that form pertaining field. can this be
posible.

something like this.
DoCmd.OpenForm frmMain, , , "[ID_1]='" &
Forms!frmMultProduction!qrUnmatchProd![ID_1] & " "

but its not doing it
--
need help

You can create a table and define an index, on one of its field, not
allowing duplicated values into it.


Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

DoCmd.OpenForm "frmMain", whereCondition :=
"ID_1=FORMS!frmMultProduction![qrUnmatchProd.ID_1]"



should do. You can skip the arguments, and the coma, if you supply the
argument name, followed by := followed by the value of the argument.
Since you use DoCmd, you can keep the FORMS!formName!ControlName inside the
'string', which has the benefit to NOT have TO supply delimiters in case of
string, or date, since you refer to the holder of the value, not the
constant itself.

I ASSUMED the control name was [qrUnmatchProd.ID_1]. Indeed, your original
message is with an excess of one ! There is one between the keyword FORMS
and the form name, and another one between the form name and the control
name. If you wish to refer to a sub-form control, the syntax is lightly
different.


Hoping it may help,
Vanderghast, Access MVP

got it...thanks a lot...can you help with this?
i have a form with a subform in it in a datasheet format. on a click event
of one of the fields, i like to open another form and i like to pass the
object that i just clicked on to that form pertaining field. can this be
posible.

something like this.
DoCmd.OpenForm frmMain, , , "[ID_1]='" &
Forms!frmMultProduction!qrUnmatchProd![ID_1] & " "

but its not doing it

 

[Guest]

Hey that works just fine...thanks a bunch....

1. now when you want to select an item from the list box, does the same rule
apply....for example,
DoCmd.OpenForm "frmMain", whereCondition :=
"ID_1=FORMS!frmMultProduction!mylistbox"
or is it different systax

2. i know i am asking too much now, but i think you doing a great job
helping me and thanks...i have another question. i have a differetn LIST
BOX.....theList, the user wants to select MULTIPLE items from this list and
click a cmdbutton that printspreview of reports. the list box comes from a
query named qrProd and the list box is from that same query, there is a rpt
that's also from that query. how can i have the user select multiple items
from this list and preview different report for each item. i dont know if
explained myself well, but that's the idea.
--
need help

DoCmd.OpenForm "frmMain", whereCondition :=
"ID_1=FORMS!frmMultProduction![qrUnmatchProd.ID_1]"



should do. You can skip the arguments, and the coma, if you supply the
argument name, followed by := followed by the value of the argument.
Since you use DoCmd, you can keep the FORMS!formName!ControlName inside the
'string', which has the benefit to NOT have TO supply delimiters in case of
string, or date, since you refer to the holder of the value, not the
constant itself.

I ASSUMED the control name was [qrUnmatchProd.ID_1]. Indeed, your original
message is with an excess of one ! There is one between the keyword FORMS
and the form name, and another one between the form name and the control
name. If you wish to refer to a sub-form control, the syntax is lightly
different.


Hoping it may help,
Vanderghast, Access MVP

got it...thanks a lot...can you help with this?
i have a form with a subform in it in a datasheet format. on a click event
of one of the fields, i like to open another form and i like to pass the
object that i just clicked on to that form pertaining field. can this be
posible.

something like this.
DoCmd.OpenForm frmMain, , , "[ID_1]='" &
Forms!frmMultProduction!qrUnmatchProd![ID_1] & " "

but its not doing it
--
need help

You can create a table and define an index, on one of its field, not
allowing duplicated values into it.


Hoping it may help,
Vanderghast, Access MVP


I am not sure is this is possible since i am fairly new to this. I have
a
linked table to an xl table and i was to do couple of things with it.
--One is that i have another table that i compare it to and get the
unmatch
records...it's there a way to do this without going through the whole
process
of creating the unmatch query to update the current table. the xl table
needs
to me compared at all times because it might have new records that come
from
a different program.

 

[Guest]

Hey,
for the two question i just posted this is what i've tried.
this on i just want it to populate the field on the MSM form
1. DoCmd.OpenForm "MSM", , , _
"[qrUnmatch.ID_1]=" & "'" & Me.MyList.Column(0) & "'"

this one i like to print out A report for each ID_1 that's selected from the
list.
2.Private Sub PrintMultRpt_Click()
DoCmd.OpenReport "rptProduction", acViewPreview, , "[qrProduction.ID_1]="
& "'" & Me.MyList.Column(0) & "'"
End Sub

and none are working.
--
need help

DoCmd.OpenForm "frmMain", whereCondition :=
"ID_1=FORMS!frmMultProduction![qrUnmatchProd.ID_1]"



should do. You can skip the arguments, and the coma, if you supply the
argument name, followed by := followed by the value of the argument.
Since you use DoCmd, you can keep the FORMS!formName!ControlName inside the
'string', which has the benefit to NOT have TO supply delimiters in case of
string, or date, since you refer to the holder of the value, not the
constant itself.

I ASSUMED the control name was [qrUnmatchProd.ID_1]. Indeed, your original
message is with an excess of one ! There is one between the keyword FORMS
and the form name, and another one between the form name and the control
name. If you wish to refer to a sub-form control, the syntax is lightly
different.


Hoping it may help,
Vanderghast, Access MVP

got it...thanks a lot...can you help with this?
i have a form with a subform in it in a datasheet format. on a click event
of one of the fields, i like to open another form and i like to pass the
object that i just clicked on to that form pertaining field. can this be
posible.

something like this.
DoCmd.OpenForm frmMain, , , "[ID_1]='" &
Forms!frmMultProduction!qrUnmatchProd![ID_1] & " "

but its not doing it
--
need help

You can create a table and define an index, on one of its field, not
allowing duplicated values into it.


Hoping it may help,
Vanderghast, Access MVP


I am not sure is this is possible since i am fairly new to this. I have
a
linked table to an xl table and i was to do couple of things with it.
--One is that i have another table that i compare it to and get the
unmatch
records...it's there a way to do this without going through the whole
process
of creating the unmatch query to update the current table. the xl table
needs
to me compared at all times because it might have new records that come
from
a different program.

 

[Michel Walsh]

1- To specify the rowsource of a list box, or combo box, it must be a
complete SQL statement:

Me.ComboBoxName.RowSource = "SELECT * FROM myTableName WHERE
myFieldName=FORMS!FormName!ControlName "


2- Have the list box multiple selection (Multi Select property) turned on,
to something else than none (ie, simple,or complex).

Then, you can use http://www.mvps.org/access/forms/frm0007.htm to reach
each selected items in the list, and, in the example, make an SQL statement
that limit the records to those there the field has a value among those in
the selected line in the list box.




Hoping it may help,
Vanderghast, Access MVP

Hey that works just fine...thanks a bunch....

1. now when you want to select an item from the list box, does the same
rule
apply....for example,
DoCmd.OpenForm "frmMain", whereCondition :=
"ID_1=FORMS!frmMultProduction!mylistbox"
or is it different systax

2. i know i am asking too much now, but i think you doing a great job
helping me and thanks...i have another question. i have a differetn LIST
BOX.....theList, the user wants to select MULTIPLE items from this list
and
click a cmdbutton that printspreview of reports. the list box comes from a
query named qrProd and the list box is from that same query, there is a
rpt
that's also from that query. how can i have the user select multiple items
from this list and preview different report for each item. i dont know if
explained myself well, but that's the idea.
--
need help

DoCmd.OpenForm "frmMain", whereCondition :=
"ID_1=FORMS!frmMultProduction![qrUnmatchProd.ID_1]"



should do. You can skip the arguments, and the coma, if you supply the
argument name, followed by := followed by the value of the argument.
Since you use DoCmd, you can keep the FORMS!formName!ControlName inside
the
'string', which has the benefit to NOT have TO supply delimiters in case
of
string, or date, since you refer to the holder of the value, not the
constant itself.

I ASSUMED the control name was [qrUnmatchProd.ID_1]. Indeed, your
original
message is with an excess of one ! There is one between the keyword
FORMS
and the form name, and another one between the form name and the control
name. If you wish to refer to a sub-form control, the syntax is lightly
different.


Hoping it may help,
Vanderghast, Access MVP

got it...thanks a lot...can you help with this?
i have a form with a subform in it in a datasheet format. on a click
event
of one of the fields, i like to open another form and i like to pass
the
object that i just clicked on to that form pertaining field. can this
be
posible.

something like this.
DoCmd.OpenForm frmMain, , , "[ID_1]='" &
Forms!frmMultProduction!qrUnmatchProd![ID_1] & " "

but its not doing it
--
need help


:

You can create a table and define an index, on one of its field, not
allowing duplicated values into it.


Hoping it may help,
Vanderghast, Access MVP


I am not sure is this is possible since i am fairly new to this. I
have
a
linked table to an xl table and i was to do couple of things with
it.
--One is that i have another table that i compare it to and get the
unmatch
records...it's there a way to do this without going through the
whole
process
of creating the unmatch query to update the current table. the xl
table
needs
to me compared at all times because it might have new records that
come
from
a different program.

 

[Michel Walsh]

A list box does not NECESSARY have one value, use a combo box, or collect
the selected items as illustrated in
http://www.mvps.org/access/forms/frm0007.htm.

Again, with DoCmd, it is totally useless and even error prone to error (due
to the required delimiters) to move the FORMS!FormName!ControlName outside
the string of the criteria.


Hoping it may help,
Vanderghast, Access MVP

Hey,
for the two question i just posted this is what i've tried.
this on i just want it to populate the field on the MSM form
1. DoCmd.OpenForm "MSM", , , _
"[qrUnmatch.ID_1]=" & "'" & Me.MyList.Column(0) & "'"

this one i like to print out A report for each ID_1 that's selected from
the
list.
2.Private Sub PrintMultRpt_Click()
DoCmd.OpenReport "rptProduction", acViewPreview, , "[qrProduction.ID_1]="
& "'" & Me.MyList.Column(0) & "'"
End Sub

and none are working.
--
need help

DoCmd.OpenForm "frmMain", whereCondition :=
"ID_1=FORMS!frmMultProduction![qrUnmatchProd.ID_1]"



should do. You can skip the arguments, and the coma, if you supply the
argument name, followed by := followed by the value of the argument.
Since you use DoCmd, you can keep the FORMS!formName!ControlName inside
the
'string', which has the benefit to NOT have TO supply delimiters in case
of
string, or date, since you refer to the holder of the value, not the
constant itself.

I ASSUMED the control name was [qrUnmatchProd.ID_1]. Indeed, your
original
message is with an excess of one ! There is one between the keyword
FORMS
and the form name, and another one between the form name and the control
name. If you wish to refer to a sub-form control, the syntax is lightly
different.


Hoping it may help,
Vanderghast, Access MVP

got it...thanks a lot...can you help with this?
i have a form with a subform in it in a datasheet format. on a click
event
of one of the fields, i like to open another form and i like to pass
the
object that i just clicked on to that form pertaining field. can this
be
posible.

something like this.
DoCmd.OpenForm frmMain, , , "[ID_1]='" &
Forms!frmMultProduction!qrUnmatchProd![ID_1] & " "

but its not doing it
--
need help


:

You can create a table and define an index, on one of its field, not
allowing duplicated values into it.


Hoping it may help,
Vanderghast, Access MVP


I am not sure is this is possible since i am fairly new to this. I
have
a
linked table to an xl table and i was to do couple of things with
it.
--One is that i have another table that i compare it to and get the
unmatch
records...it's there a way to do this without going through the
whole
process
of creating the unmatch query to update the current table. the xl
table
needs
to me compared at all times because it might have new records that
come
from
a different program.

 

[Guest]

you have been helpful Walsh...thanks
i might just need to take Programming classes cause this is becoming
complicated for me. i started doing this couple of weeks ago for the first
time and now i am stuck.
this is what i came up with for the population to other form

sub add_click
Me.MyList.RowSource = "SELECT * FROM qrUnMatch WHERE ID_1 =
forms!MSM!ModelNumber"
end sub
when i do that, it gives me an error to what is forms!MSM!ModelNumber
how can i incorporate that to the docmd.
'DoCmd.OpenForm "MSM", , ,

on the other hand, the multiselect list doesnt work either. let me tell you
that i am very new to this and man, i am having difficulties.

this is what i came up with for that and i still do get an error. i want to
incorporate both list selection to docmds. in this case to open multiple
reports.
Private Sub PrintMultRpt_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Me.Form
Set ctl = Me.sched
strSQL = "Select * from qrProduction where [ID_1]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [ID_1]="
Next varItem
'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 12)
DoCmd.OpenReport "rptProduction", acViewPreview, , WhereCondition:=strSQL
End Sub
--
need help

A list box does not NECESSARY have one value, use a combo box, or collect
the selected items as illustrated in
http://www.mvps.org/access/forms/frm0007.htm.

Again, with DoCmd, it is totally useless and even error prone to error (due
to the required delimiters) to move the FORMS!FormName!ControlName outside
the string of the criteria.


Hoping it may help,
Vanderghast, Access MVP

Hey,
for the two question i just posted this is what i've tried.
this on i just want it to populate the field on the MSM form
1. DoCmd.OpenForm "MSM", , , _
"[qrUnmatch.ID_1]=" & "'" & Me.MyList.Column(0) & "'"

this one i like to print out A report for each ID_1 that's selected from
the
list.
2.Private Sub PrintMultRpt_Click()
DoCmd.OpenReport "rptProduction", acViewPreview, , "[qrProduction.ID_1]="
& "'" & Me.MyList.Column(0) & "'"
End Sub

and none are working.
--
need help

DoCmd.OpenForm "frmMain", whereCondition :=
"ID_1=FORMS!frmMultProduction![qrUnmatchProd.ID_1]"



should do. You can skip the arguments, and the coma, if you supply the
argument name, followed by := followed by the value of the argument.
Since you use DoCmd, you can keep the FORMS!formName!ControlName inside
the
'string', which has the benefit to NOT have TO supply delimiters in case
of
string, or date, since you refer to the holder of the value, not the
constant itself.

I ASSUMED the control name was [qrUnmatchProd.ID_1]. Indeed, your
original
message is with an excess of one ! There is one between the keyword
FORMS
and the form name, and another one between the form name and the control
name. If you wish to refer to a sub-form control, the syntax is lightly
different.


Hoping it may help,
Vanderghast, Access MVP


got it...thanks a lot...can you help with this?
i have a form with a subform in it in a datasheet format. on a click
event
of one of the fields, i like to open another form and i like to pass
the
object that i just clicked on to that form pertaining field. can this
be
posible.

something like this.
DoCmd.OpenForm frmMain, , , "[ID_1]='" &
Forms!frmMultProduction!qrUnmatchProd![ID_1] & " "

but its not doing it
--
need help


:

You can create a table and define an index, on one of its field, not
allowing duplicated values into it.


Hoping it may help,
Vanderghast, Access MVP


I am not sure is this is possible since i am fairly new to this. I
have
a
linked table to an xl table and i was to do couple of things with
it.
--One is that i have another table that i compare it to and get the
unmatch
records...it's there a way to do this without going through the
whole
process
of creating the unmatch query to update the current table. the xl
table
needs
to me compared at all times because it might have new records that
come
from
a different program.

 

[Michel Walsh]

Clearly, the form you referred to, through the syntax
FORMS!formName!ControlName must be open, and the control, be filled with
the right data.


The line of code:

Me.MyList.RowSource = "SELECT ... "


is intended to supply the data to the list of the list box control, and
nothing more. It cannot be used as criteria, it is a command to retrieve
data (SELECT).



From your code, if you want to build a criteria, then, it is probably:


Private Sub PrintMultRpt_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Me.Form
Set ctl = Me.sched

strSQL = "[ID_1]=" ' <<<<--------------------- here

'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [ID_1]="
Next varItem
'Trim the end of strSQL

strSQL = Left$(strSQL, Len(strSQL) - 11) ' <<<<------------ and here

DoCmd.OpenReport "rptProduction", acViewPreview, , WhereCondition:=strSQL
End Sub




since we only want to built the WHERE clause, and only it, without the word
WHERE. Indeed, we send this value as the WhereCondition argument, so the
word WHERE is not required. And since it is ONLY a criteria, the SELECT ...
FROM ... parts are not required either, when we open a form in this manner.





Hoping it may help,
Vanderghast, Access MVP

you have been helpful Walsh...thanks
i might just need to take Programming classes cause this is becoming
complicated for me. i started doing this couple of weeks ago for the first
time and now i am stuck.
this is what i came up with for the population to other form

sub add_click
Me.MyList.RowSource = "SELECT * FROM qrUnMatch WHERE ID_1 =
forms!MSM!ModelNumber"
end sub
when i do that, it gives me an error to what is forms!MSM!ModelNumber
how can i incorporate that to the docmd.
'DoCmd.OpenForm "MSM", , ,

on the other hand, the multiselect list doesnt work either. let me tell
you
that i am very new to this and man, i am having difficulties.

this is what i came up with for that and i still do get an error. i want
to
incorporate both list selection to docmds. in this case to open multiple
reports.
Private Sub PrintMultRpt_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Me.Form
Set ctl = Me.sched
strSQL = "Select * from qrProduction where [ID_1]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [ID_1]="
Next varItem
'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 12)
DoCmd.OpenReport "rptProduction", acViewPreview, , WhereCondition:=strSQL
End Sub
--
need help

A list box does not NECESSARY have one value, use a combo box, or collect
the selected items as illustrated in
http://www.mvps.org/access/forms/frm0007.htm.

Again, with DoCmd, it is totally useless and even error prone to error
(due
to the required delimiters) to move the FORMS!FormName!ControlName
outside
the string of the criteria.


Hoping it may help,
Vanderghast, Access MVP

Hey,
for the two question i just posted this is what i've tried.
this on i just want it to populate the field on the MSM form
1. DoCmd.OpenForm "MSM", , , _
"[qrUnmatch.ID_1]=" & "'" & Me.MyList.Column(0) & "'"

this one i like to print out A report for each ID_1 that's selected
from
the
list.
2.Private Sub PrintMultRpt_Click()
DoCmd.OpenReport "rptProduction", acViewPreview, ,
"[qrProduction.ID_1]="
& "'" & Me.MyList.Column(0) & "'"
End Sub

and none are working.
--
need help


:


DoCmd.OpenForm "frmMain", whereCondition :=
"ID_1=FORMS!frmMultProduction![qrUnmatchProd.ID_1]"



should do. You can skip the arguments, and the coma, if you supply the
argument name, followed by := followed by the value of the
argument.
Since you use DoCmd, you can keep the FORMS!formName!ControlName
inside
the
'string', which has the benefit to NOT have TO supply delimiters in
case
of
string, or date, since you refer to the holder of the value, not the
constant itself.

I ASSUMED the control name was [qrUnmatchProd.ID_1]. Indeed, your
original
message is with an excess of one ! There is one between the keyword
FORMS
and the form name, and another one between the form name and the
control
name. If you wish to refer to a sub-form control, the syntax is
lightly
different.


Hoping it may help,
Vanderghast, Access MVP


got it...thanks a lot...can you help with this?
i have a form with a subform in it in a datasheet format. on a click
event
of one of the fields, i like to open another form and i like to pass
the
object that i just clicked on to that form pertaining field. can
this
be
posible.

something like this.
DoCmd.OpenForm frmMain, , , "[ID_1]='" &
Forms!frmMultProduction!qrUnmatchProd![ID_1] & " "

but its not doing it
--
need help


:

You can create a table and define an index, on one of its field,
not
allowing duplicated values into it.


Hoping it may help,
Vanderghast, Access MVP


I am not sure is this is possible since i am fairly new to this. I
have
a
linked table to an xl table and i was to do couple of things with
it.
--One is that i have another table that i compare it to and get
the
unmatch
records...it's there a way to do this without going through the
whole
process
of creating the unmatch query to update the current table. the xl
table
needs
to me compared at all times because it might have new records
that
come
from
a different program.

 

[Guest]

code you edited, gives me an error, but i found this somewhere and it works
fine, thanks for your help...........you the best
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "rptProduction"

'Loop through the ItemsSelected in the list box.
With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ID_1] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
strDescrip = "Production: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

--
need help

Clearly, the form you referred to, through the syntax
FORMS!formName!ControlName must be open, and the control, be filled with
the right data.


The line of code:

Me.MyList.RowSource = "SELECT ... "


is intended to supply the data to the list of the list box control, and
nothing more. It cannot be used as criteria, it is a command to retrieve
data (SELECT).



From your code, if you want to build a criteria, then, it is probably:


Private Sub PrintMultRpt_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Me.Form
Set ctl = Me.sched

strSQL = "[ID_1]=" ' <<<<--------------------- here

'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [ID_1]="
Next varItem
'Trim the end of strSQL

strSQL = Left$(strSQL, Len(strSQL) - 11) ' <<<<------------ and here

DoCmd.OpenReport "rptProduction", acViewPreview, , WhereCondition:=strSQL
End Sub




since we only want to built the WHERE clause, and only it, without the word
WHERE. Indeed, we send this value as the WhereCondition argument, so the
word WHERE is not required. And since it is ONLY a criteria, the SELECT ...
FROM ... parts are not required either, when we open a form in this manner.





Hoping it may help,
Vanderghast, Access MVP

you have been helpful Walsh...thanks
i might just need to take Programming classes cause this is becoming
complicated for me. i started doing this couple of weeks ago for the first
time and now i am stuck.
this is what i came up with for the population to other form

sub add_click
Me.MyList.RowSource = "SELECT * FROM qrUnMatch WHERE ID_1 =
forms!MSM!ModelNumber"
end sub
when i do that, it gives me an error to what is forms!MSM!ModelNumber
how can i incorporate that to the docmd.
'DoCmd.OpenForm "MSM", , ,

on the other hand, the multiselect list doesnt work either. let me tell
you
that i am very new to this and man, i am having difficulties.

this is what i came up with for that and i still do get an error. i want
to
incorporate both list selection to docmds. in this case to open multiple
reports.
Private Sub PrintMultRpt_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Me.Form
Set ctl = Me.sched
strSQL = "Select * from qrProduction where [ID_1]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [ID_1]="
Next varItem
'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 12)
DoCmd.OpenReport "rptProduction", acViewPreview, , WhereCondition:=strSQL
End Sub
--
need help

A list box does not NECESSARY have one value, use a combo box, or collect
the selected items as illustrated in
http://www.mvps.org/access/forms/frm0007.htm.

Again, with DoCmd, it is totally useless and even error prone to error
(due
to the required delimiters) to move the FORMS!FormName!ControlName
outside
the string of the criteria.


Hoping it may help,
Vanderghast, Access MVP


Hey,
for the two question i just posted this is what i've tried.
this on i just want it to populate the field on the MSM form
1. DoCmd.OpenForm "MSM", , , _
"[qrUnmatch.ID_1]=" & "'" & Me.MyList.Column(0) & "'"

this one i like to print out A report for each ID_1 that's selected
from
the
list.
2.Private Sub PrintMultRpt_Click()
DoCmd.OpenReport "rptProduction", acViewPreview, ,
"[qrProduction.ID_1]="
& "'" & Me.MyList.Column(0) & "'"
End Sub

and none are working.
--
need help


:


DoCmd.OpenForm "frmMain", whereCondition :=
"ID_1=FORMS!frmMultProduction![qrUnmatchProd.ID_1]"



should do. You can skip the arguments, and the coma, if you supply the
argument name, followed by := followed by the value of the
argument.
Since you use DoCmd, you can keep the FORMS!formName!ControlName
inside
the
'string', which has the benefit to NOT have TO supply delimiters in
case
of
string, or date, since you refer to the holder of the value, not the
constant itself.

I ASSUMED the control name was [qrUnmatchProd.ID_1]. Indeed, your
original
message is with an excess of one ! There is one between the keyword
FORMS
and the form name, and another one between the form name and the
control
name. If you wish to refer to a sub-form control, the syntax is
lightly
different.


Hoping it may help,
Vanderghast, Access MVP


got it...thanks a lot...can you help with this?
i have a form with a subform in it in a datasheet format. on a click
event
of one of the fields, i like to open another form and i like to pass
the
object that i just clicked on to that form pertaining field. can
this
be
posible.

something like this.
DoCmd.OpenForm frmMain, , , "[ID_1]='" &
Forms!frmMultProduction!qrUnmatchProd![ID_1] & " "

but its not doing it
--
need help


:

You can create a table and define an index, on one of its field,
not
allowing duplicated values into it.


Hoping it may help,
Vanderghast, Access MVP


I am not sure is this is possible since i am fairly new to this. I
have
a
linked table to an xl table and i was to do couple of things with
it.
--One is that i have another table that i compare it to and get
the
unmatch
records...it's there a way to do this without going through the
whole
process
of creating the unmatch query to update the current table. the xl
table
needs
to me compared at all times because it might have new records
that
come
from
a different program.