Please help me - I'm new here!!

[Total Newbie]

Hi all,



Really new to Access, and have read a couple of books but they baffle me (I'm no programmer) so I thought I'd try this way of finding stuff out - It's often the best!!

Ok, I'm thinking of starting a new business, and I'd really like to write my own database rather than having to pay someone, as I think my requirements are simple.

It's a customer database - and these are the things I'd like it to do for me:-

1. Store names, addresses, and some general info I want to keep - that's fine, I can do that and have done so.
2. I have some fields with dates in, I have got 2 of them that when I click on the field, a little calendar icon pops up, and I click on it - easy - I need to do this in 2 other fields, but no matter how I try I can't get them to do the same - I have looked and looked at the format of these fields and cannot see that I have done anything different, but I must have done!
3. Have a table within a table that can store numbers (amounts of money essentially) for each customers spend and then add them together (i.e. I just enter the cost and the VAT rate, and it works things out such as the Total, the deposit (30%) which I can manually override and then it works out the balance to pay. Just like excel would (can't find how to do this).
4. Have buttons on the bottom of the customer form, which when I click it will produce the required paperwork for that customer. SO - I click "Create confirmation letter" and it places that record's details into a pre-written letter which I can then print off and send. I envisage about 8 pre-written letters so I'll need 8 buttons. It would also be quite fabulous to be able to then generate an invoice from the amounts of money fields listed above. If I am to generate invoices however it would need to be able to create a unique, sequential invoice number as well from that.

That's more than enough for starters, thank you in advance for any help you can give me.

[ranman256]

2. Your field the textbox stores to, must be a date/time field. And the textbox property SHOW DATE PICKER = For dates

3. you need a subtable/sub form to the tCustomer table:
tCustPay
-----------------
[tPayID] (autonum)
[CustID] (long) allow duplicates
[PayDate]
[Cost]
[VAT rate]


4. build a report, the report has a query, this query looks at the customer form to pull that 1 customer.
select tables where [custID] = forms!frmCustomer!txtCustID

now the report will ONLY gather that 1 customer.

[Total Newbie]

Thank you.

I'll work through those. But in 2) above, that's what I have done and it STILL works for 2 fields and not for 3 others - very frustrating!

[Total Newbie]

Just deleted the failing fields from the Customer Form - and re-entered them, checked and double checked, and they are set up right, but the little calandar doesn't appear! Do I need to set these profiles in Customer Form, or Customer Table - I've actually tried fiddling in both but cannot see what settings are different between the ones that work and the ones that don't

[Total Newbie]

Ok, deleted them completely, re-inserted them again - and working now!!!! jees Thanks for your help there.

Ok, so next is 3) a subtable / subform - can you tell me where I'd find that, can't see in "Forms" in design view.

[Total Newbie]

Have tried with 4) but totally don't understand what you've put. I have opened the query wizard - but there was no-where to enter any of what you put above? Is this something I will need to do a macro for? I don't know how to do them, or Visual basic etc.

So I get that I need to create an "event", then link a button to running that "event" - it's running the event I have the problem in doing!!

The event is to push my customer details into a pre-written word document, I just don't have a clue how to do that!

[ranman256]

make a new form for only your sub table, tCustPay, all fields , save as say: frmCustPay_Sub
hide the [custID] field on the sub form, visible = false.

now in the Master form ,frmCustomer, add a subform control onto the form.
in the properties of this subform, the SOURCE OBJECT will be frmCustPay_Sub
the LINK MASTER FIELDS will be: [CustID]
same with LINK CHILD FIELDS.


Now for the customer your form is on, you can enter payments into the subform. And they will only relate to that custID.

[Total Newbie]

ranman - you are clearly the master here, sadly I am a REAL newbie to all this - and struggling to understand.

I create a new form - got that, but what is my subtable?

Currently I have 1 Table that matters (the others are just to supply drop downs) - I called it "Customer Table" - so if I understand you, I create a new form called say frmCustPay_Sub, I can't seem to find where to set the "visible" to false, I was sure I saw it previously, so I'll take a look around.

Will do the rest and come back.

[Total Newbie]

You are going to have to take me in much smaller steps!!

How do I add a "Subform Control" to the main table?

Sorry to sound thick - but this is totally new to me

[ranman256]

create a blank form, connect it to the payment table,
add all the fields.
save the form as frmCustPay_sub

you have a master form frmCustomers
put a subForm icon (from the control toobar is an icon that says subform)
put the subform box on the frmCustomers form
in the properties of the subform,
set the SOURCE OBJECT will be frmCustPay_Sub
set the linking fields :
set the LINK MASTER FIELDS will be: [CustID]
set the LINK CHILD FIELDS will be: [CustID]

[Total Newbie]

Thank you,

Created the blank form, Connected it to the payment table ok.

Added the fields ok.

Saved as frm_custpay_sub ok.

I have put the subform icon into the main customer form ok as well.

I have the subform open in Design view, but cannot even with the property sheet open, see anywhere that says source object or "link master fields" or child fields, is this in another menu somewhere.

Thanks for your help, I'd never have found all this out without you!!

[ranman256]

Click the subForm box
view properties
Click DATA tab
the data info is there

[Total Newbie]

Brilliant - found it now!

That all seems set up anyhow - the Master and Child fields are just set to [ID] though - which is what the identifier is on the main customer form - I assume that's ok?

So If I fill in the boxes now with "Cost" "VAT" how do I get it to add the two together to get a total, and work out percentages and stuff - i.e. use it a bit like access to create simple formulas...

I want to

a) Input the cost, the square meterage, and the vat rate, and have it work out the VAT for me, the total, the amount I have charged per M2 and what 30% deposit will be and the balance to pay. If I manually enter the deposit, I want it to re-calculate and tell me the new balance.

b) Then - in a perfect world - I'd be able to click on a button that said "raise invoice" and it would drop the name, address and financial details into a pre-laid out excel sheet thus creating the invoice along with a unique, sequential invoice number for that invoice - but that's not critical, if it could create the invoice, I can allocate an invoice number very easily.

[ranman256]

It MUST be the exact field name in your tables....it's possible it used default names.

[Total Newbie]

That seems to work in one way, but not sure it's working how I want!!

If I put in 3 different invoice amounts for customer 1. (So lets say he had 3 orders from me), I can scroll through them in customer 1's record, but when I click to the next customer, I get the same 3 invoices! The invoices don't seem tied to the customer, it's just like another database that shows in the main customer records form but is independant of the first record.....

I need to be able to store financial details of each customers transactions, which are for their record not ALL the financial records for ALL the customers showing up in a window that is the same window as the customer records...

Is that clear, as I seem to have confused myself there!