VBA Routine: Go to related record; if none, then create one

[amywolfie]

Hi:

I'm used to FileMaker, where it's very easy to script GTTR; if there
is none, then create it, and pass the PK.

What would be the VBA equivalent?

I'm trying to get to:

Open Form where fk = PK on another form
IF [fk] Is Null, Then
Create new record in form and
Pass PK in Form 1 to fk.

Thanks!
====

 

[John W. Vinson]

Hi:

I'm used to FileMaker, where it's very easy to script GTTR; if there
is none, then create it, and pass the PK.

What would be the VBA equivalent?

I'm trying to get to:

Open Form where fk = PK on another form
IF [fk] Is Null, Then
Create new record in form and
Pass PK in Form 1 to fk.

Thanks!
====

Access is not a flawed implementation of FileMaker.

Both Access and FileMaker are very capable database programs, BUT THEY ARE
DIFFERENT. Trying to use Filemaker syntax in an Access database will be a
guaranteed source of frustration (I can say that the opposite is true as well,
from personal painful experience!)

The Access solution would use no script and no code at all; you would simply
make the second form a Subform of the first, using the PK as the Master Link
Field and the fk as the Child Link Field.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[amywolfie]

John:

No disrespect meant to either app -- they both have their strengths &
flaws.

As far as creating a subform:

Both the main form and subform contain A LOT of data, and need to be
full screen.

Again, I'd like to GTRR when PK = fk

If no fk in Form 2, then create a new record there and pass the PK
from Form 1

Thanks Again!

Amy
===

Hi:

I'm used to FileMaker, where it's very easy to script GTTR; if there
is none, then create it, and pass the PK.

What would be the VBA equivalent?

I'm trying to get to:

Open Form where fk = PK on another form
IF [fk] Is Null, Then
Create new record in form and
Pass PK in Form 1 to fk.

Thanks!
====

Access is not a flawed implementation of FileMaker.

Both Access and FileMaker are very capable database programs, BUT THEY ARE
DIFFERENT. Trying to use Filemaker syntax in an Access database will be a
guaranteed source of frustration (I can say that the opposite is true as well,
from personal painful experience!)

The Access solution would use no script and no code at all; you would simply
make the second form a Subform of the first, using the PK as the Master Link
Field and the fk as the Child Link Field.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

 

[Access Developer]

Ah, I am beginning to understand... as John says, Access is not Filemaker
and Filemaker is not Access... so describing what you want to accomplish in
terms of how you would do it in Filemaker is a self-defeating. If you
describe what you want to accomplish in terms of what you want to see /
happen may allow us to give you guidance on "how-to" do it in Access.

A phrase I've never used in discussing Access since it was introduced in
1993 is "code GTTR", but, if I understand what you want to happen, John's
already told you that you can do it, with no code, using Access' Subform
Control into which you can embed a Form. It's nothing special for the Form
in the Subform to display a matching record if one exists or allow creating
one if none exists. And, the Subform Control (and the Form displayed within)
will "go away" when the parent Form is closed.

But a Subform Control exists within a parent Form... if you are opening a
separate Form, as is likely if you want it to fill the screen, that is not
(repeat NOT) a Subform.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


John:

No disrespect meant to either app -- they both have their strengths &
flaws.

As far as creating a subform:

Both the main form and subform contain A LOT of data, and need to be
full screen.

Again, I'd like to GTRR when PK = fk

If no fk in Form 2, then create a new record there and pass the PK
from Form 1

Thanks Again!

Amy
===

Hi:

I'm used to FileMaker, where it's very easy to script GTTR; if there
is none, then create it, and pass the PK.

What would be the VBA equivalent?

I'm trying to get to:

Open Form where fk = PK on another form
IF [fk] Is Null, Then
Create new record in form and
Pass PK in Form 1 to fk.

Thanks!
====

Access is not a flawed implementation of FileMaker.

Both Access and FileMaker are very capable database programs, BUT THEY ARE
DIFFERENT. Trying to use Filemaker syntax in an Access database will be a
guaranteed source of frustration (I can say that the opposite is true as
well,
from personal painful experience!)

The Access solution would use no script and no code at all; you would
simply
make the second form a Subform of the first, using the PK as the Master
Link
Field and the fk as the Child Link Field.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

 

[John W. Vinson]

John:

No disrespect meant to either app -- they both have their strengths &
flaws.

As far as creating a subform:

Both the main form and subform contain A LOT of data, and need to be
full screen.

Again, I'd like to GTRR when PK = fk

If no fk in Form 2, then create a new record there and pass the PK
from Form 1

Thanks Again!

Amy
===

You can get the best of both worlds by putting a Tab Control on your main
form; put all the controls you currently have on the first page of the tab,
and the full screen Subform on the second page.

Or you can pop up a second independent form (which is not a "subform" in
Access jargon) in code. This requires two minor bits of code; the command
button's Click event would contain code like

Private Sub cmdSecondForm_Click()
DoCmd.OpenForm "YourSecondFormName", _
WhereCondition:="[fk] = " & Me.PK, _
OpenArgs:= " & Me!PK, _
WindowMode:=acDialog
End Sub

Then in the second form's Open event put code like

Private Sub Form_Open(Cancel as Integer)
Me!fk.DefaultValue = "" & Me.OpenArgs & ""
End Sub

Check out the VBA help on the OpenForm method, I'm condensing things a lot
though this should work.

(btw, no disrespect intended toward Filemaker - just trying to suggest an
alternate mindset which may help!)
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[amywolfie]

John:

I am getting the following error when I apply your first bit of code
to the command button on Form #1:

"2424 The expression you entered has a field, control, or property
name that FieldColelction (d-base name) can't find."

Here is the code:

====================


Private Sub butGoToSatelliteOffices_Click()

On Error GoTo Error_Handler:

DoCmd.OpenForm "frmSatellites", _
WhereCondition:="[County_fk] = " & Me.County_sk, _
OpenArgs:=" & Me!County_sk", _
WindowMode:=acDialog


Exit Sub


Error_Handler:
MsgBox Err.Number & " " & Err.Description



End Sub

===========

I checked the form name, key names -- they are OK. What am I doing
wrong?

Thanks.
====

 

[John W. Vinson]

John:

I am getting the following error when I apply your first bit of code
to the command button on Form #1:

"2424 The expression you entered has a field, control, or property
name that FieldColelction (d-base name) can't find."

Here is the code:

====================


Private Sub butGoToSatelliteOffices_Click()

On Error GoTo Error_Handler:

DoCmd.OpenForm "frmSatellites", _
WhereCondition:="[County_fk] = " & Me.County_sk, _
OpenArgs:=" & Me!County_sk", _
WindowMode:=acDialog


Exit Sub


Error_Handler:
MsgBox Err.Number & " " & Err.Description



End Sub

It's the DoCmd line? Try changing Me.County_sk to Me!County_sk.

What is the name of the county_sk field in your table, and what is the name of
the form control to which that field is bound (if any)?

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[amywolfie]

John:

I am getting the following error when I apply your first bit of code
to the command button on Form #1:

"2424 The expression you entered has a field, control, or property
name that FieldColelction (d-base name) can't find."

Here is the code:

Private Sub butGoToSatelliteOffices_Click()

On Error GoTo Error_Handler:

DoCmd.OpenForm "frmSatellites", _
 WhereCondition:="[County_fk] = " & Me.County_sk, _
 OpenArgs:=" & Me!County_sk", _
 WindowMode:=acDialog

Exit Sub

Error_Handler:
   MsgBox Err.Number & " " & Err.Description

End Sub

It's the DoCmd line? Try changing Me.County_sk to Me!County_sk.

What is the name of the county_sk field in your table, and what is the name of
the form control to which that field is bound (if any)?

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

John:

The field is called County_sk in the table on and the form. It's not
really bound to a control.

Thanks Again!!

Amy
====

 

[amywolfie]

Tried Me!County_sk, but received the same error message.

Thanks.
===

 

[John W. Vinson]

Tried Me!County_sk, but received the same error message.

Thanks.
===

I can't think what might be the problem, and I'm leaving town for a couple of
days. Please post a new thread; include the SQL view of the forms'
Recordsources and your code, I hope someone else can help.

Or you could do the same on the UtterAccess forum in my .sig, it's a good bit
more active than this newsgroup. Mention that you're following up on a thread
here to prevent confusion.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com