Form not feeding macro enabled form with where clause

[gumbi17]

I am fairly new to the Access world and i have run into a problem with my macro enabled button. The forms name is called Well Master and has an autonumber every time a new record is created. All this information is fed to a Well-Master Table, but here is where I am getting into problems. I have a macro enabled button that launches a note feature so someone can write a note on the well, but the table that holds the note is in a table called Memos. When I press the button I want it to take the well autonumber and attach itself to the memo for population into the other table, but it is not happening. It will allow you to fill out everything on the form but without having the well number you can not tell what the note is attached to. So it goes to the table but is not attached to anything. It seems that when I change my where clause I get a lot of different results, so that is where I feel I am failing. The form is called Memo Form Well but the information for the note is within a subform called Memo Form Lease Well. Can you see anything wrong with my macro and help me get this working?

[June7]

One technique is to use the OpenArgs argument of OpenForm to pass a value to the opening form. Then code behind the opening form can extract value from OpenArgs and populate field. I know how to do this with VBA but since I don't use macros don't know if macro can.

Have you considered a form/subform arrangement using a tab control to organize other controls, including a subform on a tab page?

[gumbi17]

I have not used OpenArgs before, actually I hadn't even heard of it before. I am not much of a VBA guy, I have been trying to learn Macros just because it seems they are closer to SQL and I want to get better at SQL.

[June7]

And I'm not much of a macro gal. Macro coding doesn't seem anything like SQL to me. Two very different things. This shows one use of OpenArgs http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

The article states that macro can reference the OpenArgs property but I don't know how that can serve your situation.

[gumbi17]

Ok OpenArg is a no go, it would not work with my form! Any other suggestions????

[June7]

Not if you want to stick with macros, I just don't know them well enough. Unless you want to go with form/subform approach - no code required.

[gumbi17]

What do you mean form/sub-form? Do you mean take out the subform and just leave it as a from?

[June7]

This is what I mean http://office.microsoft.com/en-us/ac...010098674.aspx

[gumbi17]

Ahhh it is a form with a subform in it but looking at it I am not sure I have a subform control, I am not seeing it on the list. Which table should it link to, the one I want it to go to or the one I am getting the number from?

[June7]

The main form would be Well Master, the subform would be for the Memos.

A subform container control can have a SourceObject of table, query, form, report.

Master/Child links properties of the subform container control will synchronize the related records.

[gumbi17]

so I never even noticed the Master/Child link before, this is what I have currently and I should move the Master to Well master and Child to Memos is that correct? I have never really understood the Master/child relationship on forms!

[June7]

Not sure I follow that. The first form (/Well Zones/PUDS/Platforms) in your original post is the primary (parent, master)? You want Memos (child, dependent) related to the records of that form? Put [MEMO FORM LEASE Well] on the primary.

LEASE NUM1 is the PK id for wells? And Memos uses that as FK?


BTW, advise no spaces, special characters/punctuation (underscore is exception) in any names.

[Missinglinq]

...it is a form with a subform...I am not sure I have a subform control

If you have a Subform on your Form, you do, in fact, have a Subform Control; it's the only way this can be done!

...this is what I have currently and I should move the Master to Well master and Child to Memos is that correct?...

No! Assuming that 'Lease Num1' is the name of the Autonumber Field in the Well Table, and also the name of a Number Field, in the Memos Table, the Link Master Fields/Link Child Fields Properties are set as they should be! The Access Gnomes will now use this setup to automatically keep the two Tables synchronized, no code needed!

While Macros in 2007/2010 are more powerful than in previous versions, and now include limited Error Handling, they still offer far less flexibility, in what can be done and how it can be accomplished. The use of them also makes it more difficult to understand what is being done, and, once again, how it is being done, compared to VBA code.

Using VBA, you can go into a Form's code module and view the majority of the Commands used to power the Form.

Using Embedded Macros, in order to view what is being done, as well as how it is being done, requires going, one at a time, into each and every Macro, in order to view what to me, with over a decade of experience developing Access apps, is a series of unrecognizable Commands, methods and qualifiers.

Six years into these 'new and improved' Macros, very, very few of the experienced developers, here, or on any of the other half-dozen Access forums I contribute to, utilize Macros, with the possible exception of Autoexec, a specialized Macro that runs when an Access Database first fires up.

I'm afraid if you want to learn to write truly powerful Access databases, you're going to have to bite the bullet and learn VBA, especially if you want to learn to do so utilizing help from Access forums.

Linq ;0)>

[gumbi17]

To clarify the Wells Zones/Puds is just the title of the form it is not what the form is actually named, and it is the main form with the button to launch the notes, it is named Well Master, yes I know a space it was before I got there. I have been slowly updating to get it web ready, they had so many "/" before I got here. The primary key for the Well Master is Well and the autonumber for the memos is called counter.

[gumbi17]

Missinglinq....thats what I was afraid to hear about VBA's! Everytime I seem to write any code in VBA it never really does what I want it to! What do you recommend to start learning VBA then, I can't really get a lot of time off work to go to any classes are their any good software for learning?