Form text box updates for all records

[AndrewsPanda]

I'm going to try and explain this to the best of my ability. I have a Form


that is drawing data from a datasheet. The datasheet contains 300 entries so the form has 300 records. I want to be able to create a text box that isn't
bound to anything. But the text box has to be updated for each record
displayed in the form.

For example I want to have Customer 1 have a text box for Kit Location, in
the available space I put the location which might be "Warehouse".
Then for customer 2 for kit location I want to be able to put "Office"

At the moment if I put "Warehouse" for customer 1 it will put warehouse for
all 300 customers.

Any help would be greatly appreciated!

Thanks
Andrew

[pbaldy]

An unbound textbox will do that. How do you expect Access to remember the value for each record if the textbox isn't bound?

[AndrewsPanda]

Ok I appreciate that. Here is the explanation of what I am trying to do.

I have a datasheet of over 300 customers. I use forms to represent information of those customers for certain things. Ie. Restocking first aid kits. On the form I have all the information I need per customer to know about the restocking job.

I am trying to have kit information displayed for each of the first aid kits for each of the customers,
Kit No
Kit Type
Kit Location
Checklist type

This will show per customer what kits they have, where they are etc so we have all the information we need to go restock the first aid kits.

Unforunately there might be up to 15 kits per each customer. So I might need to have up to 15 different lots of information for customer on record.

The only way I know how to do this is to create a Field for each option, 15 times over. Eg.

Field1: Kit No1
Field 2: Kit Type1
Field3: Kit Location1
Field 4: Checklist Type1
Field5: Kit No2
Field6: Kit Type2
Field7: Kit Location2
Field8: Checklist Type2
Field9: Kit No3
^^^ All the way down to kit No 20.

If there is a better way of doing this PLEASE help me do so. it will be a tremendous help.

Thanks
Andrew

[pbaldy]

You definitely don't want that. The normalized solution would be a related table. It would have your basic fields:

Field1: Kit No
Field 2: Kit Type
Field3: Kit Location
Field 4: Checklist Type

plus a field for the customer. Presumably you have a customer ID field in a customer table, and this would be the same. That field links the two tables together, telling Access which customer each kit is related to. If I was a customer with 15 kits, I would have 15 records in this table. You would typically represent that with a form/subform. The form would be based on your existing table, the subform to this new table. Master/child links would keep them in sync with each other (the wizard will ask about linking fields).

[AndrewsPanda]

WOW I really appreciate your help. That is such a way better way to doing it then my previous one. (I am self taught and still very new at this)

I created the second table for Kits. I added the Customer ID Field and also added the kit related fields. I then created a relationship between the two tables based on the Customer ID. I also created the Form/subform which I am absolutely blown away with.

Thanks alot.
Andrew

[pbaldy]

No problem Andrew; I think you'll be happier in the long run.

[AndrewsPanda]

Mate I can post this in a new thread if you prefer.

We have a first aid training business and I created an overly complex way of doing invoices drawing data from access into excel and then drawing that same data into the invoice. I thought well why not just create an invoice system on Access. Then I stumbled upon this youtube video of a Invoice and quotation system made on Access for $60. Curious to what your thoughts are? Also would I be able to link my customer database to this system to easily use customer information in the invoices?

http://www.youtube.com/watch?v=SEiT9T-99pw

[AndrewsPanda]

I also have another question about reports. From the same excel database drawing customer information from Access I then have it going to Word mailmerge to do confirmation letters of courses. I would much rather set up something similar in access.

Goal: To have a default letter template in access. Then to be able to in perhaps a form select the customer, it automatically pulls up the fields I have designated. Input some temporary information about the course into a box, ie. Course time, course location, price etc. Then it automatically adds this information onto a report which is the confirmation letter.

Can you please point me in the right direction.

[pbaldy]

As to the youtube video, I didn't watch it all the way through but it seemed to have a lot of functionality. If it does what you need, and the guy is legit, $60 is pretty cheap. I assume he's looking to make it up in volume and perhaps customization. Presuming he provides an open copy, I would expect you could export your data into his tables.

Not sure what you're looking for regarding reports. It is certainly possible to draw data out and populate a report to create customized confirmation letters, invoices, or whatever. I often use this method:

BaldyWeb wherecondition

to restrict a report to a specified customer, though if I'm going to be doing automated emails and such I'm more likely to use this method:

http://www.granite.ab.ca/access/emai...recipients.htm

Basically it's deciding whether to restrict from outside the report or from within.

[AndrewsPanda]

The invoicing system does seem like the way to go, a small price compared to the amount of time it would take for me to learn/create it.

As for the report I think I have overcomplicated it. Just like the related table thing earlier, the way I think of being able to do it is probably way harder then the normalized way. I also have no experience in VBA.

I guess Mail merge would best suit my need. I just wanted to make it as simple and as few steps as possible so its easy for my Dad to be able to do it.

What I had in my head is starting with a form. I wanted to be able to select multiple customers on the form, this would pull up the fields from the Customer table:

Company Name
Contact First Name
Contact Last Name

Then also type in the following information into boxes. (this would only need to be typed in once for this particular session because the same information here applies to all of the customers selected)

Course Date
Course Time
Course Location

Then have the information automatically put into a mail merge document, so each Customer gets their letter containing the information. I wasn't sure if this would be better done by mail merge or reports.. Or even if it was possible to do.

[pbaldy]

Personally I would do it with a report, but I suppose it could be done with a mail merge. I've never used that for this type of thing. Here's one way to filter a report for multiple selections:

http://www.baldyweb.com/multiselect.htm

[AndrewsPanda]

I went with the report idea. I am learning how to write up the template confirmation letter to includes the fields. I have become stuck on how to have normal text, then a field then normal text again. In the same text box.

Eg, Dear FirstName LastName,

=Trim([Contact First Name]) & " " & ([Contact Last Name]) & ","

So far I can do first name and last name. How do I put Dear at the front of it in the same text box?

[pbaldy]

The same way you did the space and the comma:

="Dear " & Trim([Contact First Name]) & " " & ([Contact Last Name]) & ","

[AndrewsPanda]

Once again thank you so very much for your help. I have created it now and it works great. The project is almost done =)