Search for Record Match then populate Form

[Guest]

Maybe someone can help here? I have read multiple postings out there on how
to search using a combo box or text box unbound. I have 2 forms, 1 query,
and 1 table named respectively:

form(s): "frmMain" & "frmCrit"
table(s): table 1
query(s): query1

I have a unbound combo box on "frmCrit" with a command button that is tied
to "query1" for my search form. On my other form "frmMain" I have a command
button that opens form "frmCrit". <my search form>

When I type within the open box to search for matching records of "table 1"
my search function works "however", it only brings up a table with the
matching record highlighted within the table. It does'nt actually populate
my "frmMain" with all the corresponding record information.

What I am trying to do is basically search records so that if there is a
unique key "-ID" already in existence that the form is populated by the
tables matching records.

It should be noted that my Query Properties have "Unique Records" set to NO.
Should this matter in populating the form?

 

[Guest]

hi,
you can't populate a form with a select query.
you would have to dump the query results into a temp
table then use the temp table as the forms record source.
here is sample code that i use. it works. i only provided
2 populate samples. my form has 17 text boxes to populate.

DoCmd.OpenQuery "qryVMDtempDiscDel", acViewNormal, acEdit
'a delete query to make sure the temptable is empty
DoCmd.OpenQuery "qryVMDGetRecord", acNormal, acEdit
'an append query to repopulate the temp table
Me!txtItemID = DLookup("[dis_ItemID]", "tempDisc", "")
'Populate the form's text boxes from the temp table
Me!txtDesc = DLookup("[Description]", "tempDisc", "")
'ect. ect. ect.

-----Original Message-----
Maybe someone can help here? I have read multiple postings out there on how
to search using a combo box or text box unbound. I have 2 forms, 1 query,
and 1 table named respectively:

form(s): "frmMain" & "frmCrit"
table(s): table 1
query(s): query1

I have a unbound combo box on "frmCrit" with a command button that is tied
to "query1" for my search form. On my other

form "frmMain" I have a command

 

[Guest]

hi again,
forgot to mention.
if you make changes to the contents of the form, can use a
record set to update the contents of the form back into
the main table or you can use and if then statement to
decide if it is a new record or an existing record. use
update for an existing record and addnew for a new record.
techniqually 2 recordsets.

-----Original Message-----
hi,
you can't populate a form with a select query.
you would have to dump the query results into a temp
table then use the temp table as the forms record source.
here is sample code that i use. it works. i only provided
2 populate samples. my form has 17 text boxes to populate.

DoCmd.OpenQuery "qryVMDtempDiscDel", acViewNormal, acEdit
'a delete query to make sure the temptable is empty
DoCmd.OpenQuery "qryVMDGetRecord", acNormal, acEdit
'an append query to repopulate the temp table
Me!txtItemID = DLookup("[dis_ItemID]", "tempDisc", "")
'Populate the form's text boxes from the temp table
Me!txtDesc = DLookup("[Description]", "tempDisc", "")
'ect. ect. ect.

-----Original Message-----
Maybe someone can help here? I have read multiple postings out there on how
to search using a combo box or text box unbound. I have 2 forms, 1 query,
and 1 table named respectively:

form(s): "frmMain" & "frmCrit"
table(s): table 1
query(s): query1

I have a unbound combo box on "frmCrit" with a command button that is tied
to "query1" for my search form. On my other

form "frmMain" I have a command

button that opens form "frmCrit". <my search form>

When I type within the open box to search for matching records of "table 1"
my search function works "however", it only brings up a table with the
matching record highlighted within the table. It does'nt actually populate
my "frmMain" with all the corresponding record information.

What I am trying to do is basically search records so that if there is a
unique key "-ID" already in existence that the form is populated by the
tables matching records.

It should be noted that my Query Properties have "Unique Records" set to NO.
Should this matter in populating the form?
.

.

 

[Guest]

Maybe someone can help here? I have read multiple postings out there on how
to search using a combo box or text box unbound. I have 2 forms, 1 query,
and 1 table named respectively:

form(s): "frmMain" & "frmCrit"
table(s): table 1
query(s): query1

I have a unbound combo box on "frmCrit" with a command button that is tied
to "query1" for my search form. On my other form "frmMain" I have a command
button that opens form "frmCrit". <my search form>

When I type within the open box to search for matching records of "table 1"
my search function works "however", it only brings up a table with the
matching record highlighted within the table. It does'nt actually populate
my "frmMain" with all the corresponding record information.

What I am trying to do is basically search records so that if there is a
unique key "-ID" already in existence that the form is populated by the
tables matching records.

It should be noted that my Query Properties have "Unique Records" set to NO.
Should this matter in populating the form?

Tom,

The query (query1) would need to be the Record Source for form frmMain to
have the form populated wiht the found records.

Try this:

-Copy the query and name it "query2". We'll get back to it in a minute.

-Next, create a new form and name it "frmSearch".
-Set the form header to visible. Copy the combo box from the form "frmCrit"
and paste it into the form header (frmSearch). Rename the combo box
"cboSearch". The Row Source for the combo box should look something like:

SELECT DISTINCT table1.fieldName
FROM table1
ORDER BY table1.fieldName;

Change table1 and fieldname to your table name and field name.


-Set the form Record Source to "query2". Add the fields to the detail
section. Set the form default view to "Continuous Forms". If you don't want
the "New Record" row visible, (Data Tab) set the "Allow Additions" property
to NO.

-Open the properties for the combo box "cboSearch". In the "AfterUpdate"
event add "Me.Requery".

-Close and save the form frmSearch.

-Now open "query2" in design view. Add [Forms]![frmSearch].[cboSearch] to
the criteria row of the query for the field you want to search on.

-Save the query.

Done!

Open the form "frmSearch". Select something in the combo box. The records
should appear in the details section.