Command Button

  • Thread starter Thread starter Debbie
  • Start date Start date
D

Debbie

Ok. I set up my forms using command buttons to go from one form to
another. Can I make it so that when I am in the main form and click on
command button (which is a related form), for it to go directly to the
associated (related) record?
 
Hi Debbie

Yes - no problem :-)

Question is: Do you want to see ONLY the one record in the second form, or
do you want to be able to scroll through all records but start on the given
one?

If it's the first option, then add a WhereCondition to your OpenForm
statement (this is the 4th argument):

DoCmd.OpenForm "Form2", , , "[KeyField]=" & Me.[KeyField]

If you want to scroll all records, pass the key field value via OpenArgs
(the 7th argument) and navigate to the desired record in the second form's
Load event:

DoCmd.OpenForm "Form2", , , , , , Me.[KeyField]

Then, in Form2:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst "[KeyField]=" & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

Note that if your key field is a text field then you must enclose the value
in quotes:

"[KeyField]='" & Me.[KeyField] & "'"

and

"[KeyField]='" & Me.OpenArgs & "'"
 
Excuse me if I am dumb, but I am a little new to codes and stuff. What
does [KeyField] stand for? Is that the name of my form I am trying to
open.
If I choose the second choice, do I go to the event and copy these two
lines. Is there some place on the internet where I can learn more
about VBA coding.
 
Usually, when i choose the command button it is to add a new record to
the form. However, sometimes I just need to view the information in the
form. So I guess I need to setup 2 buttons.
 
This is what it does right now.

Private Sub Command41_Click()
On Error GoTo Err_Command41_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "frmNonEnrolleeParent"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command41_Click:
Exit Sub

Err_Command41_Click:
MsgBox Err.Description
Resume Exit_Command41_Click
 
Hi Debbie

[KeyField] is the name of the primary key field in the table bound to the
form you are opening.
 
Hi Debbie

You have a variable called "stLinkCriteria" but you are not using it for
anything. That is where you put your WhereCondition string. For example:

stLinkCriteria = "[ParentID]=" & Me.txtParentID

Where:
ParentID is the name of the numeric primary key field in your table.
txtParentID is the name of a textbox on your current form which contains
the matching ID value.
 
Help

DoCmd.OpenForm stDocName, , , stLinkCriteria = "[Enrollee.EnrolleeID]="
& Me.txt[NonEnrolleeParent.NonEnrolleeID]

"[Enrollee.EnrolleeID]=" This is the main table and primary key

[NonEnrolleeParent.NonEnrolleeID] This is the form with the ID that I
want to show in form

EnrolleeID is an autonumber from table

NonEnrolleeID is the text value of the EnrolleeID, which is located in
the form that I want to open

This is what I changed

Private Sub Command41_Click()
On Error GoTo Err_Command41_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNonEnrolleeParent"
DoCmd.OpenForm stDocName, , , stLinkCriteria =
"[Enrollee.EnrolleeID]=" & Me.txt[NonEnrolleeParent.NonEnrolleeID]

Exit_Command41_Click:
Exit Sub

Err_Command41_Click:
MsgBox Err.Description
Resume Exit_Command41_Click

End Sub

Does this make sense?
I want to open the form so that it matches the main form (The enrollee
is related to the nonenrollee) Mother (main form) and Father (subform)
 
No, stLinkCriteria is a *string variable* (it stores a string).

"[Enrollee.EnrolleeID]=" & ... is the string you wish to store.

You need this:

stLinkCriteria = "[some field name]=" & [some value]
stDocName = "frmNonEnrolleeParent"
DoCmd.OpenForm stDocName, , , stLinkCriteria

The [some field name] bit is the name of a field in the record source of the
form you are opening.

The [some value] bit is the name of a variable or control (textbox) which
contains the value you wish to match.

You have not given me enough information to know exactly what your setup is,
but I suspect something like this might work:

stLinkCriteria = "[EnroleeID]='" & Me.NonEnroleeID & "'"
 
OK. It now opens; however it only opens to enter new data. It does not
allow me to find or edit the records that are already in the table.
What do I do from here. If I open the form, I would like it to open to
the matching record (I want the (Main form) is the mother and when I
open the (Subform) I want the father to open. If no matching record,
then I want it to open as it does now, to add new data
This is what I have so far:

Private Sub Command41_Click()
On Error GoTo Err_Command41_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNonEnrolleeParent"
DoCmd.OpenForm stDocName, , , stLinkCriteria = "[EnrolleeID]='" &
"Me.NonEnrolleeID" & "'"


Exit_Command41_Click:
Exit Sub

Err_Command41_Click:
MsgBox Err.Description
Resume Exit_Command41_Click

End Sub
 
This is hat I put in LOAD in the subform

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst "[EnrolleeID]='" & Me.OpenArgs & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

End Sub
 
I also will need to be able to find other records in the form for
viewing. Is this possible. Thank you for being so patient. Your job
must be really hard trying to figure out what people are trying to do
without being able to see their work. I do appreciate your time.
 
Hi Debbie

Did you not read my last post? I said that what you should have is:

stLinkCriteria = "[EnrolleeID]='" & Me.NonEnrolleeID & "'"
stDocName = "frmNonEnrolleeParent"
DoCmd.OpenForm stDocName, , , stLinkCriteria

This line that you have:
DoCmd.OpenForm stDocName, , , stLinkCriteria = "[EnrolleeID]='" &
"Me.NonEnrolleeID" & "'"
makes no sense at all!

I'm still not sure I'm on your wavelength though. Can you please post a
summary of your table structures with the names and data types of the
relevant fields, including which is the primary key for each table. Also,
post the recordsource for each of your two forms.
 
Graham,
Thank you so much for your time. With your help, I was able to get it
to do what I wanted. I just had to tweak it a little bit. I love this
stuff. Grant it I am just a novice, but I love to learn. Again, thank
you for your input. I could not have done it with out you getting me
started.
 
Back
Top