Export thread
Microsoft Access
#1
Necronic
Necronic
I was thinking of writing this into the programming section, but I guess that's not really what this is for. I've been working on a database at work for the last couple months whenever I have a free minute or two, and I am really starting to hit a brick wall.
I have the data in tables, and I can hand write/modify my queries to get the info I need. But now I need to start getting it useable for other people, and that won't be an option. So I have finally started screwing around with Records/Forms, and am having a hell of a time making it work.
Here's my current form I am working with. It's for a batch chemical reactor.
Datasource -
General Data - 1 record per reactor run
RunData - Many to 1 relationship with "Generaldata.runnumber". Contains 100 some odd points of temperature,
gas flows etc for each run in General Data
I've bundled the rundata into a query ("runquery") with a parameter for the "runnumber".
On to the form.
The form has a combobox tied to GeneralData.Runnumber. User should be able to select a specific run with this. Which leads me to my first problem:
1) Control Source - If I set the control source for the combo box to GeneralData.Runnumber then I get the list of runs, but I am unable to select a run or type anything into it. That field isn't locked or anything, anyone can edit it.
I was able to get around this by removing it as the control source and just including it as the Row Source. With the combo box as the row source, I went back to the query "runquery" and linked the parameter to the combobox.
So this means that when someone scrolls through and selects some item on the combo box it allows the runquery to find a specific run based on what's in the combo box.
Follow me so far?
I'll write more later, got to get back to work.
I have the data in tables, and I can hand write/modify my queries to get the info I need. But now I need to start getting it useable for other people, and that won't be an option. So I have finally started screwing around with Records/Forms, and am having a hell of a time making it work.
Here's my current form I am working with. It's for a batch chemical reactor.
Datasource -
General Data - 1 record per reactor run
RunData - Many to 1 relationship with "Generaldata.runnumber". Contains 100 some odd points of temperature,
gas flows etc for each run in General Data
I've bundled the rundata into a query ("runquery") with a parameter for the "runnumber".
On to the form.
The form has a combobox tied to GeneralData.Runnumber. User should be able to select a specific run with this. Which leads me to my first problem:
1) Control Source - If I set the control source for the combo box to GeneralData.Runnumber then I get the list of runs, but I am unable to select a run or type anything into it. That field isn't locked or anything, anyone can edit it.
I was able to get around this by removing it as the control source and just including it as the Row Source. With the combo box as the row source, I went back to the query "runquery" and linked the parameter to the combobox.
So this means that when someone scrolls through and selects some item on the combo box it allows the runquery to find a specific run based on what's in the combo box.
Follow me so far?
I'll write more later, got to get back to work.
#2
Necronic
Necronic
So I fixed some problems and came up with a whole handful of others. I am going to c/p my post from another forum:
My top table in the heirarchy is "tabCatData" which gives information about a specific catalyst batch. That table has a 1 to many relationship (through CatID) with "tabReactData" which gives some simple information about a reaction (date, operator, etc). This has a 1 to many relationship (through ReactID) with "tabReactDetails" which gives time based information about the reaction (gas flow, pressure, temperature, etc).
tabCatData < tabReactData < tabReactDetails
With this I have a form "formCatData" which has a combobox combCatID tied to CatID in "tabCatData". Based on what catalyst I select I get a nice little control chart showing me performance data from tabReactData. There is also a subform subReactData which has a combobox combReactID which is updated when a catalyst is selected in combCatID to show the ReactIDs that line up with the catalyst.
Up to this point everything works well (which is pretty cool that I got it this far)
Within this subform I have a graph gTemp which shows the data in tabReactDetails which lines up with the ReactID selected in combReactID. This is where it doesn't work, and I can't seem to make it work right. Here's what happens.
Say I select a catalyst, I get the control chart on the main form, and the subform populates the combReactID and the graph gTemp within the subform plots for the first ReactID in combReactID. However, when I open combReactID and change the ReactID selected the graph gTemp won't update. On the other hand, if I have a text box field for ReactID I can hit the "select next record" and the graph updates correctly.
So I guess the problem is that I haven't linked the graph correctly to the combobox, and I can't figure out how to do that.
And of course on top of all of this I get to deal with the wonderful frustration caused by the complete uselessness of the Child and Master field links, which for some reason always declare the Chart as unbound no matter how I create it. I've tried manually typing in the combReactID to be the Master link, but I can't figure out the syntax (if there is any).
I've gotten this subform to work correctly as a standalone form, being able to select the ReactID and have it update the graph (looks great too), but bringing it in as a subform doesn't seem to work.
My guess is that I am doing many many many things heinously wrong with regards to my overall design which is the source of my problems. I've read that you should generally run forms bound to queries instead of tables, but I think I have effectively done that by having the rowsources being standalone queries themselves.
Anyways, any thoughts?
PS. Is there any indication that the next release of Access will have better graphing options? The wizards for access are absolutely atrocious.
My top table in the heirarchy is "tabCatData" which gives information about a specific catalyst batch. That table has a 1 to many relationship (through CatID) with "tabReactData" which gives some simple information about a reaction (date, operator, etc). This has a 1 to many relationship (through ReactID) with "tabReactDetails" which gives time based information about the reaction (gas flow, pressure, temperature, etc).
tabCatData < tabReactData < tabReactDetails
With this I have a form "formCatData" which has a combobox combCatID tied to CatID in "tabCatData". Based on what catalyst I select I get a nice little control chart showing me performance data from tabReactData. There is also a subform subReactData which has a combobox combReactID which is updated when a catalyst is selected in combCatID to show the ReactIDs that line up with the catalyst.
Up to this point everything works well (which is pretty cool that I got it this far)
Within this subform I have a graph gTemp which shows the data in tabReactDetails which lines up with the ReactID selected in combReactID. This is where it doesn't work, and I can't seem to make it work right. Here's what happens.
Say I select a catalyst, I get the control chart on the main form, and the subform populates the combReactID and the graph gTemp within the subform plots for the first ReactID in combReactID. However, when I open combReactID and change the ReactID selected the graph gTemp won't update. On the other hand, if I have a text box field for ReactID I can hit the "select next record" and the graph updates correctly.
So I guess the problem is that I haven't linked the graph correctly to the combobox, and I can't figure out how to do that.
And of course on top of all of this I get to deal with the wonderful frustration caused by the complete uselessness of the Child and Master field links, which for some reason always declare the Chart as unbound no matter how I create it. I've tried manually typing in the combReactID to be the Master link, but I can't figure out the syntax (if there is any).
I've gotten this subform to work correctly as a standalone form, being able to select the ReactID and have it update the graph (looks great too), but bringing it in as a subform doesn't seem to work.
My guess is that I am doing many many many things heinously wrong with regards to my overall design which is the source of my problems. I've read that you should generally run forms bound to queries instead of tables, but I think I have effectively done that by having the rowsources being standalone queries themselves.
Anyways, any thoughts?
PS. Is there any indication that the next release of Access will have better graphing options? The wizards for access are absolutely atrocious.
#3
Piotyr
Piotyr
Access is so bad that Microsoft doesn't use it. I wouldn't expect Access to improve much.
#4
Necronic
Necronic
to be honest the only beef I have with it is the graphing options, aside from that it is fantastic. Anyways, I got this to work about 15 minutes before I left work on friday. Still not sure how I did it...