Forms for Word using different records

[Splinters]

I need to construct a form in Access that uses data from different records within the DB - 4 identical formats on one page. This will be sent to Word to print (or could this be printed directly from Access?)...

Is this possible, or do I need to produce the separate sections individually and somehow combine them in Word?

I've attached a sample of what I do in Word.

Thanks,

Stephen

 

[Oldsoftboss]

Do it in Access.

Add some images, format the report a bit and away you go.

 

[Splinters]

Do it in Access.

Add some images, format the report a bit and away you go.

OK, that's a great start - thanks for the example.

Stephen

 

[Splinters]

OK, took a closer look.

The problem I see with this is that it requires all the records accessed to be sequential - I need to be able to access any 4 (out of many, at some point hundreds) individual bowl ID records, not in any order and possibly even forward or backward from the prior ID record. In the example set, I might want #3, then #1, then #4, and then #2. I don't want to change any of the data in the fields - new entries will be entered using a different form.

I probably should have mentioned that part at first.

I suspect that what I need is a form where I enter the 4 codes, and then that brings up the appropriate data from the specific records for those codes. I also am beginning to suspect it will be a VBA solution...

There was one bonus to your example, an answer to a question I had some time back - how to bring the individual picture into the form...

Thanks,

Stephen

 

[Oldsoftboss]

VBA is required, but your requirements are indeed possible.

Dave

 

[Oldsoftboss]

This example, although probably not how I would do it, is the easiest way I could think of for someone without a lot of coding knowledge.

Dave

 

[Splinters]

This example, although probably not how I would do it, is the easiest way I could think of for someone without a lot of coding knowledge.

Dave

Dave, that is outstanding! It does exactly what I needed it to do! I even loaded the photo paths & got the images...a bit of tweaking with the formating on the print should give me exactly what I've been doing by hand in Word.

A big thanks,

Stephen

 

[Oldsoftboss]

While we are on a roll.....

 

[Splinters]

While we are on a roll.....

Oh, yes! Getting better all the time!

Again, thanks...you want to do my web page as well? (just kiddin')

Stephen

 

[Oldsoftboss]

you want to do my web page as well? (just kiddin')

Stephen

Sure, as soon as I finish my own http://members.dodo.com.au/predatorsoftware/predatorsoftware.html

I've only been doing for about a year... see how far I got. One page

 

[Splinters]

Well, at least you have most of the info on the one page...

It's been about 3 years for mine - http://www.splinterscraftworks.us/, nice images but slow and clunky.

Another of those "soon, right after..." projects.

Stephen

 

[Splinters]

Dave, I've tried to edit the example by changing the field names to have prefixes - ProductCode becomes txtProductCode sort of thing, to make it consistent with my other files - and seem to be hitting a brick wall of sorts.

I have set Tools/Options/General/Name AutoCorrect to change all instances of names when the field name is changed in the table, and to log it. On ProductDetails I still had to change the name in all the forms, queries & report, but it seemed to make it OK. When I try to change any of the others, however, I run into errors and sometimes Access just locks me out! And I am going into the VBA modules to find & change all of the names there.

Is there something I am missing here? Shouldn't having the autocorrect set make the changes needed?

Stephen

 

[boblarson]

Dave, I've tried to edit the example by changing the field names to have prefixes - ProductCode becomes txtProductCode sort of thing, to make it consistent with my other files - and seem to be hitting a brick wall of sorts.

I have set Tools/Options/General/Name AutoCorrect to change all instances of names when the field name is changed in the table, and to log it. On ProductDetails I still had to change the name in all the forms, queries & report, but it seemed to make it OK. When I try to change any of the others, however, I run into errors and sometimes Access just locks me out! And I am going into the VBA modules to find & change all of the names there.

Is there something I am missing here? Shouldn't having the autocorrect set make the changes needed?

Stephen

Don't rely on autocorrect as it can honk up your database. Instead download the free V-Tools and use their Total Deep Search, which allows you to find EVERYWHERE something is being used and can also do a replace. You can find the tools here:

http://www.skrol29.com/dev/en_vtools.htm

 

[Splinters]

Don't rely on autocorrect as it can honk up your database. Instead download the free V-Tools and use their Total Deep Search, which allows you to find EVERYWHERE something is being used and can also do a replace. You can find the tools here:

http://www.skrol29.com/dev/en_vtools.htm

Oh, one of the things they don't tell you about in the books!

Just looked at the site, will definitely give it a try...thanks.

 

[Splinters]

OK, I used the V-Tools, and it did help locate some references I missed...but in most cases it required me to make the change manually - not a big deal but a bit more work.

I did notice that it does not seem to clear it's own buffer - after making a change and doing a search for any stragglers I still got the ones I had just changed listed in their old state. In fact, often the only way to clear the buffer was to quit Access & restart each time! But it was useful to find things I couldn't see - including at least one field in a form that was covered by another... (& caused no end of head scratching until I found it!)

So, as it is now, I have made the name changes - note in the attachment that I used "...Preduct..." in place of "...Product..." to make it easier to see what was happening after each change - and because it was the only way I could actually change the field named "Product" using the replace!

It seems to work - but (and you knew there was a but ) I get a question after hitting "Set Print Order" in frmProducts, asking for "qryPrintProducts.ProductID" - which has been changed in every reference I can find to "intPreductID". It seems to be coming from an event, but I can't seem to locate which one. If I type a number in the box & hit enter, it continues on just fine...

There also seems to be a formating problem with the actual print - mostly just making the changes in font size, etc. - I should get that done quickly.

Stephen

 

[Oldsoftboss]

It's coming from the record source for the subform (open the subform directly from the Db window)

SELECT [qryPrintProducts].ProductID, [qryPrintProducts].[txtPreductCode], [qryPrintProducts].[txtPreduct], [qryPrintProducts].[PrintOrder] FROM qryPrintProducts ORDER BY [qryPrintProducts].[PrintOrder];

should be....

SELECT qryPrintProducts.intPreductID, qryPrintProducts.txtPreductCode, qryPrintProducts.txtPreduct, qryPrintProducts.PrintOrder FROM qryPrintProducts ORDER BY qryPrintProducts.PrintOrder;

Dave

 

[Oldsoftboss]

Also did you notice that turning on the AutoCorrect "Feature" rotates your report to Portrait. (Possibly the formatting issue you mentioned.)

Yes, another Access blunder.

Dave

 

[Splinters]

It's coming from the record source for the subform (open the subform directly from the Db window)

SELECT [qryPrintProducts].ProductID, [qryPrintProducts].[txtPreductCode], [qryPrintProducts].[txtPreduct], [qryPrintProducts].[PrintOrder] FROM qryPrintProducts ORDER BY [qryPrintProducts].[PrintOrder];

should be....

SELECT qryPrintProducts.intPreductID, qryPrintProducts.txtPreductCode, qryPrintProducts.txtPreduct, qryPrintProducts.PrintOrder FROM qryPrintProducts ORDER BY qryPrintProducts.PrintOrder;

Dave

Yes, I see that now...so nice to have such compact work space, isn't it?

Thanks - I thought I had all of those, but sometimes what the eye sees doesn't make it all the way to the brain. Plus, after looking at them so many times they all start looking the way you think they should look.

And no, I hadn't noticed why the print went to Portrait - but I do now. But that's only part of the formatting needed - for one thing the fonts on the description are too big, and I need to set the borders in a bit to keep it all within the acceptable print area. And add a few additional tweaks.

Again, thanks loads for the assistance - helped me avoid a meltdown or two.

Stephen

 

[Oldsoftboss]

All the formatting is easily done. Just time consuming. Be sure to post back with the finished product. (Or should the be preduct)

Dave

 

[Splinters]

All the formatting is easily done. Just time consuming. Be sure to post back with the finished product. (Or should the be preduct)

Dave

Dare I predict that the preduct will be productive?

I'll do that - at least the short version (the full file would be too big). I still need to put all the data together - including getting the photos and descriptions on over 100 bowls into the PC! And I need to link the wood inventory with the bowl inventory, and add a gallery table to track who has what - and hopefully who sold what...

Little did I realize all this when I decided to start this small business effort...

But, since I really love turning bowls, it's well worth the work & hours - and might even pay for itself!

Thanks,

Stephen