Auto-populating invoice number into a certain field (1 Viewer)

Hi Everyone

I have a query in which I have created that extracts all payments that have not been paid (no invoice number). In one of my source tables I have an invoice number field (as my query draws from several tables it has become read only) which needs to be populated when the query is run.

I have created a formula that will auto-generate my invoice number in the query, but I am unsure how to make it populate into my invoice number column in the source table.

ClaimRef: ("641" & (CStr(Format(Date(),"yyyymm"))) & [ServiceID])

To add another layer of confusion for me, the source table column is called InvoiceNumber, however in the query it needs to be labelled ClaimReference. So I currently have it set up like this:
ClaimReference: InvoiceNumber

So I am not sure then how to incorporate my formula into this so it populates in the correct column.

Your help would be appreciated

the field autonum is a great idea to use as Invoice#. It is created automatically.
the user does nothing, there is zero programming.
but, if you want to put more work into this, if you are creating an invoice# to the tInvoice table, you do not have to label the field during the append, just append it to the correct table field:

("641" & (CStr(Format(Date(),"yyyymm"))) & [ServiceID]) will append to the field: tInvoices.ClaimRef

as Ranman stated, use Update query
to update your InvoiceTable:

UPDATE yourInvoiceName SET yourInvoiceName.InvoiceNumber = "641" & (CStr(Format(Date(),"yyyymm"))) & yourInvoiceName.[ServiceID]
WHERE (((yourInvoiceName.InvoiceNumber) Is Null));

(as my query draws from several tables it has become read only)

Click to expand...

The number of tables is not what is making the query not updateable. Either one of the tables is not updateable and that has made the query not updateable or you have included multiple 1-many relationships that are not hierarchical. An example:
Student-->Classes
Student-->Pets
Just because Classes and Pets contain StudentID doesn't mean they have any relationship to each other and all three cannot be used in the same query if you expect it to be updateable.

Perhaps you don't need to include all the tables in the query that generates the InvoiceNumber. Perhaps, you can just generate the invoice number and then select the new invoice numbers for the report or export file.

If you post your database (after removing or disguising sensitive information), we can help figure out why the query is not updateable.

I have multiple queries that include more than a dozen tables and they are updateable. I haven't seen a hard limit. The updateability is governed by the relationships and whether or not the individual parts are updateable. Make sure that all the tables have unique primary keys. Defining relationships using the relationship diagram and enforcing RI is also appropriate.

Hi Everyone

Thankyou for your help so far, I am still super confused as to how I can get this working.

I am working on cleansing my DB of all sensitive data, as it is all pretty sensitive so I can attach it, but for now I thought i'd attached the SQL view of the query I am trying to figure out. (this doesn't have my self generating invoice number in it at this stage.) I have really basic knowledge of SQL code so it doesn't make a whole lot of sense to me, yet.:banghead:

SELECT qryPortalCurrent.RegistrationNumber, qryPortalCurrent.NDISParticipantNumber, tblInvoiceDetails.ServiceDate AS SupportsDeliveredFrom, tblInvoiceDetails.ServiceDate AS SupportsDeliveredTo, tblServiceTypes.ServiceTypeCode AS SupportNumber, tblInvoiceDetails.InvoiceNumber AS ClaimReference, DateDiff("n",[StartTime],[FinishTime])/60 AS Quantity, " " AS Hours, tblServiceTypes.FundValue AS UnitPrice, "" AS AuthorisedBy, "" AS ParticipantApproved, "" AS InKindFundingProgram
FROM qryPortalCurrent INNER JOIN (tblServiceTypes INNER JOIN tblInvoiceDetails ON tblServiceTypes.TypeofServiceID = tblInvoiceDetails.TypeofServiceID) ON (qryPortalCurrent.ClientID = tblInvoiceDetails.ClientID) AND (qryPortalCurrent.PlanID = tblInvoiceDetails.PlanID)
WHERE (((tblInvoiceDetails.ServiceDate)>=[Enter Start Date] And (tblInvoiceDetails.ServiceDate)<=[Enter End Date]) AND ((tblInvoiceDetails.InvoiceNumber) Is Null))
ORDER BY tblInvoiceDetails.ServiceDate;

and attached is a screenshot of my relationship table. I recently changed all of the relationships so they weren't enforce with referential integrity, but its still not giving me what I need to make it not read only.

I hope that makes some sense, I'll try and post my DB soon.

Thanks
T

There are some problems with your relationships.
1. RI is not enforced.
2. Some tables have extra Foreign keys. For example tblInoviceDetails does not need SupportCoordiantorID, or ClientID since both can be derived via the relationship with tblPlanDetails.
3. tblLGAs does not have a primary key defined
4. tblCatchments' primary key doesn't follow the naming standard of the other PK's. I can't tell about tblSupportCoordinat...
5. What is the point of tblCatchments and tblLGAs and why does it have a 1-1 relationship?

If you add RI and remove the extraneous relationships, your queries might become updateable.