Record Source for a form (2000)

Topic

New Reply

How do any of you handle this situation:

You want a form to open up without a record source, then after making choices from a couple of unbound fields, use a record source for the form. I’m looking to see what others do to avoid the #Name? when the form first opens. I am hoping to avoid having to set the control source for each field upon making the choices in the unbound fields.

TIA

Reply | Quote

Replies

Hi
Could you put some unbound controls in the form Header or footer. Use the contents of these once populated as the basis of a query . Then use docmd findrecord ..
Alternatively create a record set from the querydef and populate the form detail from the record set.

Cheers
Geof

Reply | Quote

I’m not sure I would ever have that situation, since it requires that each source have the same field names! However, I’d set my recordsource equal to one of the possible alternatives, but include a WHERE clause I knew would not find any matching records (for example, if primarykey field is an autonumber, I look for ID=0). This would give me an empty recordset, so no data is display; yet no #Name error either.

Reply | Quote

Yeah, that was stupid of me. I should make one basic SQL statement in code without the where clause. Create the where clause based on the choices made in the unbound fields, then set the record source to that SQL statement created by joining the two strings.
And set the form to look for the value I know wouldn’t be found.

Is this what you’re saying I could do?

Reply | Quote

>>Is this what you’re saying I could do? <<

Pretty much so. What I also said to use a WHERE clause in the initial SQL string, but one that would guarentee returning an empty recordset. This way you wouldn't waste time initially loading the form with a recordset you knew would change as soon as user made his/her selections.

Reply | Quote

Thanks for the input!

I’ve got strSQL = to the SQL statement that will give me the fields and records I need from 3 tables. In my form’s on open event I’ve got the recordsource=strSQL & strWhere which looks for a null in the Primary Key field of one of the tables, thus giving me my empty recordset when the form opens. The recordsource property in form design doesn’t need to be set.

Then based on whichever field is picked from the four in my combo box, I then set my strWhere string to an appropriate where clause.

This works great as no one can hit the Remove/Filter sort button and display all the records like they could if I simply stuck with an ApplyFilter method.

Thanks again

Reply | Quote

I usually open my bound forms with an empty recordset by using a where clause that always evaluates to false. Something like this:

SELECT * FROM tblWhatever WHERE 1=2

As soon as Jet realizes that the condition is always false, it returns an empty records, and the form opens very quickly. Then, when the user does whatever has been prescribed to specify a record or records, I create a SQL string that includes a specific WHERE condition to return the specified record or record and set the recordsource to that new SQL string.

Reply | Quote