form with Listbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Will G - 21 Mar 2007 22:55 GMT

can anyone help me figure why i am not getting the result that i am looking
for....

i have a listbox mylist, and i want the user to select an item from the list
and than click to open another form that should populate, ID_1 which is
String and other fields pertaining to that ID_1 on the form.
i have this on a cmdbutton_click
DoCmd.OpenForm "MSM", , , "[qrUnMatch].[ID_1]=" & "'" & Me.MyList.Column(0)
& "'"
but seems to be wrong somehow....

the list(mylist) comes from a Query named : qrUnmatch..this query is a query
that finds the Unmatching records between two tables...and the form MSM has a
differnt row source and it's tblMSM.....WOULD THAT BE WHY I DONT GET THE
POPULATION THAT I WANT? OR DO I HAVE WRONG SYNTAX?

Signature

need help

Reply to this Message

strive4peace - 22 Mar 2007 04:13 GMT

Hi Will,

unless ID_1 is on the form from 2 different recordsets, you do not
preface it with [qrUnMatch] ... turn on the fieldlist in the design view
of the MSM form and look to see how it is listed

from the menu --> View, Fieldlist

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

> can anyone help me figure why i am not getting the result that i am looking
> for....
[quoted text clipped - 11 lines]
> differnt row source and it's tblMSM.....WOULD THAT BE WHY I DONT GET THE
> POPULATION THAT I WANT? OR DO I HAVE WRONG SYNTAX?

Reply to this Message

Will G - 22 Mar 2007 06:19 GMT

Thanks for that, i think that's where the problem is. the field that i am
sending over does not exist in the form MSM. Now, can you help me figure how
to do this. i have two tables, tblMSM and tblImport, these two table have the
same type of information only that one of them is being imported from a
different location. Fields Model Number and Description are the same type.
However, tblMSM have other foreign keys from other tables. when i bring in
the table, i compare both, and find the unmatching Model Numbeer and Desc
using a unmatch query. the Model Number and Desc that does not match, are the
ones that are in Mylist and this is viewed in form A. now form B contains,
data from tblMSM. example....
tblMSM
Fields: ModelNumber, Desc, SpID, FmID, ....etc......

tblImport
Fields: ModelNumber, Desc

qrUnmatch
finds the ModelNumber and Desc that is not in Both

form A:
myList = qrUnmatch

form B:
ModelNumber, Desc, Sp, Fm

action to be taken: User clicks an item from myList,
populate form B with ModelNumber and Desc that comes from mylist(qrUnMatch)
and user selects Sp, and Fm for that ModelNumber

note: ModelNumber in qrUnMatch is called ID and tblMSM is called ModelNumber
is that part of the problem???? and can you help me with this......

Signature

need help

> Hi Will,
>
[quoted text clipped - 29 lines]
> > differnt row source and it's tblMSM.....WOULD THAT BE WHY I DONT GET THE
> > POPULATION THAT I WANT? OR DO I HAVE WRONG SYNTAX?

Reply to this Message

strive4peace - 22 Mar 2007 18:13 GMT

Hi Will,

in order to help you,we need to know your data structure. Here is
something you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~

also, please tell us how the relevant tables are related

'~~~~~~~~~~~~~~~~~~

also, what is the RecordSource for the MSM form and the first form?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

> Thanks for that, i think that's where the problem is. the field that i am
> sending over does not exist in the form MSM. Now, can you help me figure how
[quoted text clipped - 27 lines]
> note: ModelNumber in qrUnMatch is called ID and tblMSM is called ModelNumber
> is that part of the problem???? and can you help me with this......

Reply to this Message

Will G - 22 Mar 2007 18:46 GMT

Hello, Thanks, i just learned couple of important things
here is the copied doc
Record Source for MSM form is MSM table
Record Source for frmMultProduction is: there is not record source

frmMultProduction contains only mylist that has a record source: query
qrUnmatch
will these help, i hope, thanks again.
Config
==========================
0 ConfigID, 4 (Long), 4
1 Config, 10 (Text), 50

FoamCore
==========================
0 FoamCoreID, 4 (Long), 4
1 FoamCore, 10 (Text), 50
2 Cost, 5 (Currency), 8
3 VendorID, 4 (Long), 4
4 QuantityInStock, 4 (Long), 4

FoamEnc
==========================
0 FoamEncID, 4 (Long), 4
1 FoanEnc, 10 (Text), 50
2 Cost, 5 (Currency), 8
3 VendorID, 4 (Long), 4
4 QuantityInStock, 4 (Long), 4

Items
==========================
0 ID, 4 (Long), 4
1 ItemName, 10 (Text), 50

Label
==========================
0 LabelID, 4 (Long), 4
1 Label, 10 (Text), 50
2 Description, 10 (Text), 50

LabelDesc
==========================
0 LabelDescID, 4 (Long), 4
1 LabelDesc, 10 (Text), 255

LinkedSalesOrder
==========================
0 OrderNo, 7 (Double), 8
1 ID, 10 (Text), 255
2 RequiredDate, 8 (Date), 8
3 ID_1, 10 (Text), 255
4 Description, 10 (Text), 255
5 Comment1, 10 (Text), 255
6 Comment2, 10 (Text), 255
7 OrderQty, 7 (Double), 8
8 Type, 7 (Double), 8

MSM
==========================
0 MSMID, 4 (Long), 4
1 ModelNumber, 10 (Text), 255
2 Desc1, 10 (Text), 255
3 Desc2, 10 (Text), 255
4 SpringID, 3 (Integer), 2
5 LabelID, 3 (Integer), 2
6 SizeID, 3 (Integer), 2
7 NeedleID, 3 (Integer), 2
8 PatternID, 3 (Integer), 2
9 ConfigID, 3 (Integer), 2
10 BorderID, 3 (Integer), 2
11 Produce, 1 (Boolean), 1
12 BoxfileID, 4 (Long), 4
13 BoxtypeID, 4 (Long), 4
14 FoamCoreID, 4 (Long), 4
15 FoamEncID, 4 (Long), 4

MSMBfill
==========================
0 BedBfillID, 4 (Long), 4
1 MSMID, 3 (Integer), 2
2 BfillID, 3 (Integer), 2

MSMPtFill
==========================
0 BedPtFillID, 4 (Long), 4
1 MSMID, 3 (Integer), 2
2 PtfillID, 3 (Integer), 2

MSMQfill
==========================
0 BedQfillID, 4 (Long), 4
1 MSMID, 3 (Integer), 2
2 QfillID, 3 (Integer), 2

MSMUphFill
==========================
0 BedUphFillID, 4 (Long), 4
1 MSMID, 3 (Integer), 2
2 UphfillID, 3 (Integer), 2

Needle
==========================
0 NeedleID, 4 (Long), 4
1 Needle, 10 (Text), 50

Pattern
==========================
0 PatternID, 4 (Long), 4
1 Pattern, 10 (Text), 255

PO
==========================
0 POID, 4 (Long), 4
1 PODescription, 10 (Text), 255
2 VendorID, 3 (Integer), 2
3 OrderDate, 8 (Date), 8
4 PONumber, 3 (Integer), 2

PtFill
==========================
0 PtFillID, 4 (Long), 4
1 PtFill, 10 (Text), 50
2 PtFillCost, 5 (Currency), 8
3 VendorID, 4 (Long), 4
4 QuantityInStock, 4 (Long), 4

Qfill
==========================
0 QfillID, 4 (Long), 4
1 Qfill, 10 (Text), 50
2 QfillCost, 5 (Currency), 8
3 VendorID, 4 (Long), 4
4 QuantityInStock, 4 (Long), 4

SalesOrder
==========================
0 OrderNoID, 4 (Long), 4
1 OrderNo, 7 (Double), 8
2 CustomerID, 10 (Text), 255
3 RequiredDate, 8 (Date), 8
4 ProductID, 10 (Text), 255
5 Description, 10 (Text), 255
6 Comment1, 10 (Text), 255
7 Comment2, 10 (Text), 255
8 OrderQty, 7 (Double), 8

SalesOrder1
==========================
0 OrderNo, 7 (Double), 8
1 ID, 10 (Text), 255
2 RequiredDate, 8 (Date), 8
3 ID_1, 10 (Text), 255
4 Description, 10 (Text), 255
5 Comment1, 10 (Text), 255
6 Comment2, 10 (Text), 255
7 OrderQty, 7 (Double), 8
8 Type, 4 (Long), 4

Size
==========================
0 SizeID, 4 (Long), 4
1 Size, 10 (Text), 50
2 Desc, 10 (Text), 255

Spring
==========================
0 SpringID, 4 (Long), 4
1 Spring, 10 (Text), 50
2 SpringCost, 5 (Currency), 8
3 VendorID, 4 (Long), 4
4 QuantityInStock, 4 (Long), 4

Switchboard Items
==========================
0 SwitchboardID, 4 (Long), 4
1 ItemNumber, 3 (Integer), 2
2 ItemText, 10 (Text), 255
3 Command, 3 (Integer), 2
4 Argument, 10 (Text), 255

tblScheduleAscDate
==========================
0 ImportDate, 10 (Text), 50

UphFill
==========================
0 UphFillID, 4 (Long), 4
1 UphFill, 10 (Text), 50
2 UphFillCost, 5 (Currency), 8
3 VendorID, 4 (Long), 4
4 QuantityInStock, 4 (Long), 4

Vendor
==========================
0 VendorID, 4 (Long), 4
2 Vendor, 10 (Text), 50
3 VendorContact, 10 (Text), 50
4 ContactTitle, 10 (Text), 50
5 Address, 10 (Text), 50
6 City, 10 (Text), 50
7 State, 10 (Text), 50
8 Zip, 10 (Text), 50
9 Email, 10 (Text), 50
10 Phone, 10 (Text), 50
11 Fax, 10 (Text), 50
12 Note, 10 (Text), 50

Signature

need help

> Hi Will,
>
[quoted text clipped - 134 lines]
> > note: ModelNumber in qrUnMatch is called ID and tblMSM is called ModelNumber
> > is that part of the problem???? and can you help me with this......

Reply to this Message

strive4peace - 23 Mar 2007 05:29 GMT

Hi Will,

yes, this helps... now we need to know the SQL for qrUnmatch

1. go to design view of query
2. from menu --> View, SQL
3. copy it and paste into message

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

> Hello, Thanks, i just learned couple of important things
> here is the copied doc
[quoted text clipped - 202 lines]
> 11 Fax, 10 (Text), 50
> 12 Note, 10 (Text), 50

Reply to this Message

Will G - 23 Mar 2007 16:20 GMT

SELECT LinkedSalesOrder.ID_1, LinkedSalesOrder.Description,
LinkedSalesOrder.Comment1 AS Comment, LinkedSalesOrder.Type
FROM LinkedSalesOrder LEFT JOIN MSM ON LinkedSalesOrder.ID_1 = MSM.ModelNumber
WHERE (((LinkedSalesOrder.Type)=6) AND ((MSM.ModelNumber) Is Null));

Signature

need help

> Hi Will,
>
[quoted text clipped - 220 lines]
> > 11 Fax, 10 (Text), 50
> > 12 Note, 10 (Text), 50

Reply to this Message

strive4peace - 25 Mar 2007 01:41 GMT

Hi Will,

"the field that i am sending over does not exist in the form MSM"

since ID_1 is IN the Recordset for the form... put on the form. It can
have the Visible property set to No so it does not show and you can, of
course, delete the corresponding label.

Even though ID_1 is linked to ModelNumber, you are using a LEFT JOIN, so
ID_1 is what is controlling

You will only be able to use this form to display data ... if you want
to add records, you will need to use code behind the form triggered by a
checkbox click or something else to add a record using, for instance, SQL

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

> SELECT LinkedSalesOrder.ID_1, LinkedSalesOrder.Description,
> LinkedSalesOrder.Comment1 AS Comment, LinkedSalesOrder.Type
> FROM LinkedSalesOrder LEFT JOIN MSM ON LinkedSalesOrder.ID_1 = MSM.ModelNumber
> WHERE (((LinkedSalesOrder.Type)=6) AND ((MSM.ModelNumber) Is Null));

Reply to this Message