1

So I've been reading everything that I can find on this problem and everyone's answer seems to be alternatives, but those alternatives won't really work for me.

What I am trying to do is click a button in form-1 and have it open form-2 with the correct foreign key from form-1 so I can view the data and add more based off of form-1's key. The reason for this is form-1 doesn't have enough space for hold all of the subforms that I would like to use. Also, putting a button on form-1 to make form-2 visible doesn't really work well for me, but looks to be my only option for now.

What happens now is when I click the button on form-1, form-2 does open, but it shows all of the data from table-2 and if I try to add a new record, I have to manually enter it in. I can't get the foreign key in form-2 to auto populate the ID in form-1 when using a button to call the form. 

example tables and forms:
table-1 & form-1: The form has the same data as table
id
first name
last name

table-2 & form-2: The form has the same data as table
id
foreign key = table-1.id
address
someotherdata

goal: form-1 has a button called "add data". It opens form-2 with foreign key from the same ID from form-1. I can view past records or enter more data and save. If I go to form-1 record ID=5 and click the button, then form-2 will pop up with foreign key 5 and allow me to enter more data to foreign key 5/ID 5.

If you have any questions, please let me know. I can't really do much until this is figured out. I feel like I'm missing something and this should be much easier to do.

Thank you!!

Dmurphy
  • 33
  • 7
  • 1
    Why not use form/subform design? Another approach is to pass the ID with OpenArgs argument of OpenForm method and open the form to a new record. Then form2 code can use its OpenArgs property to pull the ID. `If Not IsNull(Me.OpenArgs) Then Me!fieldname = Me.OpenArgs` or `If Me.NewRecord Then Me!fieldname = Me.OpenArgs`. https://stackoverflow.com/questions/43600653/passing-values-between-forms-using-openargs – June7 Feb 26 '18 at 21:49
  • @June7 If I ended up using subforms, my main form will be overloaded with subforms. I'm using tabs with subforms now, but it is still too clustered. Have a button to access another form would be the best for me. So I don't mess up my current database, I made a basic ms access database to attempt to solve this problem. Tables and forms are the same. Form1 is (MainID, Fname,Lname) and form2 is (NotesID,MainID,Notes). I created a button with what you suggested, – Dmurphy Feb 28 '18 at 23:33
  • so I can pass the Form1 MainID to Form2 MainID. I now get wrong data type error. form1 code `DoCmd.OpenForm Form2, acNormal, , , , , Me.MainID ` and form2 code on open `If Not IsNull(Me.OpenArgs) Then Me.boxFinder.Value = Me.OpenArgs Me.Requery End If` – Dmurphy Feb 28 '18 at 23:33
  • I'm not sure what I am doing wrong. I greatly appropriate the help! This is driving me nuts. – Dmurphy Feb 28 '18 at 23:39
  • I don't know your db. What is data type of MainID? What field is boxFinder bound to? What data type is that field? Form2 is opening to an existing record unless you have the form DataEntry property set to yes. Otherwise, code will change data in that record. What is 'too cluttered'? If have form with 10 pages on tab control. – June7 Mar 01 '18 at 01:12
  • @June7 I went with the subform and tabs option, but it is messy. It would take a lot of time to explain why my real db it's set up the way it is, so that's why I just gave the simple example db above. In the example MainID in form1 is a primkey. in table2 its a number. I dont know what boxFinder is, I was thinking it was some sort of function. The goal is to pass the MainID in tb1 to MainID in tb2. – Dmurphy Mar 02 '18 at 18:27
  • I figured this would be a common thing to do in access. Lets say someone has a users table/form and a notes table/form. There may be multiple users and each user may have multiple notes. They want to add notes via a button that pops up the notes form instead of having a subform on the page. Is VB the only way to do this? and if so, am I on the right track? – Dmurphy Mar 02 '18 at 18:36
  • Yes code (VBA or macro) is required to pass the ID to the called form. Why do you not know what boxFinder is - why do you reference it in your code? The code is supposed to be setting value for the foreign key field. Why do users feel the subform is not adequate? Consider the popup zoom box. In textbox DblClick event `RunCommand acCmdZoomBox`; Shit+F2 also opens it. – June7 Mar 02 '18 at 19:49
  • I've read so many blogs and tried so many different variations of code from those blogs, that I must of missed that I put that there 'acCmdZoomBox'. I'm the only user for this db and if you saw it you would probably agree. I corrected my code and I'm now getting Run-Time Error '2448': You can't assign a value to this object. Again I appreciate your help! Ill post the code – Dmurphy Mar 02 '18 at 22:51
  • This is for the Form2. Form2 MainID is the name of the foreign key which is the same for the primary key on Form1. `Private Sub Form_Open(Cancel As Integer) If Not IsNull(Me.OpenArgs) Then Me.MainID.Value = Me.OpenArgs Me.Requery End If End Sub` – Dmurphy Mar 02 '18 at 22:55
  • Here is the code for Form1: `Private Sub CommandBTN_Click() DoCmd.OpenForm "Form1", acNormal, , , , , Me.MainID End Sub` What am I doing wrong? – Dmurphy Mar 02 '18 at 22:58
  • Edit your question to provide new info. Don't you want to open Form2? However, if the form opens to existing records, the code will change value in the first record. Need to be on a new record row. Several ways to accomplish that. – June7 Mar 03 '18 at 01:02

1 Answers1

0

Consider:

Code behind form to open another form to a new record and pass value with OpenArgs:
DoCmd.OpenForm "yourFormName", , , , acFormAdd, acDialog, Me.yourControlName

Code behind called form to pull value from OpenArgs and populate field:
If Me.NewRecord And Not IsNull(Me.OpenArgs) Then Me.yourControlName = Me.OpenArgs
Real trick is figuring out what event to put this in - probably the form Open.

June7
  • 19,874
  • 8
  • 24
  • 34
  • It took a little tweaking, but it finally worked! The one other thing I had to do to get it to work is select design view on the form that I wanted to send the data to (Form2). Select the field that I wanted the data to go to (Foreign Key) and in the property sheet set the default value to the form/field that I wanted the data to come from. `=[Forms]![Form1]![MainID]`. Thank you very much for your help @June7 ! – Dmurphy Mar 05 '18 at 18:23
  • Well, never seen that approach used. So don't need OpenArgs nor code behind the second form. Always more than one way to accomplish something. – June7 Mar 05 '18 at 19:44