2 Forms Updating 1 Table

[Guest]

I am buidling two forms (form1 and form2). They both update the same table
but form2 is activated (via a macro) from form1 when a particular field is
populated. My problem is that I cannot get form 2 (when activated) to open
up at the same record as what is being updated in form1. It defaults back to
the first record in the table and I need it to open up at the current record
I was updating in form1. I'd really appreciate some help.

 

[Wayne Morgan]

To do this, the record you are "updating" has to already exist. You can't be
creating a new record. Next, in the DoCmd.OpenForm command there is an
option to pass a filter.

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

The WhereCondition argument is the one you are after. Pass the unique ID
field of the record in this argument.

Example:
"IDField=" & Me.txtID
if the value is a string instead of a number
"IDField='" & Me.txtID & "'"

Another option is to pass this value in the OpenArgs argument, then in the
Load event of the second form, use this argument to move to the desired
record. The OpenArgs argument accepts a string value, so you may have to
pass the value as a string, even if it is a number, then convert it back in
the second form.

Example:
Me.Recordset.FindFirst "IDField=" & CLng(Me.OpenArgs)

 

[Guest]

Wayne,
Thank you very much for that response. The problem however is that I am
creating a new record each time I update the form, or that is what I hope to
do. Does this mean it is not possible?

 

[Guest]

Wayne,
Sorry, but just to clarify. The unique ID does already exist because I have
assigned it in the table, the rest of the fields however are those that need
populating by means of these two forms.

So, in saying so the instructions you gave should then apply. However, your
answer highlighted how little I know about syntax and 'terminology' in ACCESS.

Could you clarify what is the DoCmd.OpenForm. I recognised the (FormName,
View, FilterName, WhereCondition, DataMOde, WindowMode, OpenArgs) to be the
action arguments in the design fiew of the macro I created to open the form.
Is this what you meant?

If so, I tried adding the unique ID field in the Where Condition of the
Macro however your sytax in your example "IDField='" & Me.txtID" confused
me. When applying this I got a mesage to enter a parameter but upon entering
the correct ID to open up the second form on the same record it was
unsuccessful. I am thinking I may have got the sytax wrong or in fact the
DoCmd.OpenForm is not actually the same as updating the action arguments in
the design view of the macro but something else? Hope you can help

 

[John Vinson]

I am buidling two forms (form1 and form2). They both update the same table
but form2 is activated (via a macro) from form1 when a particular field is
populated. My problem is that I cannot get form 2 (when activated) to open
up at the same record as what is being updated in form1. It defaults back to
the first record in the table and I need it to open up at the current record
I was updating in form1. I'd really appreciate some help.

Why two forms?

If you have two forms open at the same time, editing the same record,
any attempt to update the record from the second-opened form WILL FAIL
with an error message: "another user has the record locked for
editing". The other user is, of course, you yourself (on the other
form).

It sounds like an unnecessarily complex operation for the user, as
well! You're only editing one record; what benefit comes from having a
second form to do so, that could not be attained just as well by
having (say) a Tab Control on the form, with some controls on the
first tab page, and the remaining ones on the second?

John W. Vinson[MVP]

 

[Guest]

John,
Thanks for your response to this. Yes I have two forms but the fields I am
updating on each form are different although updating the same record. The
reason for the two.. well actually there are more like 9 forms updating the
same record form1 being the main form, and the others are only activated if a
particular value is populated within a field. The reason for doing is this
way is because not all fields are required to be populated, and therefore
only the relevant forms for the chosen value are opened and populated. I
don't get that error because I am not updating fields twice. You mentioned
putting some tabs etc in. Do you mean you can have several pages in the one
form? If that is the case, I didn't know it was possible. Would appreciate
your feedback on this one.
Cheers

 

[Rick Brandt]

John,
Thanks for your response to this. Yes I have two forms but the fields
I am updating on each form are different although updating the same
record. The reason for the two.. well actually there are more like 9
forms updating the same record form1 being the main form, and the
others are only activated if a particular value is populated within a
field. The reason for doing is this way is because not all fields are
required to be populated, and therefore only the relevant forms for
the chosen value are opened and populated. I don't get that error
because I am not updating fields twice. You mentioned putting some
tabs etc in. Do you mean you can have several pages in the one form?
If that is the case, I didn't know it was possible. Would appreciate
your feedback on this one.
Cheers

The TabControl is available from the same Toolbox bar as the TextBox, ComboBox,
etc.. Just drag one onto your form and add as many pages as you need and then
put your controls onto the pages. Existing controls must be cut and pasted,
while new controls can be dragged onto the pages.

 

[Wayne Morgan]

Angeline,

Rick's idea of a tab control would probably be much simpler than opening a
bunch of forms since they are all related to the same record.

To answer your question, the examples I gave were VBA, not macros. The macro
probably won't recognize "Me". "Me" is shorthand for the form or report that
the code is running on (i.e. in the form's or report's module). A macro is a
separate entity, so you'll need to give the "path" to the form.

[IDField]=[Forms]![frmForm1]![txtID]

In the macro, whether the field is text or numeric doesn't appear to need a
syntax change. I just tried this with both types of values and it worked.