open to record on third form - Value From a Form in Microsoft Access


	Back to Value From a Form Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

open to record on third form

Dan Pickle

2 years ago

I have 3 forms I am working with a wire form a logon form and an edit form. In the wire form there is a value "Num" which has a wire number in it IF you want to edit that record you click a button labeled "EDIT" which will load the Logon form. Once the UN and PW are correct then it opens the "edit" form. What I want to happen is for the edit form to get the "num" value from the wire form and open to that specific record. I tried to use the Forms!Form Name! Field name as in the video however I don't know how to get the "edit" form to open with that value. If I put that in the default value If I were to poen a NEW record then yes it would put that value there. If I put it into the onload field for the form how would the form know which field to add the "num" value to it

	Adam Schwanz @Reply 2 years ago
There's a number of ways you could do this such as Tempvars. The most basic way is probably just to put an invisible textbox on your login screen. Say we call it NumText Then when you push edit, you do something like this `DoCmd.OpenForm "LoginF" Forms!LoginF!NumText=Num 'This would be whatever your Num field is` Then when you push the login button you can just do `Forms!EditForm!Num=NumText`

	Kevin Yip @Reply 2 years ago
To open a form and show a specific record, use a filter expression for the OpenForm command: DoCmd.OpenForm "FormName", , , "[MyField]=MyValue" The form will show only the record, or records, that satisfy the criteria [MyField]=MyValue.

	Dan Pickle OP @Reply 2 years ago
Kevin how do I get that value from a different form though? As I said earlier I tried to use the docmd.Openform with the Forms!Form Name! Field name in the link and it did not work

	Kevin Robertson @Reply 2 years ago
Value From a Form Value From Subform

Kevin Yip @Reply

2 years ago

Hi Dan, the form that has the value need to be opened.  The form reference needs to be valid (square brackets and extra quotation marks may be needed), and used in a string concatenation as shown below:

    DoCmd.OpenForm "FormName", , , "[MyField]=" & Forms![Form Name]![Field name]

If MyField is text, use extra quotation marks:

    DoCmd.OpenForm "FormName", , , "[MyField]='" & Forms![Form Name]![Field name] & "'"

	Dan Pickle OP @Reply 2 years ago
Adam your method ALMOST worked but it did not open the edit form to the correct record

	Dan Pickle OP @Reply 2 years ago
Adam this is the code that I have for the logon button DoCmd.OpenForm "editF" Forms!editF!Num = NumTxt DoCmd.Close acForm, Me.Name It inputs the value of the wire number into that form but it does not open the form to that record

Adam Schwanz @Reply

2 years ago

Oh, you have to do the criteria to get to a specific record. For instance if you were on an invoice form that had the customerID as the foreign key, you would do something like this

DoCmd.OpenForm "CustomerF",,,"CustomerID=" & CustomerID

I'm not sure what your ID fields are that identify the record, but use those. I don't know your setup but if for some reason that unique identifier is the num field, you'd have to instead use something like currentdb.execute to insert the value to the table, and then open the form with it.

	Dan Pickle OP @Reply 2 years ago
Kevin Yip your solution worked! Thanks!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Value From a Form.

Next Unseen

New Feature: Comment Live View