Hold value in field, and open form to new record from an "open for

[Guest]

I am trying to do a couple of things with field and control on a form, rather
like a switchboard. Basically, I want my users to sign in and the
UsersSignIn value to populate the Users field in 2 other forms. After that, I
want them to click a button which will open the main form and go to a new
record.

I can figure out how to open the form to new record in a macro, but I cannot
get the fields to hold the value in a macro, and I can’t figure out how to do
all the requests in VBA. Mainly because I am not a programmer, and none of
my IT people know VBA. I am sorry to ask, but can you help me figure out the
code, and draw pictures since I am an idiot??

The tables and fields are:

Form Function Form Name Field Result
Open Form Users UsersSignIn Value in Field populates next 2
forms
Main Form IB Performance Users Holds value from UsersSignIn field above
Sub Form Accounts subform Users Holds value from UsersSignIn field above

Command Button OpenFormCommand3 Should open IB Performace to new record &
allow the above to hold true as well


The code I am using so far is:

Private Sub Combo1_Change()
If Form_User.Combo1.Text <> "" Then
Set [Form_IB Performance].User.Text = Form_User.Combo1.Text
Set [Form_Accounts subform].User.Text = Form_User.Combo1.Text
End If
End Sub

Private Sub UsersSignIn_Click(Cancel As Integer)
On Error GoTo Err_Combo1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "IB Performance"

stLinkCriteria = "[UsersSignIn]=" & Me![User]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_UsersSignIn_Click:
Exit Sub

Err_UsersSignIn_Click:
MsgBox Err.Description
Resume Exit_UsersSignIn_Click
End Sub

Private Sub UsersSignIn_Click()

End Sub

 

[tina]

i don't have a clear picture of what you're doing, but i'll try to give a
hand. let's look at your first posted procedure:

Private Sub Combo1_Change()
If Form_User.Combo1.Text <> "" Then
Set [Form_IB Performance].User.Text = Form_User.Combo1.Text
Set [Form_Accounts subform].User.Text = Form_User.Combo1.Text
End If
End Sub

okay, first, if you're not referring to the Text property of the various
controls for a specific reason, then remove that reference. the default
property of most controls is .Value; that's the property you want to read or
populate in most cases and, because it's the default property, you don't
have to refer to it explicitly.
second, you only "set" the value of an object variable, not control objects
in a form, so the "Set" word has to go.
third, are [Form_IB Performance] and [Form_Accounts subform] both open when
this code runs? if not, you can't set the value of fields in their
underlying tables this way; you'll have to 1) open the forms, or 2) run an
Append or Update query on the underlying table(s), or 3) add/edit the table
data by opening a Recordset in VBA.

assuming that those two forms *are* open, try changing your code to

If Len(Me!Combo1 & "") > 0 Then
Forms![Form_IB Performance]!User = Me!Combo1
Forms![Form_Accounts subform]!User = Me!Combo1
End If

okay, to move on to the second post procedure:

Private Sub UsersSignIn_Click(Cancel As Integer)
On Error GoTo Err_Combo1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "IB Performance"

stLinkCriteria = "[UsersSignIn]=" & Me![User]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_UsersSignIn_Click:
Exit Sub

Err_UsersSignIn_Click:
MsgBox Err.Description
Resume Exit_UsersSignIn_Click
End Sub

i don't really know what your intent is here. what the code is actually
doing (or trying to do) is open form [IB Performance], and filter the
records to include only those where the [UsersSignIn] field is equal to
Me!User. if what you want to do instead is to open the form, and transfer
the value of Me!User to a UsersSignIn field in the form, then try using the
OpenForm action's OpenArgs argument to pass the value of Me!User to form [IB
Performance] (read up on the OpenForm action so you'll understand how the
arguments work). then use that form's Load event to assign the value of
Me.OpenArgs to the field in that form.

finally, i noticed that in your first procedure you referred to form
[Form_IB Performance]. the brackets indicate that the form is actually named
"Form_IB Performance". in your second procedure, you referred to "IB
Performance", and used that as the form name argument in the OpenForm
action. if you're referring to the *same form* in both procedures, then you
need to be clear on what the form is actually named, and use that name when
you refer to it.

hth

I am trying to do a couple of things with field and control on a form, rather
like a switchboard. Basically, I want my users to sign in and the
UsersSignIn value to populate the Users field in 2 other forms. After that, I
want them to click a button which will open the main form and go to a new
record.

I can figure out how to open the form to new record in a macro, but I cannot
get the fields to hold the value in a macro, and I can't figure out how to do
all the requests in VBA. Mainly because I am not a programmer, and none of
my IT people know VBA. I am sorry to ask, but can you help me figure out the
code, and draw pictures since I am an idiot??

The tables and fields are:

Form Function Form Name Field Result
Open Form Users UsersSignIn Value in Field populates next 2
forms
Main Form IB Performance Users Holds value from UsersSignIn field above
Sub Form Accounts subform Users Holds value from UsersSignIn field above

Command Button OpenFormCommand3 Should open IB Performace to new record &
allow the above to hold true as well


The code I am using so far is:

Private Sub Combo1_Change()
If Form_User.Combo1.Text <> "" Then
Set [Form_IB Performance].User.Text = Form_User.Combo1.Text
Set [Form_Accounts subform].User.Text = Form_User.Combo1.Text
End If
End Sub

Private Sub UsersSignIn_Click(Cancel As Integer)
On Error GoTo Err_Combo1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "IB Performance"

stLinkCriteria = "[UsersSignIn]=" & Me![User]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_UsersSignIn_Click:
Exit Sub

Err_UsersSignIn_Click:
MsgBox Err.Description
Resume Exit_UsersSignIn_Click
End Sub

Private Sub UsersSignIn_Click()

End Sub