Passing field from a form to a form based on a different table/query (1 Viewer)

Absolute VBA noob here. I've been searching the net for days but the answers confuse me more than they help. I need a good VBA book...

Anyways, I have a search form based on a query that is based on tbl_models. When the model is found, you double-click on it in a listbox and a data entry form opens for that model. Works just fine, [modelid] is the primary key of tbl_models:


Instead of opening the data entry form frm_view_cigar, I want to open another form, called frm_review_cigar. This is a data entry form too, but based on another table, tbl_reviews. This table also contains the [modelid] field, but as a foreign key.

So I figured I'd do this:


Alas, no joy. The form opens all right, but the value for [reviewmodelid] is not passed on. What am I doing wrong? Please tell me it's just a minor syntax thing...

Also, book recommendations are appreciated

====================

BTW, I originally started out the second form frm_review with a combobox and that works fine. The selected [modelid] of the cigar is neatly entered into the [reviewmodelid] field.

My reason for wanting to use the other form is that with a combo, you can only search alphabetically, but the user may only know part of the name. Compare it to searching for "Mustang" while the combo wants you to start with "Ford".

On the other form, the user can type any part of the name through a nifty "like" statement in the query, which I poached off the internet.
I can use any text to populate the listbox (using some "like"-syntax in the query, which I poached off the internet), and in a combo you need to know the start of the name of the cigar you're looking for.

For starters, you don't want the form name in the where condition. Try

DoCmd.OpenForm "frm_review", , , "[reviewmodelid] =" & Me.lstbx_search_results.Column(0)

I did as you suggested, and frm_review opens, but [reviewmodelid] remains empty.

Do I need to ditch the original combo (still on the form, tied to [reviewmodelid]?

ETA: deleted the combo, still no go :-(

Another edit: I notice the control source of the reviewmodelid field on the form is [reviewmodelid] - should I delete that? I suppose this happened when setting up the form with the wizard.

The code you're using is designed to open the form filtered to any records that match the criteria. Are there any? It's not designed to input the value into a new record.

pbaldy said:

The code you're using is designed to open the form filtered to any records that match the criteria. Are there any? It's not designed to input the value into a new record.

Click to expand...

My noob-answer would be "yes and no":

The reviewmodelid exists but it gets inserted in a new record of the query based on tbl_reviews. So in tbl_reviews, reviewid is the PK, while [reviewmodelid] is the FK.

When I used the combo, it would insert the modelreviewid FK, even if the reviewid PK wasn't generated yet.

pbaldy said:

The code you're using is designed to open the form filtered to any records that match the criteria. Are there any? It's not designed to input the value into a new record.

Click to expand...

Thank you for the pointer, Paul. I searched the forum again and found the solution. Since I can't post a link yet (too few posts), here's the code for anyone stumbling on this thread:

missinglinq said:

In The calling or original form

Code:Copy to clipboard

Private Sub Go2SecondaryForm_Click()

If Not IsNull(Me.ID) Then
  DoCmd.OpenForm "SecondaryFormName", , , , , , Me.ID
 Else
  MsgBox "An ID Must Be Entered First!"
 End If

End Sub

In the called form (SecondaryFormName in this example)

Code:Copy to clipboard

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
    DoCmd.GoToRecord , , acNewRec
    Me.ID = Me.OpenArgs
 End If

End Sub

Linq ;0)>

Click to expand...

Glad you got it working. Simpler would be opening the form in data entry mode and dropping in the value:

DoCmd.OpenForm...
Forms!NewFormName.TextboxName = Me.ID

Would that require me to base the form on the table/query of which modelid is the PK?

The reason I'm asking is that I want to enter the modelid as an FK in a query based on another table.

Also, what does ... mean in your code? Sorry to ask but being a VBA noob sometimes makes it hard for me to correctly identify dummy content and replace it with my own.

It should work the same as what you have. Basically you're passing the ID and letting the newly opened form place the value, my way pushes it from the first form. Your way is better if the user might add several new records, which should all get the same ID.

The "..." is shorthand for "and so on", basically meaning to fill in the normal info. It's not a VBA thing, more of a general thing. From a dictionary: "the omission from speech or writing of a word or words that are superfluous or able to be understood from contextual clues." So in your case:

DoCmd.OpenForm "SecondaryFormName"

If the new form isn't already set to data entry, you can add the argument in OpenForm. My guess is it already is, if your code works.

Good to know. I won't need to pass the modelid on to many records nor is my form set to data entry, so I'll try to get your code working.

What you have works, so feel free to leave it alone. I was just offering a simpler alternative.