where condition in vba

Dear all, i'm trying to set up a mail merge that is executed from a form in access 2000.i have a checkbox on a form which avtivates a macro and launches the merge using the code below, i copied from microsoft. This works fine, only ALL my records from the database merge to word instead of only ONE.
basically i want to add this where condition to visual basic:
[project_number]=[Forms]![Transport]![project_number] And [shipment_number]=[Forms]![Transport]![shipment_number]
anybody knows?????

Function MergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("C:\Documents and Settings\Randall\My Documents\HouZi5\HouZi5_DPBank.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the Northwind database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\Randall\My Documents\HouZi5\HouZi5.mdb", _
LinkToSource:=True, _
Connection:="QUERY BankDoc", _
SQLStatement:="SELECT * FROM [BankDoc]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function

Hi Randall,

Try this and see if it works

SQLStatement:="SELECT * FROM [BankDoc] WHERE [project_number]=[Forms]![Transport]![project_number] And [shipment_number]=[Forms]![Transport]![shipment_number]"

lwells

thnx! but it doesn't seem to work,

i can't believe that it would not be possible, so if anybody has another idea,
please let me know.

Hi Randall,

Is [BankDoc] a query from one table or two. If [BankDoc] is a query from two tables, then this might be what you need:

Designate which tables the ID's are coming from:

SQLStatement:="SELECT [tableName1].*, [tableName2].* FROM [tableName1] INNER JOIN [tableName2] ON [tableName1].ID = [tableName2].ID WHERE ((([tableName1].[project_number])=[Forms]![Transport]![project_number]) And (([tableName2].[shipment_number])=[Forms]![Transport]![shipment_number]))"

The above example used the ID of each table as the joining fields with a one to one relationship. Just edit accordingly if your data is from two tables.

Another possibility if only one table is involved:

Dim strWhere As String
strWhere = " [project_number]= " & [Forms]![Transport]![project_number]
strWhere = strWhere & " And [shipment_number]= " & [Forms]![Transport]![shipment_number]
strWhere = strWhere

SQLStatement:="SELECT * FROM [tableName] WHERE strWhere"

Just make a note of the spaces between the quotation marks. This also assumes that the [Forms]![Transport]![shipment_number] and [Forms]![Transport]![project_number] are numbers and not text values. Otherwise you will need to include some additional quotes.

strWhere = " [project_number]= '" & [Forms]![Transport]![project_number] & "'"
strWhere = strWhere & " And [shipment_number]= '" & [Forms]![Transport]![shipment_number] & "'"

See if this helps,
lwells

Thanx again,
but i still can't get it to work, indeed the fields in the query come from several tables(3), but the project and shipment number are both from 1 table.
SELECT Transport.project_number, Transport.shipment_number, Transport.loading_port, Transport.bl_number, Transport.voyage, Transport.invoice_number, Transport.vessel_name, Transport.ETD, Transport.ETA, Transport.insurance_number, Sales_Contracts.contract_number, Sales_Contracts.shipping_terms, Sales_Contracts.commodity, Sales_Contracts.unit_price_china, Sales_Contracts.country_of_orign, Sales_Contracts.packaging_shipping_marks, Sales_Contracts.Packing, Sales_Contracts.destination, Sales_Contracts.Consignee, Sales_Contracts.nepa_number, [Packing_List Query].CountOfcontainer, [Packing_List Query].[Sum Of bales], [Packing_List Query].[Sum Of nett_weight], [Packing_List Query].[Sum Of gross_weight], Transport.type_of_container
FROM [Packing_List Query] INNER JOIN (Sales_Contracts INNER JOIN Transport ON Sales_Contracts.project_number = Transport.project_number) ON ([Packing_List Query].project_number = Transport.project_number) AND ([Packing_List Query].shipment_number = Transport.shipment_number);
tried to implement your string, with the 1st one i got the message that the string was too long, more than 255.(anyway to go around this?), and the other one i tried as well, probably i'm doing something wrong here, for this being my first with visual basic. my database is almost finished, but till so far i could manage everything with macro's.
utterly frustrated!!

randall.

Hi Randall,

Post the sql from your query [Packing_List Query]. This is where the issue is as to why the above code I gave you didn't work. It will be a pretty simple correction after that.
lwells

this one you mean?

SELECT DISTINCTROW Packing_List.project_number, Packing_List.shipment_number, Count(Packing_List.container) AS CountOfcontainer, Sum(Packing_List.bales) AS [Sum Of bales], Sum(Packing_List.nett_weight) AS [Sum Of nett_weight], Sum(Packing_List.gross_weight) AS [Sum Of gross_weight]
FROM Packing_List
GROUP BY Packing_List.project_number, Packing_List.shipment_number;

Randell,

Try this in your sql for the [BankDoc] and see if it displays the data correctly.

SELECT Transport.project_number, Transport.shipment_number, Transport.loading_port, Transport.bl_number, Transport.voyage, Transport.invoice_number, Transport.vessel_name, Transport.ETD, Transport.ETA, Transport.insurance_number, Sales_Contracts.contract_number, Sales_Contracts.shipping_terms, Sales_Contracts.commodity, Sales_Contracts.unit_price_china, Sales_Contracts.country_of_orign, Sales_Contracts.packaging_shipping_marks, Sales_Contracts.Packing, Sales_Contracts.destination, Sales_Contracts.Consignee, Sales_Contracts.nepa_number, [Packing_List Query].CountOfcontainer, [Packing_List Query].[Sum Of bales], [Packing_List Query].[Sum Of nett_weight], [Packing_List Query].[Sum Of gross_weight], Transport.type_of_container
FROM Sales_Contracts INNER JOIN ([Packing_List Query] INNER JOIN Transport ON ([Packing_List Query].project_number = Transport.project_number) AND ([Packing_List Query].shipment_number = Transport.shipment_number)) ON Sales_Contracts.contract_number = Transport.project_number;


lwells

hi iwells,

don't have a qlue what you did exactly, it seems to basically work for some records, only some records are left out for some reason when i put it like this.

when i change the last part it does work
( ON Sales_Contracts.project_number = Transport.project_number; )
instead of
( ON Sales_Contracts.contract_number = Transport.project_number; )

Hi Randall

The problem was how the tables were joined initially. The sql that you posted created ambiguous joins (which was the reason to see how the packing list query was joined). So I selected the contract_number to project_number as the join just to see if we were getting any closer to what you needed. Just move the joins around until you can query the exact data you need.

lwells

Hi Iwels,
have the bankdoc like this below now, and this works. only still figuring out how to make the script work, so it will only merge the record selected on the form.
SELECT [Transport].[project_number], [Transport].[shipment_number], [Transport].[loading_port], [Transport].[bl_number], [Transport].[voyage], [Transport].[invoice_number], [Transport].[vessel_name], [Transport].[ETD], [Transport].[ETA], [Transport].[insurance_number], [Sales_Contracts].[contract_number], [Sales_Contracts].[shipping_terms], [Sales_Contracts].[commodity], [Sales_Contracts].[unit_price_china], [Sales_Contracts].[country_of_orign], [Sales_Contracts].[packaging_shipping_marks], [Sales_Contracts].[Packing], [Sales_Contracts].[destination], [Sales_Contracts].[Consignee], [Sales_Contracts].[nepa_number], [Packing_List Query].[CountOfcontainer], [Packing_List Query].[Sum Of bales], [Packing_List Query].[Sum Of nett_weight], [Packing_List Query].[Sum Of gross_weight], [Transport].[type_of_container]
FROM Sales_Contracts INNER JOIN ([Packing_List Query] INNER JOIN Transport ON ([Packing_List Query].[project_number]=[Transport].[project_number]) AND ([Packing_List Query].[shipment_number]=[Transport].[shipment_number])) ON [Sales_Contracts].[project_number]=[Transport].[project_number];

Randall,

Work with this type of syntax or very close

SQLStatement:="SELECT * FROM [BankDoc] WHERE [Transport].[project_number]=[Forms]![Transport]![project_number] And [Transport].[shipment_number]=[Forms]![Transport]![shipment_number]"

Basically call out which table has the [project_number] and [shipment_number]that you want to sort by in your query. Another way to do this, is to reopen your query in design and place the control name from your form in the criteria below the field you want filtered. Then open your form select the project and shipping numbers and while the form is still open...open the query and see if you got the records you wanted. You can save the query and then try your mail merge from your form without the WHERE syntax.
lwells

hi Iwells,

tried to do it in the query as well, and this works initiallly, it shows only the record which i have opened on the form. when i publish to word or excell it works, but it doesn't merge!! it asks to select a table from a list where all the queries and tables are listed, except the bankdoc!!

don't know if you have another idea left, but i am ready to admit by now that i'm starting to get pretty deperate!!

Randall,

This isn't a solution, but if you make a table from your query, will it mail merge when you select the table that you created?

Still looking for a solution,
lwells

hi iwells,

i've been away for a few days, spending some time on other things luckily!
but no, when i make a table out of the query, it doesn't merge at all.

Hi Randall,

When you made the new table did you change the syntax accordingly in your mail merge code

LinkToSource:=True, _
Connection:="Table NameOfTable", _
SQLStatement:="SELECT * FROM [NameOfTable]"

lwells

yes!!