Unbound text/combo boxes with subforms(a few q's

Topic

New Reply

Hi all

A few questions, first off I have a main form and table with 3 subforms running off 2 other tables. In my main table I have only 3 fields, ID, PID and Team, all unique so that I have a set number of main records. The subforms are all linked by team command but I also use an unbound combo box, month, on my main form as a selection for the subforms. Now I have month as reqired on the sub forms and that’s ok but what I want is that when a month is selected in the unbound combo then the subforms will open to that month or if it’s not there it’ll create a new record but not having much success so any help would be awesome. Still pretty rubbish with VBA but trying to learn so dummy instructions would be nice.

Another question, one of my subforms is used only to enter hours allocated to certian areas, so it’s just the first field is total hours available then a few feilds for deductions and finally a GrossHours field that’s a simple calculation, I want to be able to use that figure in another subform and be able to manipulate it without changing the original entery in GrossHours(i.e. after they have completed the subform for hours there will be an entry in GrossHours that will be saved in a table, I want to be able to copy that number into another text box, I’ve been trying with an unbound text box on my main form, and then be able to take hours away from that without changing the entry in GrossHours). Hopefully this makes sense to some one.

Thanks for reading these 2 walls of text anyways.

Cheers

Reply | Quote

Replies

It might be an idea to post a sanitized copy of what you have now.
It is often easier to give guidance when the issue is seen in context.

Reply | Quote

Here you go, hopefully this show/explains what I mean better

cheers

Reply | Quote

I had a look at the database you posted, you might like to clean it up a bit and repost to make it easier to work on. Stuff like the naming of fields and controls so that they make sense.

eg fields that relate to each other should have the same name like Team_Command

don’t use ‘Month’ as a field name because it’s a reserved word, use a name like Transaction_Month. I always use at least two words separated by an underscore. That way I can’t possible use a reserved word by mistake.

then name the combo cboTransaction_Month. It’s really hard to follow code that refers to controls with names like ‘combo38’

OK now a couple of pointers.

You can use the Link Master Fields / Link Child Fields to limit the data in a subform as you have done. Unfortunately you can only do this for one field.

You can however use a filter in the Record Source of the subform.

Code:

SELECT DSAllocation.*, * FROM DSAllocation WHERE (((DSAllocation.Transaction_Month)=[Forms]![DSMainFrm]![cboTransaction_Month]));

You can do this easily enough using the query builder but you can just paste the above string into the Record Source of DSProductionFrm and it should work, just so long as you rename the combo box to cboTransaction_Month

you can then just requery the subform in the after update of the combo

Reply | Quote

Hi, thanks for that, I’m out of office for a few days so I wont get a chance to try this out for a bit. I now I’m terrible at naming things so sorry about that.

Few quick questions, I take it I should remove the master/child link and add something like that to the record source of all 3 sub forms and requery the 3 of them after update on the combo?

thanks again for the help.

Cheers

Reply | Quote

No the existing parent/child links are fine, but you need the month in addition.

Reply | Quote

Contrary to what I previously said, and although I haven’t tried it, you can have multiple criteria in the subform links if you separate them by a semicolon.
you may like to try that.

Reply | Quote