Update query question

[YamiMarik777]

I have three tables. I have Vendor Name table w/ vendor#(p-key), vendor.I have a VendAlias table that has Vendor, Vend#(both fields are p-key). They have a one-to-many relationship. Next , I have an invoice table that has stmtdate, posteddate, transdate, Vendor, Vend#. I want Access to fill in Vend# in the Invoice table utilizing the VendAlias table. I thought about using a Lens() function because for instance, in the Invoice table vendor is HP services, but in the VendAlias table it could be HP products, HP online, etc. I am doing all of this to generate reports that total are grouped vendor by all of its invoices. Any help I would appreciate. Thank you.

 

[Pat Hartman]

I want Access to fill in Vend# in the Invoice table utilizing the VendAlias table

Use a combo to select the vendor using the names from the alias table but store the VendorNum.

Using special characters such as # or using embedded spaces in your names is poor practice. Also avoid the names of functions such as Month and Date and avoid the names of properties - especially "Name". All of these will cause problems with VBA.

 

[YamiMarik777]

Re: Pat Hartman

Thank you for the into. I need more explaination of combo. Do you mean create a combo box in report design view??

 

[Pat Hartman]

Sorry, I was thinking form when I wrote that. For a report, just use a text box.

 

[YamiMarik777]

Thank you for replying. These are things I want Access do. I want the user to be able to check a box off to show the Invoice is Paid and in the datasheet put in Date/Time in the DateModified and TimeModified field. I want to be able to print out a report that shows all invoices paid and unpaid. I want to be able to count all the invoices under one VendNum. I thought to have Access using grouping you have to establish a relationship. I have attached my relationships and attributes/datatype of all my tables. Please help I have a deadline for Friday evening. Thank you.

 

[Pat Hartman]

You can add code in the AfterUpdate of the checkbox to populate the DateTimeUpdated field. This should be a single field, not two separate fields for date and time.

Me.DateTimeUpdated = Now()

Now() returns the date and time whereas Date() returns only the date and Time() returns only the time.

If you want to select only unpaid invoices, you would add the following criteria to your query:

Where DateTimeUpdated Is Null;

If you want only paid invoices, the criteria would be:

Where DateTimeUpdated Is Not Null;