Using Recorsets

[Dinger_80]

I have tried on different forms using more than a few different methods to make recordsets happen and to no avail. My current reason for needing to use recordsets is because I am loading a form with information from different tables. Instead of using multiple DLookups, opening the recordset only looks for the information once, speeding up the load time on the form.

Dim Rs As Dao.Recordset
Set Rs = CurrentDb.OpenRecordset("PreviousTRRetrieval2Query")
    If Rs.RecordCount > 0 Then
        ProjectNumber = Rs!ID
        Agency = Rs!WitnessAgency
        FileNumber = Rs!FileNumber

That is the start of my code and I keep getting an error, 3061 Too Few Parameters. Expected 1. Now when I run the query I get the results I desire, which is all the information I need at this particular junction. I am using Access 2010. I was looking for any help on why I may not be returning any results for my recordset when the PreviousTRRetrival2Query returns results on its own.

 

[vbaInet]

You need to use a subforms to display the fields from different tables.

 

[CJ_London]

why not simply use your query as the recordsource to your form?

 

[Dinger_80]

The information that is being displayed on the form from other tables isn't information that is doing anything more than just being viewed. As an example, I have a field called CatalogNumber. A user selects a number from the drop down menu and and the next three fields autopopulate with information from the table about that CatalogNumber. In the main table that this form is based on is the primary key. I need the information about the CatalogNumber to be displayed so the user doesn't have to reselect the item off of the drop down menu.
So with what I have posted as my code, I am just trying to display the correct information in the drop down boxes so the user can easily glance overthings and doens't need to mess with things too much.
What I am trying to do is speedup the load time of the form. If its a new record there is no issue with the load time, its only when I am pulling information from different tables that I have the issue of it taking longer for the form to load.
I would even attempt to try and change what the recordsource is except that regardless of what query I input as the recordsourse I have the same issue. The problem isn't with the query but that the recorsource doesn't find any records when there should be a record, proven by the fact that the query returns the information. Now I am also willing to entertain ideas on how to speed up the load time if setting a query as the recordsource isn't the most efficient method to use.

 

[pbaldy]

Is this appropriate?

http://www.baldyweb.com/Autofill.htm

 

[MarkK]

That error occurs when there is text in the SQL that is not recognized by the SQL parser. Maybe a field name is misspelled, maybe you reference a form that is not yet open, maybe you've referenced a temp table you recently deleted. The query parser assumes that unknown text is a parameter name, and when you fail to supply that parameter's value, this error is raised.

Hope this helps,

 

[pbaldy]

That error occurs when there is text in the SQL that is not recognized by the SQL parser. Maybe a field name is misspelled, maybe you reference a form that is not yet open, maybe you've referenced a temp table you recently deleted. The query parser assumes that unknown text is a parameter name, and when you fail to supply that parameter's value, this error is raised.

In this instance my bet would be a parameter in the query (like a form reference):

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

But if the desired fields can be included in the combo, the recordset would be unnecessary.

 

[vbaInet]

You can do one of two things:

1. Use a subform that is tied to CatalogNumber via the the Link Master/Child properties of the subform. A subform can look as small as anything. You can seamlessly place a subform on a form in a such way that when viewed you won't even notice a subform was used.

Or

2. Tie a combo box or listbox to CatalogNumber, and use the code from pbaldy in post #5 to tie the three textboxes to the respective columns of the combo/list box.

 

[Dinger_80]

@pbaldy I think you nailed it. In the query I reference a text box on the open form so I know which records to pull. I won't know till I am work tomorrow.
@vbainet, I do tie the text boxes into the combo box, I set the after update properties of the combo box. I will have to play around with using that to load the text boxes when the form loads. Unfortunately I also have drop down menus that affect other drop down menus. Essentially a user is reducing their choices down to the item they want.
I haven't done much in the way of doing sub forms. I am willing to bet that my form would be considered to large in general. This is my second database I have designed. My first was version one of the one I am working on now, with strong emphasis on normalization. Lots of books on how to do things, very few on how to do them well. That being said I imagine with in a year I will change things again, trying to improve performance and other issues that arise.

 

[vbaInet]

@vbainet, I do tie the text boxes into the combo box, I set the after update properties of the combo box. I will have to play around with using that to load the text boxes when the form loads.

Not quite. What I'm saying is that the PreviousTRRetrieval2Query query can be the Row Source of another combo/listbox and all you do is Requery this new combo/listbox to get the filtered value. Like I mentioned pbaldy has this approach explained in his website, but since you've not managed to take a look, here it is:

http://www.baldyweb.com/CascadingCombo.htm

If after following the method above you still wish to present the values in their own individual textboxes you can use the other method pbaldy mentioned in his post #5 to pull the values from the new combo/list box.

I haven't done much in the way of doing sub forms.

It's high time you get stuck right into it. There's a lot you can accomplish with subforms and with one you can accomplish the above much easier.

 

[Dinger_80]

I am familiar with using the cascading combo box effect as it is implemented on my form in two sections. I have a harder time back filling the information though. The last combo box contains the foreign key that is saved in my main table. I will have to look into redesigning my whole form if I do split it up, so in the mean time I am going to try and do what I can to speed up the load time. This new system gets launched on Monday, and I don't see me having redesigned a whole new form with subforms build in and all working by then, but I will pursue that endavor all the same.

 

[Dinger_80]

@CJ_London I don't have an actual query for the recordsource of the form, though I am sure I could just put in the SQL in with my VBA. My reasoning for not, please let me know if I am doing something wrong, is that sometimes not all of the information has been entered into the record. Users have the option for saving their progress and completing later. When I query for fields that don't have any data in them no records are returned. Since no records were returned it wasn't possible for me to poplutate the unbound text boxes.
@ vbaInet - Perhaps it would just be best to point me in a good direction for how to better design a form with related information displayed, but without duplicating the information. If I have a good direction I can usually find the way. The other thing I have is some unbound Text Boxes on my form, they display the related information. I am sure I don't need a subform for every unbound Textbox right? There are 3 cases on my mainform that information from separate tables that just display one bit of information, for example, Username. In my main table it stores the information as a number, but they see the actual name of the requestor instead. Once again just looking for direction on. I have figured out how to make the sub form blend in nicely, thank you for the push on that.

 

[vbaInet]

@ vbaInet - Perhaps it would just be best to point me in a good direction for how to better design a form with related information displayed, but without duplicating the information.

There's no strict rules on designing a form although there are guidelines for designing a good application. There are lots of information about this on the net.

The other thing I have is some unbound Text Boxes on my form, they display the related information. I am sure I don't need a subform for every unbound Textbox right?

Right! Here are your options:
1. If you look at my previous posts I mentioned using a combo box or listbox to pull in the information.
2. There's also a function called DLookup(). A tad slow though!
3. You can use a recordset.

However, if you're needing to display a lot of information from so many different tables/queries then perhaps a form layout redesign might be a consideration.