Open Form with Information from Multiple IDs

[Nadine67]

Hello and thankyou for any attention my post may receive.

I have a db with numerous objects - three of which are tblMain, frmCapture,frmReadOnly.

When the user opens frmReadOnly I would like the from to not only populate withthe appropriate information according to the ID but also with information fromother IDs which meet a criteria.

This is the code I have to currently open frmReadOnly.

[COLOR=#222222][FONT=Verdana]Private SubCommand2_Click()<o:p></o:p>[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]DoCmd.OpenForm"frmReadOnly", , , "ImprovementID = " &Me!txtSelectView<o:p></o:p>[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]

The extra information I would like the form to populate with is stored incolumn 1 on tblMain however the columns containing the search-criteria arecolumns 51,52,53 in tblMain. The information in these three columnsis numerical values which will match the ID of frmReadOnly. So basicallywhere #10 appears in these three columns I would like the ID #s for thoserecords to appear on frmReadOnly #10 in either three separate fields or ideallyconcatenated with separators in a single field.

Is this possible?

Thank you and have a great day!<o></o>

 

[ranman256]

Use a filter.
open the form with all records.
then in a text box,user can enter, say a state, then filter all records for that state.

sub txtFind_afterupdate()
If isNull(txtFind) then 
    Me.filterOn = false
else
   Me.filter= "[state]='" & txtFind & "'"
   Me.filterOn = true
end sub

 

[Nadine67]

Thank you ranman256.

I apologise for my ignorance, but with your piece of code I don't understand how the form will open with the desired information and the 'dependents' field automatically populated with all dependent records.

I also do not understand 'state'.

 

[xenou]

State was just an example. Substitute with anything that applies (i.e., InvoiceID, InventoryNum, StudentID, AcctNum .... etc. etc. these are all common search fields).

It sounds like you want a query such as this (assuming that the user has provided an ImprovementID of 10:

select * from tblMain
    where 
	   Col51 = 10
	   or Col52 = 10
	   or Col53 = 10

 

[Nadine67]

Thank you xenou.

How do I include this in my code which opens the form? The form which I open frmReadOnly from has a textbox where the user enters the ID number. So wherever there is the particular ID # in Col's 51,52,53 then display the ID number of those records on frmReadOnly in Text78,79,80,81. Or would it be an '******' event for frmReadOnly?

[[FONT=Verdana]Private SubCommand2_Click()<o:p></o:p>[/FONT]
[COLOR=#222222][FONT=Verdana]DoCmd.OpenForm"frmReadOnly", , , "ImprovementID = " &Me!txtSelectView<o:p></o:p>[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]/CODE]

 

[xenou]

How do I include this in my code which opens the form? The form which I open frmReadOnly from has a textbox where the user enters the ID number. So wherever there is the particular ID # in Col's 51,52,53 then display the ID number of those records on frmReadOnly in Text78,79,80,81

It sounds like you are saying you want multiple records displayed all at once. Your form should be set up to display multiple records (i.e. a continuous form) - i.e., one textbox that can have one or many rows depending on how many records are returned.

Generally if you are not sure how to proceed start with a simpler case. write your form with two specific IDs in mind (they should have slightly different results, like maybe one has 2 associated with it, and the other has 3 records associated with it). The expand to allow more ID and finally any ID at all.

 

[Nadine67]

Thank you xenou!

Oh my gosh yes, a continuous form. Done!

So the form displays the information captured against the ID, as it should. But how do I display the ID's of other records that display the forms ID. E.g. ID1 has 2, 3, 4 as entries in columns 51, 52, 53. when I open ID1 on frmReadOnly I see values 2, 3, 4 in the subform. However when I open ID2 on frmReadOnly I want to see ID value 1 (and any other ID values where 2 is recorded) on the subform. Is this where I would need the query you suggested xenou?

select * from tblMain
where
Col51 = 10
or Col52 = 10
or Col53 = 10

 

[sitesazan]

Hello
The content is very informative and good on the blog
Thank you for your work
[FONT=Roboto, arial, sans-serif]web [/FONT]design

 

[xenou]

If your data might have something you want to display from any one of those three columns (col51, 52, and 53) then you have to include those three columns in the report. There will be no way around that except possibly with some vba trickery. The design is unfortunately not good - probably there should be one column for this data, not three, but now you are stuck with it.

 

[Nadine67]

Hi xenou

Thank you for your reply. I do appreciate that one column would be ideal, but am unsure how I save the values from multiple fields to a single record field in a column. Ultimately it would be ideal to have it this way. And because this is a new db without data is it too late to change the form format and only use a single table column?

Nadine