By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,587 Members | 1,168 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,587 IT Pros & Developers. It's quick & easy.

Use selected record in Form B (acDialog Form) as go to criteria in Form A

P: 10
Howdy all,

I have a form for entering data (Form A), on which is a cmd button that opens Form B in acdialog mode to allow me to enter/edit records that are being displayed on Form A.

After adding/editing a record on Form B (opened in acDialog mode), I want a cmd button on Form B to close Form B and then have Form A automatically take me to the record that was added/edited in Form B (Form A has a subform that I will then enter data into).

My experience with VBA is very limited so please provide details and descriptions.

My cmd button on Form B currently does everything I want it to do except for this issue. The code for the cmd button on Form B currently is:

************************************************** *
Private Sub cmdCloseEventForm_Click()
On Error GoTo Err_cmdCloseEventForm_Click

'Set criteria record for frm_Camas_Data upon return
'*** STILL WORKING ON THIS--HELP

DoCmd.Close
'Refresh frm_Camas_Data
Forms!frm_Camas_Data.Requery
'Match cboEventName to Event Name
Forms!frm_Camas_Data!cboFindEventAll = Forms!frm_Camas_Data![EventName]

Exit_cmdCloseEventForm_Click:
Exit Sub

Err_cmdCloseEventForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseEventForm_Click

End Sub
************************************************** ******

Thanks for the help!!!
Nov 24 '06 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,442
I'm not sure exactly what's wrong here, but my first instinct would be to move the 'Requery' after the line where you set up 'Forms!frm_Camas_Data!cboFindEventAll'.
Please ignore this advice (see below) - it's wrong.
Nov 25 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm not sure exactly what's wrong here, but my first instinct would be to move the 'Requery' after the line where you set up 'Forms!frm_Camas_Data!cboFindEventAll'.
No the order is right.

The Form A (frm_Camas_Data) has to be requeried before the new record can be found.

To add the record you need to insert it into the table or query behind the aforementioned form.

eg.

Dim strSQL As String

strSQL = "INSERT INTO TableOrQueryName (Field List separated by commas) VALUES (" & Me.Value1 & ", " & Me. Value2 ... etc. & ");"

DoCmd.RunSQL
Nov 25 '06 #3

NeoPa
Expert Mod 15k+
P: 31,442
Oh yes - my bad.
I understand the logic now - thx.
Nov 25 '06 #4

P: 10
Thanks for the respones but I don't think this is what I'm trying to make happen.

What needs to happen is that when the cmd button in Form B is clicked, Form B needs to close (which is does) and then in Form A (which remained open in the background since Form B was opend with acDialog) I want the form to automatically be updated with the record that was either edited/added when in Form B.

In other words, add record XYZ in Form B, click on cmd button and return to Form A with Form A showing record XYZ. Or edit record XYZ in Form B, click on cmd button and return to Form A with record XYS showing.

I would also add that the current code DOES update the records in Form A, thus I'm not sure the suggested code you provided is necessary.

Any other thoughts?

Thanks again!
Nov 25 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Forms are just a visual interface for the records in the tables and queries they are based on.

If Form B adding or updating the same record source then you just need to requery form A and use the DoCmd.FindRecord or DoCmd.GoToRecord.

You will have to set focus on Form A. Use one of the above to go to the record based on unique value on Form B while its still open then close Form B.


Thanks for the respones but I don't think this is what I'm trying to make happen.

What needs to happen is that when the cmd button in Form B is clicked, Form B needs to close (which is does) and then in Form A (which remained open in the background since Form B was opend with acDialog) I want the form to automatically be updated with the record that was either edited/added when in Form B.

In other words, add record XYZ in Form B, click on cmd button and return to Form A with Form A showing record XYZ. Or edit record XYZ in Form B, click on cmd button and return to Form A with record XYS showing.

I would also add that the current code DOES update the records in Form A, thus I'm not sure the suggested code you provided is necessary.

Any other thoughts?

Thanks again!
Nov 25 '06 #6

P: 10
This makes sense but how do I use criteria (the specific record value from one f the fields) from Form B (which closes) as the criteria in the FindRecord or GoTo commands. This is really the problem that I can't figure out. Is there a way to store the value of the Form B field in a temporary holding field so that it can then be used to find the identified record in Form A.

Please bear with me as my experinence with VBA is limited at best.
Nov 25 '06 #7

NeoPa
Expert Mod 15k+
P: 31,442
There is only one way that I can think of that provides what you're after.
Design a public function in a (ordinary) module which allows you to set AND get the values you need.
I have one you're welcome to use but it was designed for sharing an unspecified number of parameters with a report.
Expand|Select|Wrap|Line Numbers
  1. 'RptParms sets and returns a set of parameters required by a report.
  2. 'Call with intSetGet=0 to set various parameters.
  3. 'Call with intSetGet>0 to return an individual parameter.
  4. Public Function RptParms(intSetGet As Integer, _
  5.                          ParamArray avarParams() As Variant) As Variant
  6.     Static avarParms() As Variant
  7.     Dim intIdx As Integer
  8.  
  9.     RptParms = 0
  10.     If intSetGet = 0 Then
  11.         intSetGet = UBound(avarParams) + 1 - LBound(avarParams)
  12.         If intSetGet < 1 Then
  13.             ReDim avarParms(1 To 1)
  14.             avarParms(1) = "Error"
  15.             Exit Function
  16.         End If
  17.         ReDim avarParms(1 To intSetGet)
  18.         For intIdx = 1 To intSetGet
  19.             avarParms(intIdx) = avarParams(intIdx - 1)
  20.         Next intIdx
  21.     Else
  22.         'If outside bounds then it drops through and is set to "Error"
  23.         On Error Resume Next
  24.         If avarParms(intSetGet) = "Error" Then
  25.             RptParms = "Error"                  'On Error
  26.         Else
  27.             RptParms = avarParms(intSetGet)
  28.         End If
  29.     End If
  30. End Function
Nov 26 '06 #8

P: 10
SOLUTION!

I must admitt that I couldn't follow the above advice, however, after much trial and error here is the solution that worked to solve the problem.
__________________________________________________ _________
Private Sub cmdCloseEventForm_Click()
On Error GoTo Err_cmdCloseEventForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Save

stDocName = "frm_Camas_Data"
stLinkCriteria = "[EventName]='" & Me.[EventName] & "'"

'This command updates database with current record
Me.Recordset.Move (0)

'Opens frm_Camas_Data and goes to last record displayed in frm_Events
DoCmd.OpenForm stDocName, , , stLinkCriteria

'Close frm_Events
DoCmd.Close acForm, "frm_Events", acSaveYes

Exit_cmdCloseEventForm_Click:
Exit Sub

Err_cmdCloseEventForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseEventForm_Click

End Sub
__________________________________________________ ___________
Dec 28 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.