Solved Mark a Yes/No Data Field with VBA

[Seph]

Good day everyone.

I have an Invoice form with a subform on it pulling records from another table (JobT) pertaining to the PK.

The TicketNumber on the Invoice form is a Relational Combo box that runs off of a query. It essentially filters the JobT for records marked as Completed.

Is there any VBA code that will allow me to mark the Job Invoiced data field on the Subform automatically or should I be pointing directly to the JobT record?

https://www.access-programmers.co.u...te-yes-no-field-with-vba-on-condition.292959/
I saw a similar post, however I need the Yes/No Field to be updated with the corresponding record selected.

Thank you in advance for any ideas.

 

[arnelgp]

When do you want to checkbox to be Ticked?
On Click event of the "Accept" button?

private sub AcceptBtn_Click()
With [SubformName].[Form]
    ![InvoicedTextbox] = True
    .Dirty = False
End With
End Sub

 

[Seph]

When do you want to checkbox to be Ticked?
On Click event of the "Accept" button?

private sub AcceptBtn_Click()
With [SubformName].[Form]
    ![InvoicedTextbox] = True
    .Dirty = False
End With
End Sub

Hi @arnelgp

You sir, are a legend.

I've been searching the web for hours.

Thanks again for everyone's input.

 

[Minty]

If you recorded the invoice number against the job(s) once you invoiced them, you would have a much better audit trail, rather than just knowing it had been invoiced, you would know what invoice it was billed on. Use that data to flag that it's completed, rather than just a Yes/No.

You could then easily use that data to open a copy of the invoice from the job history if you needed to look it up.

 

[Seph]

If you recorded the invoice number against the job(s) once you invoiced them, you would have a much better audit trail, rather than just knowing it had been invoiced, you would know what invoice it was billed on. Use that data to flag that it's completed, rather than just a Yes/No.

You could then easily use that data to open a copy of the invoice from the job history if you needed to look it up.

Thank you for taking the time to reply.

When I select the Relational Combo box on the parent form (referencing JobT), it inserts the value in the corresponding field of the InvoiceT. Thereby linking them together.

The Yes/No field was just a means for me to know if the particular record had been invoiced.

 

[Pat Hartman]

You KNOW it was invoiced because the InvoiceID is not null. Having a flag is redundant.

 

[Seph]

When do you want to checkbox to be Ticked?
On Click event of the "Accept" button?

private sub AcceptBtn_Click()
With [SubformName].[Form]
    ![InvoicedTextbox] = True
    .Dirty = False
End With
End Sub

@arnelgp Your VBA code is still going strong. Thank you again!

Can you please assist with a slight modification.

I'm trying to get it to work on a different Main form with no subform.

When I try the above code but insert the form's name into the With [SubformName].[Form], then I get the following error:

Thanks in advance!

 

[arnelgp]

without subform:

private sub AcceptBtn_Click()
Me![InvoicedTextbox] = True
End Sub

 

[Seph]

without subform:

private sub AcceptBtn_Click()
Me![InvoicedTextbox] = True
End Sub

Thank you again