Using a form button to add data to another form when opened.

[erickauff]

Semi new, untrained to MS Access.



This database is for home repairs.
What I have, Main Form ( frmPropertyInformation ), Sub Form ( frmRepairs ) and table ( tblRepairs ). In the table ( tblRepairs ) I have a field named ( R_Location ).

There are 35 different possible types for ( R_Location ). Initially I made it into a drop down menu, but have since want to change it. Since there maybe multiple inputs of the same ( R_Location ) of the ( frmRepairs ) form a person would have to scroll 60 plus times thru the drop down menu.

I have created a "main" form ( frmPropertyInformation ) which list all 35 categories for ( R_Location ) and some basic data field I return with a query for each ( R_Location ). These are listed in columns with each ( R_Location ) and some data as rows.

I have a button on each category row to add repair information ( frmRepairs ). This button currently only opens the ( frmRepair ) form.

Is it possible the the button also adds to the ( R_Location ) field?

In other words Main form go to a specific category. Click " Add Repairs " button to open sub form ( frmRepairs ). OnClick would also add a catagory to the field in the ( frmRepairs ) form.

Hope this is enough info and makes sense. Thanks in advance for any help.
Eric

[orange]

Here are some links to info re Database Design and Planning
Don't be too quick to jump into making a physical database and Access. Define your requirements.
Watch a few of the videos, then work through a couple of the tutorials. I'm sure they will help with concepts and design.
Good luck.

[Micron]

While that may not be the answer you were hoping for, it is well advised. One of the glaring possible problems with your approach is that it sounds like you have approached this with what I call a spreadsheet mentality (perhaps paradigm is a more palatable word). Location data probably belongs in the property info table, or maybe in its own table - can't say 'cause I don't know the business requirements of your db. It also reads like you created either a lookup field or multi value field in your table - another thing that most of us here would avoid.

These are listed in columns with each ( R_Location ) and some data as rows.

Given the situation, I presume you ought to check out a few other things as well. I'd understand if you considered all this too much, but believe me, it will save you a lot of head banging later on - when you're too deep to want to start over but frustrated due to the roadblocks you inadvertently put in your own way.

One source about how to name things - https://access-programmers.co.uk/for...d.php?t=225837
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

[erickauff]

Thank you both orange and Micron,

My "locations" would be words like roof, windows, sidewalk, interior walls, appliances, etc. My sub table & form ( tblRepairs & frmRepairs ) have a field name R_Location. What I would like to do is when I press the "Add Roof Repair" button, the onclick command would do two things 1. open frmRepairs and 2. (which I can't figure out) I would like to add to the R_location field. In this instance it would be "roof". Remember I have a button to add repairs for each possible location.

If this is not possible would I need to add a field to both my main table & form ( tblRepairInformation & frmRepairInformation ) and then link them to the sub form? If so can you show me how.

My database is almost done, with everything working smoothly. This is something we decided to add to eliminate paper work all together. Worst case I can use a drop down field, I'd just prefer not to. The button way seems to me to be the the easiest fix.

Thanks again for your quick responses.
Eric

[Micron]

Sounds like you'd rather continue on with what sounds like a poorly designed db (sorry to have to say) rather than start over. Can understand that somewhat as it can be hard to accept that you should start over on something. As a woodworker, I get it; the difference being once I'm done, it's done with. On the other hand, you will continue to struggle or at least work harder at it continually. 60 possible items in your combo list, so you have 60 buttons?? Why not a single combo box with a 'find as you type' feature?

Anyway, one way of populating a form field or making some other type of decision related to a button click or similar user event is to make use of the OpenArgs parameter of the DocCmd.Open[something] method.

DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

Maybe you'd write DoCmd.OpenForm ("frmMyForm", acNormal,,,,,"Patio"), then in the form's Open event, you do whatever is needed based on its OpenArgs property value.

Me.txtLocation = Me.OpenArgs

[orange]

Ditto on micron's response. We've all been there --I know what I need and I'm building it. I suggest that isn't the best strategy, but it may get you to where you want to be.
The tutorials at RogersAccessLibrary will show you the value of a clear simple description of your business, and will guide you in less than 60 minutes through a general approach to database design. Sure, it's basic, but the experience of working through the process is often a big help.

Good luck.

[erickauff]

Again thank you Micron & orange,

After replying, I did some more research (thanks for the links) and rewrote that section. Condensed to one table and used a subform and query to view data as it was being entered.

The "60" buttons idea was because I could not figure out a way to input certain information in a "child" field. I did/do not want to search the "location" field thru a lookup or by spelling it out. The "location" field has to be in a certain order and the descriptive words must be the same as they would be referenced throughout. Using one table I was able to collect all data needed. Using a query and subform gave me real time data on the main form. Using the "parent" "child" logic made using one button and the where clause work smoothly. Yeah that easy.

Thanks again,
Eric