Updating a field in another table

[Guest]

Good afternoon
I have a database that tracks accounting functions for my department. I have figures out alot of things but I can't figure out this answer, so any help would be appreciated
I have 3 tables for various types of invoices-Driveway permit, utioity permit and OS/Ow permits
I have one table for payments
What I would like to do is inpuit the data from the checks in the payment form(table) and have A macro or code know what table to open to mark the invoice as paid. Now I have to open each form, put a yes in the invoice number and then go to the payments form and renter the data
Any Ideas
Thank you for your help
Wendy

 

[Nikos Yannacopoulos]

Wendy,

Not hard to do as is, but you need to provide more details on the structure
of the invoice tables and the payments table (names of tables and fields
involved), the fields on which each invoice table is joined to the payments
table, the name of the form and the involved controls on it.

Generally speaking, though, keeping three separate tables for invoices
doesn't look very good from a design point of view, storing them all in one
table (maybe with an extra field to store the invoice type) would be a much
more robust design, and make your life a lot easier through the rest of the
development. Are the three tables that different to justify being split up?
If not, I would suggest you revisit the design before you proceed any
further with the development.

HTH,
Nikos

Good afternoon,
I have a database that tracks accounting functions for my department. I

have figures out alot of things but I can't figure out this answer, so any
help would be appreciated.

I have 3 tables for various types of invoices-Driveway permit, utioity permit and OS/Ow permits.
I have one table for payments.
What I would like to do is inpuit the data from the checks in the payment

form(table) and have A macro or code know what table to open to mark the
invoice as paid. Now I have to open each form, put a yes in the invoice
number and then go to the payments form and renter the data.

 

[Nikos Yannacopoulos]

Wendy,

I suppose your three invoice tables have the same structure? For the purpose
of this example, I'll assume they are called tblInvDriveway, tblInvUtility
and tblInvOSOW respectively, and you'll change the references in the sample
code as required. Likewise, I'll assume the controls on your form for
Customer ID, Invoice Number and Invoice Type are called txtCustID, txtInvNo
and cboInvType respectively, and agian you'll have to change as required.

You will need to add a command button on your form to mark the invoice as
Paid, and paste the following code in its Click event:

Dim strSQL As String, tbl As String
Select Case Me.cboInvType
Case "Driveway"
tbl = "tblInvDriveway"
Case "Utility"
tbl = "tblInvUtility"
Case "OS/OW"
tbl = "tblInvOSOW"
End Select
strSQL = "UPDATE " & tbl & " SET Paid = True"
strSQL = strSQL & " WHERE [Customer ID] = " & Me.txtCustID
strSQL = strSQL & " AND [Inv#] = " & Me.txtInvNo
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

The code above assumes that both the Customer D and Inv# fields are numeric;
otherwise they require some quoptes around them like:
strSQL = strSQL & " WHERE [Customer ID] = ' " & Me.txtCustID & " ' "

for instance.

When you consolidate your three Invoice tables (say, tblInvoices) and add an
extra field for invoice type (say, InvType) then the code will need to be
changed to something like:

Dim strSQL As String
strSQL = "UPDATE tblInvoices SET Paid = True"
strSQL = strSQL & " WHERE [Customer ID] = " & Me.txtCustID
strSQL = strSQL & " AND [Inv#] = " & Me.txtInvNo
strSQL = strSQL & " AND [InvType] = ' " & Me.cboInvType & " ' "
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

(note the quotes around Me.cboInvType which is text).

HTH,
Nikos

Here is more info.
Invoice Table:
Inv#
Inv Date
Customer ID
Permit Fee
Paid (Y/N)

Customer Form:
Customer ID
Customer Name
Address
etc...

Payments Table
Payment #
Customer Name
Customer ID
Line Item Number
Payment Amt
Payment Type ( Cash or Check)
Ck#
Invoice Type (Driveway, Utility, OS/OW)
Invoice Number

I have a one to many relationship on all with Customer ID in customer and

Customer ID in the other tables.

My various invoice tables do need to be merged, which I am working on, but

I would like to figure this out first.