populating record on different forms

[Guest]

I do not want to have form and subform to be displayed at the same time,
that's why I am trying to link my table/form to each other, as you told I
linked medical record field on main table to all other tables with same field
but still now I can not populate the value of medical record number on all of
my tables/forms
Please help, I am sick of it

 

[Joan Wild]

If you don't want a main form/subform arrangement, you can have a command
button that opens the related form. If you used the form wizard, it will
even offer you this choice.

After you select your main form and fields in step 2 of the wizard, just
select your second table (and its fields), before hitting next - the wizard
will offer you a choice of mainform/subform or a command button.

 

[Guest]

I have already created the command buttons to oprn my next from but what I
can not do is populating a value which is similar on all forms. I mean I have
a field which is similar on all tables/forms. I just want that user input the
value on the first form and then it automatically goes the all further forms,
no need to reenter it on every form

 

[Joan Wild]

Change your command button click code to
DoCmd.OpenForm "form name", , , "[SomeField] = " & Me![SomeField]
That assumes that SomeField is a number. If text then
"[SomeField] = " & ch4(34) & Me![SomeField] & chr(34)

 

[Guest]

My command buttons run a macro which close the current form and opens the
next form how can I make this modification on the macro. the name of the
field is 'Medical rcord #" which is number, please comment

Change your command button click code to
DoCmd.OpenForm "form name", , , "[SomeField] = " & Me![SomeField]
That assumes that SomeField is a number. If text then
"[SomeField] = " & ch4(34) & Me![SomeField] & chr(34)


--
Joan Wild
Microsoft Access MVP

I have already created the command buttons to oprn my next from but
what I can not do is populating a value which is similar on all
forms. I mean I have a field which is similar on all tables/forms. I
just want that user input the value on the first form and then it
automatically goes the all further forms, no need to reenter it on
every form

 

[Guest]

I don't really understand your aversion to sub forms, but you have two issues
to deal with. One is selecting a medical record number that already exists
in the other tables so that the related records are presented. The other is
when a new medical record has been entered in the first table, but has no
records in the related tables.

I would suggest you use the OpenArgs argument of the OpenForm method in the
first form to pass the value of the current medical record number to the form
you are opening. Then in the Load event of the opened form, set the form's
filter to that value. Check to see if the form's recordset has any records.
If it does not, that means this is a new record. Then goto a new record and
populate the medical record number control on the form.

 

[Guest]

I am quite amateur, please explain how I can create such an argument that
populates every new records on medical record # field on further forms. I
have 7 forms and I want user enters the value on the first form and then it
automatically goes to further forms

 

[Guest]

When you open the secord form:

Docmd.OpenForm "SecondForm", , , , , ,Me.txtMedicalRec

In the line above, txtMedicalRec would be the medical record number that is
on the first form that you want to use on the second form.

Now in the Open event of the second form:

If Not IsNull(Me.OpenArgs) Then
Me.Filter = "[MedicalRecordNumber] = '" & Me.OpenArgs & "'"
Me.FilterOn = True
If Me.Recordset.RecordCount = 0 Then
DoCmd.GoToRecord , , acNewRec
Me.txtMedicalRecord = Me.OpenArgs
End If
End If

In the code above, [MedicalRecordNumber] would be the field in the form's
record source that has the medical record number. txtMedicalRecordNumber
would be the control on the form that is bound the the medical record number
field.

 

[Joan Wild]

Why not use the WhereCondition argument?

--
Joan Wild
Microsoft Access MVP

When you open the secord form:

Docmd.OpenForm "SecondForm", , , , , ,Me.txtMedicalRec

In the line above, txtMedicalRec would be the medical record number
that is on the first form that you want to use on the second form.

Now in the Open event of the second form:

If Not IsNull(Me.OpenArgs) Then
Me.Filter = "[MedicalRecordNumber] = '" & Me.OpenArgs & "'"
Me.FilterOn = True
If Me.Recordset.RecordCount = 0 Then
DoCmd.GoToRecord , , acNewRec
Me.txtMedicalRecord = Me.OpenArgs
End If
End If

In the code above, [MedicalRecordNumber] would be the field in the
form's record source that has the medical record number.
txtMedicalRecordNumber would be the control on the form that is bound
the the medical record number field.

I am quite amateur, please explain how I can create such an
argument that populates every new records on medical record # field

 

[Guest]

The Where condition would work, but I think for a newbie, it is easier coding
to use the OpenArgs. He doesn't have to worry about the syntax to build a
filter based on data type, for example.

Why not use the WhereCondition argument?

--
Joan Wild
Microsoft Access MVP

When you open the secord form:

Docmd.OpenForm "SecondForm", , , , , ,Me.txtMedicalRec

In the line above, txtMedicalRec would be the medical record number
that is on the first form that you want to use on the second form.

Now in the Open event of the second form:

If Not IsNull(Me.OpenArgs) Then
Me.Filter = "[MedicalRecordNumber] = '" & Me.OpenArgs & "'"
Me.FilterOn = True
If Me.Recordset.RecordCount = 0 Then
DoCmd.GoToRecord , , acNewRec
Me.txtMedicalRecord = Me.OpenArgs
End If
End If

In the code above, [MedicalRecordNumber] would be the field in the
form's record source that has the medical record number.
txtMedicalRecordNumber would be the control on the form that is bound
the the medical record number field.

I am quite amateur, please explain how I can create such an
argument that populates every new records on medical record # field
on further forms. I have 7 forms and I want user enters the value on
the first form and then it automatically goes to further forms

:

I don't really understand your aversion to sub forms, but you have
two issues to deal with. One is selecting a medical record number
that already exists in the other tables so that the related records
are presented. The other is when a new medical record has been
entered in the first table, but has no records in the related
tables.

I would suggest you use the OpenArgs argument of the OpenForm
method in the first form to pass the value of the current medical
record number to the form you are opening. Then in the Load event
of the opened form, set the form's filter to that value. Check to
see if the form's recordset has any records. If it does not, that
means this is a new record. Then goto a new record and populate
the medical record number control on the form.

:

I have already created the command buttons to oprn my next from
but what I can not do is populating a value which is similar on
all forms. I mean I have a field which is similar on all
tables/forms. I just want that user input the value on the first
form and then it automatically goes the all further forms, no need
to reenter it on every form

:

If you don't want a main form/subform arrangement, you can have a
command button that opens the related form. If you used the form
wizard, it will even offer you this choice.

After you select your main form and fields in step 2 of the
wizard, just select your second table (and its fields), before
hitting next - the wizard will offer you a choice of
mainform/subform or a command button.


--
Joan Wild
Microsoft Access MVP

Ramtin wrote:
I do not want to have form and subform to be displayed at the
same time, that's why I am trying to link my table/form to each
other, as you told I linked medical record field on main table
to all other tables with same field but still now I can not
populate the value of medical record number on all of my
tables/forms
Please help, I am sick of it

 

[John Vinson]

Why not use the WhereCondition argument?

That will work for EXISTING records in the related tables, but will
not create new records nor will it copy the value into a new record.

Another alternative is to set the DefaultValue property of the ID
field in the second form's Open event.

John W. Vinson[MVP]

 

[Guest]

The Where Argument could be made to work much like my OpenArgs solution;
however, you would then have the additional task of changing the form's
filter for a new record. I do like the Default value idea, it would allow
the user to continue entering new related records without having to reenter
the medical record number. So, to amend the original code:

If Not IsNull(Me.OpenArgs) Then
Me.Filter = "[MedicalRecordNumber] = '" & Me.OpenArgs & "'"
Me.FilterOn = True
If Me.Recordset.RecordCount = 0 Then
DoCmd.GoToRecord , , acNewRec
Me.txtMedicalRecord.DefaultValue = Me.OpenArgs
End If
End If

 

[John Vinson]

Me.txtMedicalRecord.DefaultValue = Me.OpenArgs

minor correction; the DefaultValue property must be a String, so it
should be delimited with quotemarks:

Me.txtMedicalRecord.DefaultValue = "'" & Me.OpenArgs & "'"

John W. Vinson[MVP]