Insert value into Notes Field via a command button

[robotacha]

Hello,

First post here. Not used Access much, but I am opening a Tanning shop and need a customer database.



I have a basic DB set up, and I have a customer details form.

I need to know how I would update a field with "todays date & Service used" by clicking a command button.

example:

[ TEXT BOX] [COMMAND BUTTON] < labelled "Add Minutes"
[ LIST BOX ] [COMMAND BUTTON] < labelled "Service Used"

[ CUSTOMER VISIT HISTORY ]


If I enter "6" into the [TEXT BOX] and click the "add Minutes" button, I want the form to insert " Todays Date & 6 Minutes used" into the [CUSTOMER VISIT HISTORY] field, and at the same time, update the MINUTES USED column in the Customer Details table.

and also the same would be required for the "Services Used" Button. If I select "Pedicure" from the [LIST BOX] and click the "Services Used" button, I want the form to insert "Todays Date & Pedicure" into the [ CUSTOMER VISIT HISTORY] field.


Can anyone give me some advice please?

This is all I need to do to complete it


Thanks in Advance,

Mike.

[June7]

Why do you want to duplicate data? Also, should not combine distinct data into one field. You should have a field for date, field for minutes, field for service. Set the DefaultValue property for the date field to one of these: Date() or Now(). Enter minutes in minutes field, select service in service field (use a combobox that offers a list of options).

[robotacha]

Looking at the screenshot,

When a customer returns to the salon, I will bring up this form, and simply select which service or how many minutes they have used.

Then I click the relevant button and it logs it in the notes/visit details field. (If its minutes, then it will also deduct / add the amount from the minutes used/remaining field(s))

Am I doing it the difficult way then? What do you suggest doing, to achieve what I want to achieve?

I would have thought it would have been a simple macro to insert todays date & a value into another field.


Please help.

Also, I need to make it as simple as possible for my staff to input customers visits.


Regards,

Mike.

[June7]

I haven't been able to get a macro to save value to field, only VBA.

What is that Visit Details - a textbox bound to a field? How are you currently getting info into that field? I would not do this. I would have separate fields as I described. What is your data structure (tables and relationships)? I suspect you have one flat file for everything you show on this form (which I hope doesn't show real personal data).

[robotacha]

Correct. I have one table only, logging all details.

The info that is currently in thevisit details field (a textbox bound to a field), has been manually typed.

This is mock data. just to test.

In a nutshell, I want a simple way of logging each visit by a customer.

What would you do?

[June7]

I would have multiple tables and set up form/subform arrangement.

Review this tutorial about relational database principles: http://forums.aspfree.com/microsoft-...es-208217.html

I expect your table structure would, at a minimum, be more like:

tblCustomers
CustomerID (primary key)
LastName
FirstName
etc

tblVisits
VisitID (primary key)
CustomerID (foreign key)
VisitDate
VisitPurpose
VisitTime

tblVisitDetails (if you want to allow more than one comment per visit, otherwise just have comment field in tblVisits)
VisitID (foreign key)
Comment

Keeping track of customer time and balance is the tricky part. You can continue to use the method you have which isn't really using the full relational concept. Maintaining an aggregate value in a table has its own risks. Without detail data there is no accountability for the balance shown. The alternative is to enter transactions of time in and time out and then calculate the balance from these transactions whenever it is needed. This provides an accountability of the balance but has its own complexities if you want to show this balance 'real time' on a form and not just as a 'snapshot' on a report. The report could list the detail transactions that contribute to the balance.

Of course, with either method, the basic source for documentation is the paper trail. Just have to decide how much you want in digital form.