Form Help

[cmccarroll@verizon.n]

I have several forms in an Access db and have a command button that closes the current form and opens the next form. I need the command button to open the next form with the current form's user ID in a new record for the next form.

tia,
cmc

 

[Premy]

How about:

Sub btnClose_Click()
Dim uzerID As Integer
uzerID = Me.UserID
DoCmd.OpenForm "Form2", , , , , , uzerID
Docmd.close acForm, me.name
End Sub

Now Form2:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

'Obviously, the previous code is only necessary if your
'form's DataEntry prop has been set to No.

Private Sub Form_Current()
Me.UserID = Me.Openargs
End Sub

As u can see, I just passed the user id from form1 to form2's OpenArgs.

HTH
Premy

 

[cmccarroll@verizon.n]

Thanks very much for the reply. I have to admit to being a VBS newbie (although I am a long time programmer in other langs), so I am not sure exactly how to deploy your code. If you wouldn't mind being a little more specific...

This codes works as suspected:

-----------------------------------------------------------------------------------

Sub btnClose_Click()
Dim uzerID As Integer
uzerID = Me.UserID
DoCmd.OpenForm "Form2", , , , , , uzerID
Docmd.close acForm, me.name
End Sub

-----------------------------------------------------------------------------------

Not sure how or where to utilize the following code:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub Form_Current()
Me.UserID = Me.Openargs
End Sub

tia,
cmc

 

[ajetrumpet]

I need the command button to open the next form with the current form's user ID in a new record for the next form.

Here's another idea you can try if you want...

Private Sub YourButton_Click

  DoCmd.OpenForm "NextForm"

  [b][color=red]'Get the ID value from the previous form that is still open and active[/b][/color]
  DoCmd.GoToRecord "NewlyOpenedForm", , , acNewRec
    NewlyOpenedForm.IDcontrol = Me.IDcontrol

  DoCmd.Close

End Sub

 

[Premy]

Ok, let's do it again

This is the code for your command button on the 1st form:
Sub btnClose_Click()
Dim uzerID As Integer
uzerID = Me.UserID
DoCmd.OpenForm "Form2", , , , , , uzerID
Docmd.close acForm, me.name
End Sub

The openform method takes several arguments (check vba help for details on openform, or any method, for that matter), the last being the OpenArgs method. In Vba, arguments are separated by comma. If u leave an argument blank, the default value for that arg will be used. OpenArgs is just a way to pass a parameter/argument to the form/report being opened. Here uzerID gets the value of your underlying table's user id, the one u would like to enter in the new record on the 2nd form, right?

Now Form2:

The following procedure is your 2nd form's Load_Event procedure (just paste and copy it in the 2nd form's module). the GoToRecord method will take u directly to a new row (record) of the underlying table. This code is not necessary if you set your form's DataEntry property to yes, since this will automatically take you to a new record when you open the form.
Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

Now we're already on a new row in the recordset. The form_current event procedure fires whenever you change from one record to another (again see vba help for details), since we moved to a new record, it will fire and force your UserID field to asssume the value passed into the form's OpenArgs (remember?).
Private Sub Form_Current()
Me.UserID = Me.Openargs
End Sub

Hope you're less confused now.
regards,
premy

 

[cmccarroll@verizon.n]

Once again thanks again for the help, all is well with my request. However, upon further review, what I really need is for the next form to check for a previous record for the linked table based upon userID. Since I am new to VBS I'll write the code in a way I hope displays my meaning.

if ($table.userID == $userID) {
select * from $table where userID='$userID'
}

else {
DoCmd.GoToRecord , , acNewRec
}

Please explain where the code should be inplemented, ie, onLoad or some other function. Also is there a way to have a form display one record only?

tia,
cmc

 

[Premy]

OK, did u get the OpenArgs thing? If so, u can do:

Private Sub Form_Current()
Dim rs As Object
Me.Filter = ""
Set rs = Me.Recordset.Clone
rs.FindFirst "[UserID] = " & Me.OpenArgs
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
DoCmd.GoToRecord , , acNewRec
Me.UserID = Me.Openargs
End If
End Sub

If UserID is a string rather than an integer, then the querystring changes a bit:
rs.FindFirst "[UserID] = '" & Me.OpenArgs & "'"
In that case uzerID datatype in the 1st form should be string too, of course.
This code will also just retrieve a single record, if a match is found.

U can just copy and paste the code in your form's module: this one in form2's module and the button code in form1's module. Just make sure you activate the form_current event in form2's form properties, and the btnClose_Click event in the button's properties.

HTH
regards
Premy

 

[cmccarroll@verizon.n]

I placed the following code in an EventProcedure in the On Current section of form2's events. I changed your code, so that I could see if the bookmark criteria was being properely read. I have verified that the fid (userID) has one record with the correct fid in the linked table to form2.

Private Sub Form_Current()

strCriteria = "[fid] = " & Me.OpenArgs

Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox strCriteria
Else
Me.Bookmark = rst.Bookmark
End If

End Sub

The btnClose_Click event from form1 does properely pass the "fid" (userID) to form2, and form2 will let me add a new record with the "fid" from form1; however, it will not allow me to goto the record in form2 that has the correponding "fid" from form1.

tia,
cmc

 

[Premy]

Well the code in my example is ok. Did u run it like that? Did u step thru the code to debug it? Is userid integer or string?

 

[cmccarroll@verizon.n]

Once again thanks for your help. If you can't help me resolve, I certainly understand. The userid is an integer. I tried it your way and got no error, then I changed it to see if the userid was being properely passed.

 

[ajetrumpet]

Did you get any resolution with this Carroll? A translation for this...

if ($table.userID == $userID) {
select * from $table where userID='$userID'
}

else {
DoCmd.GoToRecord , , acNewRec
}

...in Visual Basic, would be this...

DoCmd.OpenForm "NextForm", , "NextForm'sTable.UserID = Me.UserID (or just [ID] if ID is a control bound to a field")

DoCmd.Close

And actually, this code should be put with a button, if you're still trying to open the next form and close the current one. I read your code (What is that, VB written in JAVA form!?) like this...

"If the current form's UserID is in the next form's table, open the next form filtered on that ID. If the ID is not in the next form's table, give me a new record."

The translation I gave has a WHERE clause in it. The default behavior of the clause is to open the form to a blank record if there is no filter value found. Thus, this blank record can be used as a new one. This default can be substituted for writing...

Else: DoCmd.GoToRecord, , asNewRec

Also is there a way to have a form display one record only?

Yes, but there are few specifications. Under what circumstances are you wanting to do it? Which "one record" would you want?