If Statement with MsgBox

  • Thread starter Thread starter 4charity
  • Start date Start date
4

4charity

Hi. I am fairly new to using VBA in Access and need some help with getting
the syntax and order of this correct.

I have a Function that creates a MailMerge in Word.
It selects data from a query in Access.

It prompts for the user to enter the VendorInvoiceNumber.
I want it to return a Message, if the VendorInvoiceNumber is not valid (does
not exist in the underlying table.)

This is what I have. The 'Prompt with incorrect VendorID is the part thats
not working.

Function MergeIt()
strVendorId = InputBox(Prompt:="Enter the desired Vendor Invoice Number .", _
Title:="ENTER VIN", Default:="")

If strVendorId = vbNullString Then
MsgBox "You have to enter something"
Else
Dim objWord As Word.Document
Set objWord = GetObject("C:\Komar\testletter2.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Komar\DTD.mdb", _
LinkToSource:=True, _
Connection:="QUERY _reportquery", _
SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '"
& strVendorId & "'"

' Prompt with incorrect VendorID
If VendorInvoiceNumber = vbNullString Then
MsgBox "This is not a valid Vendor Invoice Number"
Else

'"
' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
End If
End If
End Function

Thanks in advance for help on this.
 
Rather than use an input box to get the vendorID, I would put this on a form
as a combo box or listbox, and let the user select from a list. This
guarantees that the VendorID would be valid. You might even go so far as to
only display those vendors that have open invoices and where the
"LastInvoiceSent" field is more than two weeks old.

In your SQL statement, you have a where clause that is comparing an Invoice
Number with a VendorID; that doesn't make any sense to me. Additionally, you
are calling these ID values (which usually implies that they are numeric),
but are using treating them as though they are strings. Maybe you want:

"... WHERE [VendorID] = " & intVendorID

But if a vendor has multiple invoices, this criteria would return multiple
records for that vendor, so you might need to be more restrictive by
including an additional part to the criteria that only selects those records
where the invoice has not been paid.
 
Hi Dale,

Thanks for the quick response. I am out of the office the rest of the day,
so will not get a chance to try anything till tomorrow morning.

The Vendor Invoice Number is indeed a text string. This value is assigned by
each vendor, on their invoice, and can vary from only integers to a
combination of integers and text.

My original plan was to use a combo box, which I had set up, but could not
get the correct syntax for the VendorInvoiceNumber field. This is what I had:

SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '" &
[Forms]![frmLetters]![Me.cboSelectName.Column(1)] & "'"

Would you know the correct syntax for that? It would, as you said, make it
much easier.

Thanks.

Dale Fye said:
Rather than use an input box to get the vendorID, I would put this on a form
as a combo box or listbox, and let the user select from a list. This
guarantees that the VendorID would be valid. You might even go so far as to
only display those vendors that have open invoices and where the
"LastInvoiceSent" field is more than two weeks old.

In your SQL statement, you have a where clause that is comparing an Invoice
Number with a VendorID; that doesn't make any sense to me. Additionally, you
are calling these ID values (which usually implies that they are numeric),
but are using treating them as though they are strings. Maybe you want:

"... WHERE [VendorID] = " & intVendorID

But if a vendor has multiple invoices, this criteria would return multiple
records for that vendor, so you might need to be more restrictive by
including an additional part to the criteria that only selects those records
where the invoice has not been paid.

----
HTH
Dale



4charity said:
Hi. I am fairly new to using VBA in Access and need some help with getting
the syntax and order of this correct.

I have a Function that creates a MailMerge in Word.
It selects data from a query in Access.

It prompts for the user to enter the VendorInvoiceNumber.
I want it to return a Message, if the VendorInvoiceNumber is not valid (does
not exist in the underlying table.)

This is what I have. The 'Prompt with incorrect VendorID is the part thats
not working.

Function MergeIt()
strVendorId = InputBox(Prompt:="Enter the desired Vendor Invoice Number .", _
Title:="ENTER VIN", Default:="")

If strVendorId = vbNullString Then
MsgBox "You have to enter something"
Else
Dim objWord As Word.Document
Set objWord = GetObject("C:\Komar\testletter2.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Komar\DTD.mdb", _
LinkToSource:=True, _
Connection:="QUERY _reportquery", _
SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '"
& strVendorId & "'"

' Prompt with incorrect VendorID
If VendorInvoiceNumber = vbNullString Then
MsgBox "This is not a valid Vendor Invoice Number"
Else

'"
' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
End If
End If
End Function

Thanks in advance for help on this.
Click to expand...
Click to expand...
 
Try:

SQLStatement:="SELECT * FROM [_reportquery] " _
& "WHERE VendorInvoiceNumber = '" _
& [Forms]![frmLetters].cboSelectName & "'"

You should not need to use the combo boxes column property if the Invoice
Number is in the bound column.

If this MergeIt function is in the code module associated with frmLetters,
then you can replace [Forms]![frmLetters] with me; it would look like:

SQLStatement:="SELECT * FROM [_reportquery] " _
& "WHERE VendorInvoiceNumber = '" _
& me.cboSelectName & "'"

----
HTH
Dale



4charity said:
Hi Dale,

Thanks for the quick response. I am out of the office the rest of the day,
so will not get a chance to try anything till tomorrow morning.

The Vendor Invoice Number is indeed a text string. This value is assigned by
each vendor, on their invoice, and can vary from only integers to a
combination of integers and text.

My original plan was to use a combo box, which I had set up, but could not
get the correct syntax for the VendorInvoiceNumber field. This is what I had:

SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '" &
[Forms]![frmLetters]![Me.cboSelectName.Column(1)] & "'"

Would you know the correct syntax for that? It would, as you said, make it
much easier.

Thanks.

Dale Fye said:
Rather than use an input box to get the vendorID, I would put this on a form
as a combo box or listbox, and let the user select from a list. This
guarantees that the VendorID would be valid. You might even go so far as to
only display those vendors that have open invoices and where the
"LastInvoiceSent" field is more than two weeks old.

In your SQL statement, you have a where clause that is comparing an Invoice
Number with a VendorID; that doesn't make any sense to me. Additionally, you
are calling these ID values (which usually implies that they are numeric),
but are using treating them as though they are strings. Maybe you want:

"... WHERE [VendorID] = " & intVendorID

But if a vendor has multiple invoices, this criteria would return multiple
records for that vendor, so you might need to be more restrictive by
including an additional part to the criteria that only selects those records
where the invoice has not been paid.

----
HTH
Dale



4charity said:
Hi. I am fairly new to using VBA in Access and need some help with getting
the syntax and order of this correct.

I have a Function that creates a MailMerge in Word.
It selects data from a query in Access.

It prompts for the user to enter the VendorInvoiceNumber.
I want it to return a Message, if the VendorInvoiceNumber is not valid (does
not exist in the underlying table.)

This is what I have. The 'Prompt with incorrect VendorID is the part thats
not working.

Function MergeIt()
strVendorId = InputBox(Prompt:="Enter the desired Vendor Invoice Number .", _
Title:="ENTER VIN", Default:="")

If strVendorId = vbNullString Then
MsgBox "You have to enter something"
Else
Dim objWord As Word.Document
Set objWord = GetObject("C:\Komar\testletter2.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Komar\DTD.mdb", _
LinkToSource:=True, _
Connection:="QUERY _reportquery", _
SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '"
& strVendorId & "'"

' Prompt with incorrect VendorID
If VendorInvoiceNumber = vbNullString Then
MsgBox "This is not a valid Vendor Invoice Number"
Else

'"
' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
End If
End If
End Function

Thanks in advance for help on this.
Click to expand...
Click to expand...
Click to expand...
 
Dale,
This syntax is now OK, as I am not getting an error message on finding the
cboSelectName. But I am getting a Run-time error '5631': Word could not merge
the main document with the data source because the data records were empty or
no data records matched your query options.

In trying to figure this out (and excuse me for my very, very base
knowledge), the name of the query that is used for the combo box is
qry_Invoice_Detail. This is located on the frmLetters Form. The rest of the
info needed for the MailMerge is in the _reportquery Query.

Dale Fye said:
Try:

SQLStatement:="SELECT * FROM [_reportquery] " _
& "WHERE VendorInvoiceNumber = '" _
& [Forms]![frmLetters].cboSelectName & "'"

You should not need to use the combo boxes column property if the Invoice
Number is in the bound column.

If this MergeIt function is in the code module associated with frmLetters,
then you can replace [Forms]![frmLetters] with me; it would look like:

SQLStatement:="SELECT * FROM [_reportquery] " _
& "WHERE VendorInvoiceNumber = '" _
& me.cboSelectName & "'"

----
HTH
Dale



4charity said:
Hi Dale,

Thanks for the quick response. I am out of the office the rest of the day,
so will not get a chance to try anything till tomorrow morning.

The Vendor Invoice Number is indeed a text string. This value is assigned by
each vendor, on their invoice, and can vary from only integers to a
combination of integers and text.

My original plan was to use a combo box, which I had set up, but could not
get the correct syntax for the VendorInvoiceNumber field. This is what I had:

SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '" &
[Forms]![frmLetters]![Me.cboSelectName.Column(1)] & "'"

Would you know the correct syntax for that? It would, as you said, make it
much easier.

Thanks.

Dale Fye said:
Rather than use an input box to get the vendorID, I would put this on a form
as a combo box or listbox, and let the user select from a list. This
guarantees that the VendorID would be valid. You might even go so far as to
only display those vendors that have open invoices and where the
"LastInvoiceSent" field is more than two weeks old.

In your SQL statement, you have a where clause that is comparing an Invoice
Number with a VendorID; that doesn't make any sense to me. Additionally, you
are calling these ID values (which usually implies that they are numeric),
but are using treating them as though they are strings. Maybe you want:

"... WHERE [VendorID] = " & intVendorID

But if a vendor has multiple invoices, this criteria would return multiple
records for that vendor, so you might need to be more restrictive by
including an additional part to the criteria that only selects those records
where the invoice has not been paid.

----
HTH
Dale



:

Hi. I am fairly new to using VBA in Access and need some help with getting
the syntax and order of this correct.

I have a Function that creates a MailMerge in Word.
It selects data from a query in Access.

It prompts for the user to enter the VendorInvoiceNumber.
I want it to return a Message, if the VendorInvoiceNumber is not valid (does
not exist in the underlying table.)

This is what I have. The 'Prompt with incorrect VendorID is the part thats
not working.

Function MergeIt()
strVendorId = InputBox(Prompt:="Enter the desired Vendor Invoice Number .", _
Title:="ENTER VIN", Default:="")

If strVendorId = vbNullString Then
MsgBox "You have to enter something"
Else
Dim objWord As Word.Document
Set objWord = GetObject("C:\Komar\testletter2.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Komar\DTD.mdb", _
LinkToSource:=True, _
Connection:="QUERY _reportquery", _
SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '"
& strVendorId & "'"

' Prompt with incorrect VendorID
If VendorInvoiceNumber = vbNullString Then
MsgBox "This is not a valid Vendor Invoice Number"
Else

'"
' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
End If
End If
End Function

Thanks in advance for help on this.
Click to expand...
Click to expand...
Click to expand...
 
It sounds like [qry_Invoice_Details] is returning InvoiceNumbers that are not
in [_reportquery]. Please post the SQL statements for [qry_Invoice_Detail]
and [_reportquery]?

You might also want to try creating an "unmatched" query using these two
queries and the query wizard to determine what records that
[qry_Invoice_Details] are returning that are not in the other query.

If [qry_Invoice_Detail] is done correctly, it will only display the Invoice
numbers that are in [-queryreport]

----
HTH
Dale



4charity said:
Dale,
This syntax is now OK, as I am not getting an error message on finding the
cboSelectName. But I am getting a Run-time error '5631': Word could not merge
the main document with the data source because the data records were empty or
no data records matched your query options.

In trying to figure this out (and excuse me for my very, very base
knowledge), the name of the query that is used for the combo box is
qry_Invoice_Detail. This is located on the frmLetters Form. The rest of the
info needed for the MailMerge is in the _reportquery Query.

Dale Fye said:
Try:

SQLStatement:="SELECT * FROM [_reportquery] " _
& "WHERE VendorInvoiceNumber = '" _
& [Forms]![frmLetters].cboSelectName & "'"

You should not need to use the combo boxes column property if the Invoice
Number is in the bound column.

If this MergeIt function is in the code module associated with frmLetters,
then you can replace [Forms]![frmLetters] with me; it would look like:

SQLStatement:="SELECT * FROM [_reportquery] " _
& "WHERE VendorInvoiceNumber = '" _
& me.cboSelectName & "'"

----
HTH
Dale



4charity said:
Hi Dale,

Thanks for the quick response. I am out of the office the rest of the day,
so will not get a chance to try anything till tomorrow morning.

The Vendor Invoice Number is indeed a text string. This value is assigned by
each vendor, on their invoice, and can vary from only integers to a
combination of integers and text.

My original plan was to use a combo box, which I had set up, but could not
get the correct syntax for the VendorInvoiceNumber field. This is what I had:

SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '" &
[Forms]![frmLetters]![Me.cboSelectName.Column(1)] & "'"

Would you know the correct syntax for that? It would, as you said, make it
much easier.

Thanks.

:

Rather than use an input box to get the vendorID, I would put this on a form
as a combo box or listbox, and let the user select from a list. This
guarantees that the VendorID would be valid. You might even go so far as to
only display those vendors that have open invoices and where the
"LastInvoiceSent" field is more than two weeks old.

In your SQL statement, you have a where clause that is comparing an Invoice
Number with a VendorID; that doesn't make any sense to me. Additionally, you
are calling these ID values (which usually implies that they are numeric),
but are using treating them as though they are strings. Maybe you want:

"... WHERE [VendorID] = " & intVendorID

But if a vendor has multiple invoices, this criteria would return multiple
records for that vendor, so you might need to be more restrictive by
including an additional part to the criteria that only selects those records
where the invoice has not been paid.

----
HTH
Dale



:

Hi. I am fairly new to using VBA in Access and need some help with getting
the syntax and order of this correct.

I have a Function that creates a MailMerge in Word.
It selects data from a query in Access.

It prompts for the user to enter the VendorInvoiceNumber.
I want it to return a Message, if the VendorInvoiceNumber is not valid (does
not exist in the underlying table.)

This is what I have. The 'Prompt with incorrect VendorID is the part thats
not working.

Function MergeIt()
strVendorId = InputBox(Prompt:="Enter the desired Vendor Invoice Number .", _
Title:="ENTER VIN", Default:="")

If strVendorId = vbNullString Then
MsgBox "You have to enter something"
Else
Dim objWord As Word.Document
Set objWord = GetObject("C:\Komar\testletter2.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Komar\DTD.mdb", _
LinkToSource:=True, _
Connection:="QUERY _reportquery", _
SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '"
& strVendorId & "'"

' Prompt with incorrect VendorID
If VendorInvoiceNumber = vbNullString Then
MsgBox "This is not a valid Vendor Invoice Number"
Else

'"
' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
End If
End If
End Function

Thanks in advance for help on this.
Click to expand...
Click to expand...
Click to expand...
 
You are correct! The two queries so not have matching results.
I will look at these and see if I can get to the root of the problem, and
post back if I still have a problem.

Thanks so much.


Dale Fye said:
It sounds like [qry_Invoice_Details] is returning InvoiceNumbers that are not
in [_reportquery]. Please post the SQL statements for [qry_Invoice_Detail]
and [_reportquery]?

You might also want to try creating an "unmatched" query using these two
queries and the query wizard to determine what records that
[qry_Invoice_Details] are returning that are not in the other query.

If [qry_Invoice_Detail] is done correctly, it will only display the Invoice
numbers that are in [-queryreport]

----
HTH
Dale



4charity said:
Dale,
This syntax is now OK, as I am not getting an error message on finding the
cboSelectName. But I am getting a Run-time error '5631': Word could not merge
the main document with the data source because the data records were empty or
no data records matched your query options.

In trying to figure this out (and excuse me for my very, very base
knowledge), the name of the query that is used for the combo box is
qry_Invoice_Detail. This is located on the frmLetters Form. The rest of the
info needed for the MailMerge is in the _reportquery Query.

Dale Fye said:
Try:

SQLStatement:="SELECT * FROM [_reportquery] " _
& "WHERE VendorInvoiceNumber = '" _
& [Forms]![frmLetters].cboSelectName & "'"

You should not need to use the combo boxes column property if the Invoice
Number is in the bound column.

If this MergeIt function is in the code module associated with frmLetters,
then you can replace [Forms]![frmLetters] with me; it would look like:

SQLStatement:="SELECT * FROM [_reportquery] " _
& "WHERE VendorInvoiceNumber = '" _
& me.cboSelectName & "'"

----
HTH
Dale



:

Hi Dale,

Thanks for the quick response. I am out of the office the rest of the day,
so will not get a chance to try anything till tomorrow morning.

The Vendor Invoice Number is indeed a text string. This value is assigned by
each vendor, on their invoice, and can vary from only integers to a
combination of integers and text.

My original plan was to use a combo box, which I had set up, but could not
get the correct syntax for the VendorInvoiceNumber field. This is what I had:

SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '" &
[Forms]![frmLetters]![Me.cboSelectName.Column(1)] & "'"

Would you know the correct syntax for that? It would, as you said, make it
much easier.

Thanks.

:

Rather than use an input box to get the vendorID, I would put this on a form
as a combo box or listbox, and let the user select from a list. This
guarantees that the VendorID would be valid. You might even go so far as to
only display those vendors that have open invoices and where the
"LastInvoiceSent" field is more than two weeks old.

In your SQL statement, you have a where clause that is comparing an Invoice
Number with a VendorID; that doesn't make any sense to me. Additionally, you
are calling these ID values (which usually implies that they are numeric),
but are using treating them as though they are strings. Maybe you want:

"... WHERE [VendorID] = " & intVendorID

But if a vendor has multiple invoices, this criteria would return multiple
records for that vendor, so you might need to be more restrictive by
including an additional part to the criteria that only selects those records
where the invoice has not been paid.

----
HTH
Dale



:

Hi. I am fairly new to using VBA in Access and need some help with getting
the syntax and order of this correct.

I have a Function that creates a MailMerge in Word.
It selects data from a query in Access.

It prompts for the user to enter the VendorInvoiceNumber.
I want it to return a Message, if the VendorInvoiceNumber is not valid (does
not exist in the underlying table.)

This is what I have. The 'Prompt with incorrect VendorID is the part thats
not working.

Function MergeIt()
strVendorId = InputBox(Prompt:="Enter the desired Vendor Invoice Number .", _
Title:="ENTER VIN", Default:="")

If strVendorId = vbNullString Then
MsgBox "You have to enter something"
Else
Dim objWord As Word.Document
Set objWord = GetObject("C:\Komar\testletter2.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Komar\DTD.mdb", _
LinkToSource:=True, _
Connection:="QUERY _reportquery", _
SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '"
& strVendorId & "'"

' Prompt with incorrect VendorID
If VendorInvoiceNumber = vbNullString Then
MsgBox "This is not a valid Vendor Invoice Number"
Else

'"
' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
End If
End If
End Function

Thanks in advance for help on this.
Click to expand...
Click to expand...
Click to expand...
 
2 things:

1) I examined the 2 queries and changed them so they match.

2) It was still not finding the data from the combo box. I added back the
Column number (even though I checked, and that column was bound), and now it
all works.

Thanks so much for your help!

Here is the new string that works:
SQLStatement:="SELECT * FROM [_reportquery] " _
& "WHERE VendorInvoiceNumber = '" _
& Me.cboSelectName.Column(1) & "'"

4charity said:
You are correct! The two queries so not have matching results.
I will look at these and see if I can get to the root of the problem, and
post back if I still have a problem.

Thanks so much.


Dale Fye said:
It sounds like [qry_Invoice_Details] is returning InvoiceNumbers that are not
in [_reportquery]. Please post the SQL statements for [qry_Invoice_Detail]
and [_reportquery]?

You might also want to try creating an "unmatched" query using these two
queries and the query wizard to determine what records that
[qry_Invoice_Details] are returning that are not in the other query.

If [qry_Invoice_Detail] is done correctly, it will only display the Invoice
numbers that are in [-queryreport]

----
HTH
Dale



4charity said:
Dale,
This syntax is now OK, as I am not getting an error message on finding the
cboSelectName. But I am getting a Run-time error '5631': Word could not merge
the main document with the data source because the data records were empty or
no data records matched your query options.

In trying to figure this out (and excuse me for my very, very base
knowledge), the name of the query that is used for the combo box is
qry_Invoice_Detail. This is located on the frmLetters Form. The rest of the
info needed for the MailMerge is in the _reportquery Query.

:

Try:

SQLStatement:="SELECT * FROM [_reportquery] " _
& "WHERE VendorInvoiceNumber = '" _
& [Forms]![frmLetters].cboSelectName & "'"

You should not need to use the combo boxes column property if the Invoice
Number is in the bound column.

If this MergeIt function is in the code module associated with frmLetters,
then you can replace [Forms]![frmLetters] with me; it would look like:

SQLStatement:="SELECT * FROM [_reportquery] " _
& "WHERE VendorInvoiceNumber = '" _
& me.cboSelectName & "'"

----
HTH
Dale



:

Hi Dale,

Thanks for the quick response. I am out of the office the rest of the day,
so will not get a chance to try anything till tomorrow morning.

The Vendor Invoice Number is indeed a text string. This value is assigned by
each vendor, on their invoice, and can vary from only integers to a
combination of integers and text.

My original plan was to use a combo box, which I had set up, but could not
get the correct syntax for the VendorInvoiceNumber field. This is what I had:

SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '" &
[Forms]![frmLetters]![Me.cboSelectName.Column(1)] & "'"

Would you know the correct syntax for that? It would, as you said, make it
much easier.

Thanks.

:

Rather than use an input box to get the vendorID, I would put this on a form
as a combo box or listbox, and let the user select from a list. This
guarantees that the VendorID would be valid. You might even go so far as to
only display those vendors that have open invoices and where the
"LastInvoiceSent" field is more than two weeks old.

In your SQL statement, you have a where clause that is comparing an Invoice
Number with a VendorID; that doesn't make any sense to me. Additionally, you
are calling these ID values (which usually implies that they are numeric),
but are using treating them as though they are strings. Maybe you want:

"... WHERE [VendorID] = " & intVendorID

But if a vendor has multiple invoices, this criteria would return multiple
records for that vendor, so you might need to be more restrictive by
including an additional part to the criteria that only selects those records
where the invoice has not been paid.

----
HTH
Dale



:

Hi. I am fairly new to using VBA in Access and need some help with getting
the syntax and order of this correct.

I have a Function that creates a MailMerge in Word.
It selects data from a query in Access.

It prompts for the user to enter the VendorInvoiceNumber.
I want it to return a Message, if the VendorInvoiceNumber is not valid (does
not exist in the underlying table.)

This is what I have. The 'Prompt with incorrect VendorID is the part thats
not working.

Function MergeIt()
strVendorId = InputBox(Prompt:="Enter the desired Vendor Invoice Number .", _
Title:="ENTER VIN", Default:="")

If strVendorId = vbNullString Then
MsgBox "You have to enter something"
Else
Dim objWord As Word.Document
Set objWord = GetObject("C:\Komar\testletter2.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the DTD database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Komar\DTD.mdb", _
LinkToSource:=True, _
Connection:="QUERY _reportquery", _
SQLStatement:="SELECT * FROM [_reportquery] WHERE VendorInvoiceNumber = '"
& strVendorId & "'"

' Prompt with incorrect VendorID
If VendorInvoiceNumber = vbNullString Then
MsgBox "This is not a valid Vendor Invoice Number"
Else

'"
' Execute the mail merge.
objWord.MailMerge.Execute
'close the orginal file
objWord.Close wdDoNotSaveChanges
End If
End If
End Function

Thanks in advance for help on this.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top