vba for form

[vicgirl]

Hi,
Does anyone know how to create a form which let the user input some search criteria and the results will be listed in the same form by using the Docmd.RunSQl() instead of requery()? How is it done to have multiple records display?

The form is similar to the following:
Input Name: Date From: To:

Search Button

Results:
ID Date Name


Thanks in advance!

 

[WayneRyan]

vicgirl,

The DoCmd.RunSQL does not return anything. It is used to alter
values in tables, but without requerying, it won't have any
real effect on your forms.

Why the aversion to Me.Requery?

Wayne

 

[vicgirl]

I see. Thank, Wayne. I have many problems when I use requery(), which is listed in the other topic "VBA in Forms and Combo box" I have posted today. The Column() function is not recognized in the Sql statement(that is, you can write forms!search_form!Purpose.Column(0)) to get the string value of value lists in a combox, while it works fine in VBA. I am trying to get around this problem by using Runsql() instead.

 

[WayneRyan]

vic,

You have way too many things going on here at once! I'm getting confused.

Take a look at this sample db. I made it from one of your earlier posts.

Wayne

 

[vicgirl]

Thanks for your help, Wayne.
Sorry about the inconvinience. My supervisor did the Entry form and created the maintable, and I have to do the searchform. Both of us are beginners to Access.
I have took a look at your sample search form. However, it shows only one record matching with the criteria. For example, if there is two people called David, the form only shows one record. Can it show the list of record instead too? Thanks again!

 

[Mile-O]

Those tables need redesigned too. They go against First Normal Form (1NF).

 

[vicgirl]

I see. However, my supervisor doesn't want to redesign those tables since there are lots of data saved already. It is going to have problems when I adapt the db to those existing file. In addition, she doesn't want me to spend too much time on it.

Those tables need redesigned too. They go against First Normal Form (1NF).

 

[WayneRyan]

vic,

The sample is just that ... a sample.

If there are two (or 100) Davids, then they will all be returned. I think
that for ease of use, you should restrict it to one form. It's really not
that complicated of an issue.

Perhaps you could migrate to a continuous form and use a scroll
bar to move through the returned records.

Also, Mile is right about your data. You can redesign your tables
to remove the repeated fields (topics and "sent to"). You don't
have to lose your current data.

Wayne

 

[boblarson]

And I'll add a little extra to WayneRyan's comments -

If you don't redesign it now (like he said you can retain the data, you'll just have to move some of it around) you are setting yourself up for a WAY bigger headache down the road. Believe us, I know I've been there and suffered through the redesign of something that I should have done a long time ago and it was not a pretty picture. That's when you'll be spending way more time than your supervisor wants spent on it.

That is from personal experience, so take it or leave it, but it is true.