Auto-populating invoice number into a certain field

[tegand]

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

 

[Ranman256]

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

 

[arnelgp]

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));

 

[tegand]

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