creating a button that updates a form (1 Viewer)

[rabamas]

HELP!

I have a form that I use to gather information from customers who call to inquire about our service. It writes to a table called "Inquiries", and collects the customer's name, address, contact, etc.

I have another table called "PaymentNmbrs", which stores numbers that have been pre-assigned to us by a company that takes payments from customers. What we are to do is to give one of these numbers to each customer that calls in to inquire.

So...on my inquiry form, which I fill-out while the customer is on the phone with me, I need to be able to pull-up an unassigned PayentNumber, maybe through a sub-form, and then click a button that will assign that number to that inquiry, which will also make it unavailable to be assigned again.

How do I do this? I don't know any code or VBA.

Frustrated,

Rabamas

 

[shay]

Possible solution for you ...

I created a table 'Payment numbers tbl' which contains fields PaymentNo and Assigned (Yes/No field set to No by default).

Put the following code behind your form...

Me.txtPaymentNo = DFirst("[PaymentNo]", "Payment Numbers tbl", "[Assigned]=No")

DoCmd.RunSQL "UPDATE [Payment numbers tbl] SET [Payment numbers tbl].Assigned = Yes" & _
"WHERE ((([Payment numbers tbl].PaymentNo)=[Forms]![Inquiries frm]![txtPaymentNo]));"

This code looks up the first unassigned payment number and then runs the SQL code to set its Assigned flag to Yes.

Hopefully this will solve your problem.

shay

 

[rabamas]

What do you mean, "put this code behind your form..."? I don't understand that statement. Where would I enter this code?

Also, how does this associate a Payment Number with an Inquiry? All this seems to do is to update Assigned field of the first available Payment Number to Yes.

rabamas

 

[shay]

Your Inquiries table needs a field to store the associated Payment number and the text box that will display the assigned payment number must be bound to this field.

Then the line

Me.txtPaymentNo = DFirst("[PaymentNo]", "Payment Numbers tbl", "[Assigned]=No")

will do what you want ie "give one of these numbers to each customer that calls in to inquire".

As to where you put the code, that's up to you. When do you want the payment number assigned? If you want the payment number assigned after the customer name has been entered, put the code I gave you in the customer name AfterUpdate event.

shay