Replies

Figured out a way to do this. It’s a little strange but I just defined a variable and loaded it with the string information you’d normally type in the formula bar:

<–
Range("c13").Select
IndexRange = "=INDEX('Staffing Numbers Database'!" & a & ",'Staffing Numbers Database'!E1)"
Selection.Value = IndexRange
</–

Doesn't invoke INDEX in VB at all.

that’s precisely what i did.

thnkx

nevermind. got it.

I was having problems getting VLOOKUP to work but another lounger suggested INDEX and that seems to do the trick.

thanks for helping!

Thanks! works fine. I put the referenced cell for the INDEX function on sheet 2 and it worked just fine.

it’s a combo box from the Forms toolbar set directly on a worksheet. i tried data validation using List and tried to reference the list values used for the drop down box on another page but it doesn’t do anything – it seems to want to look for a list of values on the page holding the data validation cell, which is not optimal for what i am trying to do.

thanks for the quick response! i’ll keep working on it.

thanks for the explanation. still a little fuzzy on what it does as the hidden field on the main form isn’t named anything pertinent to the master/child links. also the secondary subforms use the same Master/Child relationship as the primary subform, which is appropriate as a given client can have the same info (dates and such) on their subforms so the subforms need to be tied to the client ID. I guess, abstractly, the SQL for the secondary subform displays would be

SELECT * from qryWeeklyActivities WeekIDCase = [MondayDate subform].WeekIDCase

with the ClientID/WeekID link already set, this is the needed refinement.

In this case you see I need to reference the MondayDate subform directly on the other day subforms but haven’t had much luck so far. well, must keep banging away…I am concerned with constructing a GUI that gives the end user navigation based on a week chosen and not confuse them with the details.

I noticed this tip when trying to resolve a problem synching records on a tab controlled subform to a record in the main form. The tabcontrol contains 7 subforms, one per tab, for each day of the week. the subform is linked to the main form with a ID number and I need to display the info across a week consistently. I put in a button to add a new week to the subforms; this button goes to each subform and adds a new record then goes back to the Monday subform on the tab control. I have some code that populates the rest of the week dates based on the monday date and was not able to get the new records on the other day subforms to show up consistently after adding the new record.

I inserted the hidden textbox on the main form to reference to the MondayDate on the subform and now the new record information shows up. (previously the new record for Monday shows but the other dates don’t necessarily display the new record even tho it is there) First question: how does this even work? You’re not setting up any filtering or queries or anything, just displaying the subform based on a hidden field on the main form.

I am also trying to accomplish the following: navigate to a given record in the Monday subform. Then click on the tab for some other date and have the appropriate record show up. For example, a single client has three weeks of data in the subform. When you go to the second week in the Monday subform, the Tuesday subform doesn’t follow along. I added the navigation controls to at least make it easy to find the right date but I want to do this automatically. I tried to add a hidden field to the Tuesday subform referencing the WeekIDCase of the Monday subform but that didn’t do anything – displays #Name?

The overall structure is:

ClientID –> ID for main form info
WeekID –> ID for any weekly information for a ClientID
WeekIDCase –> ID for a specific Week in the ClientID-WeekID selection

Which looks fine to me; the problem is this Tab Control and getting the subforms to respond to changes in the WeekIDCase when changed in the first Subform. I have built each subform on top of a single query so the source is exactly the same; each subform simply displays a subset of the available info in the source query.

Any suggestions? TIA!

thanks for the demo. turns out i have hacked out a solution of sorts. I have some simple code that visits each page on the tab control and adds a new record. This gives me a new record in the base query with the same WeekID, so I have the one-to-many relationship with ClientID. The basic nut has been cracked. I can now go on…hopefully! now I need to do some additional work on the tab control to help the user know what ‘page’ they’re on if there’s more than one week activity set for a client. been having some problems getting events to trigger when clicking on the tabs on the tabcontrol, but that’s alright. just another day in the life.

thanks for you assistance, all!

Ok, I’m trying to do what you suggested – it makes sense somehow: Placing the main subform on the main form with the basic query as the source would make the main subform a container for the sub-subforms. The basic problem I am having, after all, is simply adding a new record to the tabcontrol. However, I am having many obsticles:

1) the source query has too many fields for it to used as a subform. There are 7 fields for each day of the week plus the WeekID field gives 36 fields and Access chokes when I try to plop the whole query in as the source for a subform. So, I thought, why not just use the WeekID from the source query and have the sub-subforms display the rest of the fields? This doesn’t really seem to work

2) When I place a sub-subform on a tab control, strange things happen. I can set the master/child field on the main subform to ClientID/WeekID; the sub-subform master/child settings look strange in the Wizard: Show for each record in using WeekID. If I ok thru all that, I can’t edit the Linkage parameters – I get an ‘Object variable or With block not set’. I don’t think the nested forms are working correctly, but i’ve never worked with them so i may certainly be missing something.

This is all a little frustrating, because I’d think it’s not a real difficult problem: Access is supposed to allow you to parse out and relate tables. That’s what I’m trying to do with these two tables (Client and Weekly Activities) — but, since the Weekly Activities has a lot of fields, it looks best to use the tab control and, because there’s a memo field for each day, I don’t want to use a datasheet. All I really want to do is add a record to the Weekly Activities as needed and have that new record relate to an existing Client ID as a one-to-many relationship. It’s not hard to make a new record and have the tab-control formatted subforms hook up with a record in Client Info, but it seems devilishly difficult to make this thing add new Weekly Activities in the format I have set up. If I could do this in Datasheet, I think I would have been well on my way to other aspects of the database.

Perhaps your ‘rough and dirty’ example would help – if it can add a new record in the sub-subform that gives me blank fields in the tab control and keeps the same WeekID, that’s exactly what I need: a one-to-many relation between ClientID/WeekID with tab-formatted sub-subforms build from parts of the source query. Could you post it, perhaps?

Also, do you think there’s an issue with the sheer number of fields in the source query?

thanks

thanks for the response. I went ahead and replaced the DoCmd with RunCommand code and that works also. I kind of like RunCommand because you can tell from looking at it what you are trying to do, rather than calling a menu item by number. Guess it’s just philosophical. I did notice a line in the VB Help that states that RunCommand replaced DoCmd after Access 97, which implies that DoCmd is, so to speak, deprecated. That doesn’t eliminate the error you get when trying to duplicate the form data before selecting a record, which was the original problem. You suggestion to disable the button is interesting; I might want to have the form select a record on open as well — say the first one, to reinforce that one navigates to other records thru the drop-down box. That would be even more intuitive, perhaps.

On the subject of paging up and down on a selected record, I just noticed before getting your reply that this occurs when the Mouse Wheel or Page Down is used and definately don’t want this to occur. I have a sub-field that needs to be tied to a specific record and don’t want users to accidentally scroll into a new record on the subfield. I have it set to cycle in current record in Properties but can’t find anything to answer to the Wheel situation. There is a way to detect PageUp or PageDown and do an event — maybe automatically return to the ‘actual’ record and delete the one just created. It would be better to disable the key totally but help says key events can’t be cancelled. Any suggestions?

I realize this thread is getting off-topic; if you want to move it to something like Subform/Form Navigation Control issues… that’s kind of what I am blathering about. I’m using A2K SR-1 on Win2KPro

I remembered you saying there have been some problems using the stock Recordset.Clone code when running the Select a Record on a Form wizard when placing a drop-down box. I happen to use this method frequently and was wondering if you could give me more detail on the issue of applying a filter from the combo box.

I am currently working on a project that uses the standard method for finding a record based on the combo box and don’t have any problems with that directly, but I also have a button set to Duplicate Record and, if you don’t select the record using the combo box you get a very basic run-time error that I can’t seem to trap out and use a less obscure warning. The run time error is ‘Invalid use of Null’. I want to replace that with a msgbox saying ‘Please select a record from the drop down box before duplicating a record’ but the Access VB error msgbox always take precedence.

So — actually, replacing the combo box may not help but I am curious as to why you avoid it and would like more details on your substitution.

And, as to the error on the duplicate record routine, I am using the canned Access coding which likes to call menu selections — is that a bad idea? Is there a better way to do it? I get confused by the acCmd and DoCmd and RunCommand options at any rate. But, I would like to not use the kind of code the Wizard likes to use, such as DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 — it would seem to me that using custom menus or hiding/removing standard ones would cause such code not to work. It just *looks* wrong.

Hope this isn’t too confusing.

Yeah, that works. Thought I’d tried that before but apparently not. Turns out you have to refresh a couple of times for the MondayDate to take and the other dates to take, but the puppy works.

Kudos!

having a hard time visualizing this. I suppose you’ve tried moving the entire tabbed control down from the form header?

I noticed on a tabbed control I am using that it refused to re-size and was wasting screen resources and making the whole form ‘jump’ when setting focus to the tab control. My tabbed control holds sub-forms and the only way to get the darn thing to resize was to open each sub-form and ‘resize’ it’s display (if you play with the window around a form in design view, it keeps that window size in display. Anyway, once I re-sized each subform the tabbed control allowed me to resize it.

A quick way to check if this might be the problem is to open the subforms on their own and see if they are opening in a larger than needed window. then re-size their window display and re-open. Do this for all the subforms, then open the form with the tab control and try resizing it.

Thanks for the reply. I was originally finding problems getting a simple text line to wrap on re-display or print but, mysteriously, that behavior has since vanished. It seems things work fine if you are using text with spaces, like most expected input. Not sure what the problem was. Anyhow, I did spend a lot of time hacking out this function; it still has some utility in cases where you’ve got a long text string with no spaces (such as, perhaps, a GUID concatenation or a long URL reference such as the one on this reply page).

The attached file shows the function and a way to call it via an include file.