QryDefs and Parameters to get value from a table

[d9pierce1]

CSZ.zip

Hello all,
I created a new db and set up some qd's (QryDefs) to get records from 4 different tables, then populate a 5th table (Tbl_CSZ) with those values. So this all works as it should but when I open the form it is searching for the parameters and what I am asking is how do I get this to put the current record parameters in to the combos? I know there has to be some way, I just dont know how to do it? any assistance would be so helpful, data base zip attached.
Thanks


Dave

[June7]

I have never seen code using QueryDefs like this. I have never seen the @ character used in parameters. Why did you use this approach?

When opening dynamic parameterized query, Access looks for something like a textbox name that matches the parameter, when it doesn't find anything, it triggers the input popup. If you don't want the popup, then parameters must reference a form control.

So an unbound textbox or combobox can be referenced as a parameter like: Forms!Frm_CSZ!cbxState and that same reference will be used as criteria in query. User selects a value in the combobox and query receives that value.

I removed the parameters from queries and no more popups.

I don't use dynamic parameterized queries if I can avoid.

Review http://allenbrowne.com/ser-62.html

[d9pierce1]

Hi June7 and all,
First off, thanks for your input and am reviewing Allens page.

The @ symbol is just a preference of mine, you can call it anything you want to answer that.

I would think that if I have a record with those values in the table, Tbl_CSZ then I could pass them on to the Combos in that record? Maybe such as the on current event? Where Something like the values StateID =

CboStateID, CityID=CboCityID and so on in the table for that record. That way it would not be looking for the parameter value. There should be a way to take the values in the table, current record, and pass them into the combos in the OnCurrent event in less its a new record?

Thanks
Dave

[orange]

Can you tell us in plain English what you are trying to do- no jargon and no code?
I opened the database and put FL in for the State and was told it wasn't an accepted value.
Can I suggest you use a combo or listbox to select a correct/valid value(s) for your "parameters"?
Users shouldn't have to guess what your code/user interface needs to run.

[d9pierce1]

Thanks,
Sorry about confusion...
I just want to take the values from the table in an existing record and put them into the combos so that it will show me those values in text on the form so that when open or going from record to record, it populates the form with those values if not a new record. Then it wont be looking for parameters on open.
Thanks, hope this explains this clearly

[orange]

?? still confused???

You mentioned parameters in early posts.
Now it seems you simply want to display values from underlying table???

Perhaps you should mock up a picture of what you want showing some sample data from your table(s).

[d9pierce1]

OK,
When I open form, it is calling for parameters and result is (Left Cities Form) what I would like is to take the values from the table on top, put them into the the form combos, and show me the results in the (Right Cities Form) if that is possible as I would think that would get rid of the parameters? This may not be true but asking to see if possible. My thoughts are that if the record exist, then the parameters are all ready their?
Thanks

[orange]

I think we are not on same wavelength.

I can not find Montreal as a city in Montana.


Forget the Parameter and forms etc for the moment.

If you had to tell someone who doesn't know you, your environment or Access etc. in very simple English
What do are you trying to do? In 1 sentence - no jargon.

Could it be I'm trying to validate a number of City/State values?
I'm trying to remove duplicates from a table?

[d9pierce1]

Just trying to get the values on the form from the table.

[June7]

Controls used to input criteria to filter/search form or report records must be UNBOUND (see the Allen Browne link for example), otherwise use the intrinsic filter/search tools on the ribbon or right click shortcut menu. Your form has BOUND controls used to enter data to record and certainly don't want code to set these comboboxes to empty string because that would replace data in Tbl_CSZ.

BOUND or UNBOUND, if you want one combobox/listbox list to be filtered by selection/entry of another control, that is called cascading (or dependent) combobox/listbox. This is a common topic and you are using a convoluted approach. Instead of each combobox referencing a query object for RowSource, conventional approach is to build the SQL statement directly into RowSource property:

SELECT StateID, StateName FROM Tbl_State WHERE CountryID=[CboCountryID] ORDER BY StateName;
SELECT CitiesID, City FROM Tbl_City WHERE StateID = [CboStateID] ORDER BY City;
SELECT ZipCodeID, ZipCode, County, Latitude, Longitude FROM Tbl_ZipCode WHERE CityID = [CboCityID] ORDER BY ZipCode;

No parameter declarations and no QueryDefs code needed. Eliminate the __Set procedures. Fix Reset procedure so comboboxes are not set to empty string. Then call the Reset procedure from Current event or move these lines into the Current event (and use Reset procedure for UNBOUND filter/search controls):

Private Sub Reset()
With Me
    If .NewRecord Then
        .CboStateID.Enabled = False
        .CboCityID.Enabled = False
        .CboZipCodeID.Enabled = False
    End If
    .CboStateID.Requery
    .CboCityID.Requery
    .CboZipCodeID.Requery
End With
End Sub

Note for the future: BOUND cascading comboboxes/listboxes with lookup alias don't work nice on continuous or datasheet view forms.

[d9pierce1]

Thank you!