Problem with Showing Query Results on a Form

0
votes

I'm having a Projects lists Continuous form where the form's Record Source is based upon a table.

I have a requirement to display a field from another table which is linked back to original table using its primary key. The primary key is a AutoNumber field, but when displaying in the form I've used Input mask something like this "TMG/FEA/"0000.

So I made:

  • a unbound list box
  • and made the Row source as the query which displays the relevant information from second table

This query was created using primary key displayed in form (I mean the [Forms]![Form Name].[Field] ) as the where clause.

But the results returns blank. I'm Stuck here. I'm not sure if the query is not working due to the Input Mask or because of something else. Please help me. Thanks in advance

1
databasems-access
Probably, make the RecordSource a SQL that joins the two tables - donPablo
@donPablo If i have to change the RecordSource of the entire form to a query, will the form be able to write the new records data back to first table? I don't have a clue. Moreover this one field is just for glancing, no updating to table of any sort. That's why I kept as unbound field and just query the required info and display it in the unbound field - prashanthvsdvn
Can include the other table if does not result in non-editable dataset. If this other table is a lookup table then should be okay. Alternative is DLookup(). - June7
It is too unclear what are you supposed to do to my mind. Keep smaller sentences, more your goals, about desired behaviour of controls. Split it to parts - Van Ng
'which is linked back to original table' - what do you mean by this clause? - Van Ng

1 Answers

0
votes

You should add to form field listbox, and set following values:

  • Data: primary key of your first table

After this, that field will duplicate ID value. Now you should transform RecordSource of this field in order to see contents from second table. So you should set:

  • SELECT [PrimaryForeignKeyID], [DetailedField] FROM tblSecond;. Actually I don't know the contents of your second table. Whereas PrimaryForeignKeyID is a field that links second table to first, so-called FK.

  • After this set following properties of list box:

    • ColumnCount = 2
    • ColumnWidth = 0;2
    • AllowEdits = False
  • Save form and open it for viewing.

In this case, your listbox will show the associated contents of second table on form.

So to my mind it is better for you to do such simple tables and forms, and you'll realize idea.

enter image description here