Full Version: muliple entries allowed in one form.
UtterAccess Forums > Microsoft® Access > Access Forms
Pages: 1, 2
RLWAlker
Thanks to many very nice people here I managed to create a form that had a Type of Animal field and then a Individual Animal field. Each was synchronised so that you could select the Type of animal and then the next list box would show only the animals of that type.
created it as a subform which I will place in a larger table that I hope to add the date, time in and time out, person using the animal, kind of education program it is being used for.
Often more than one animal is used in an education program, sort of like when one customer will order more than one item at a time so I would like the form to allow more than one animal to be chosen. When I put the form in database view it shows the table format and allows the list dropdown box for animal type and then allows you to choose one of those animals in the next column just as planned..my problem...it does not allow me to continue down and add more rows/records.
I have seen it done (Northwind sample database order form) but not with synchronised boxes.
Any help on this matter is greatly appreciated.
Thank you.
jmcwk
What exactly is it you are trying to do? I think you want a mainform with a subform whereas your mainform is your schedule and the subform is the Animal/s scheduled for that date????? The cascading Combobox you describe is not what you want for this however it would be useful in other ways. Can you give a little more explanation regarding the intent of the DB?
RLWAlker
I am a zookeeper and am setting up a database to track how and when the education animals are used.
need to track each education program: Person running the program, time in and out, total time, type of program, weather conditions and then which animal types were used and which of those animals. I like having the synchronised boxes for this so that they will be able to select animal type and then which of those animals they want to use, but i would like it in database look so that they can list numerous animals for one program.
Thank you.
jmcwk
Do the Animal Types need broken down? For Example Snakes: Cobra,Rattlesnake,Python etc. How do you want your Database structured? For example do you want the Data inputted by Person Running the Program,Program date,Animal Type,Program Type, etc. reason for all the questions is only a means for me to advise you on the approach to take.
RLWAlker
The animals being broken down is more of Animal Type as in Chinchilla, Degu, African Pygmy Hedgehog etc. and then when you click on one of these it gives you a list of the animals available e.g. if Chinchillas was cosen the next box shows the names of the three Chinchillas available, Paulie, Rose Red, and Sussie. I have altrady got this as a subform and I like the way it loks, the problem is that it only allows one row, not multiple.
This form would be filled out by the people signing the animals out and then I would want to set up reports that would show monthly, yearly and total use of each animal species, each animal, who used which animals the most, what the most popular animals were, individual educator's animal preference, etc.
jmcwk
I am currently at work and the server will be going down for maintenance shortly. I woul dbe glad to work with you on the is it possible for you to attach a zipped copy of your DB no larger than 500000kb and I will take a look at what you have thusfar? Do not know how long the server will be down but I will certainly get back to you. Also when they are checked out could it be more than one animal type? If your subform only allows one row I do not think you have it set up properly linking it to the promarykey of your mainform????? Got To Run before I get Zapped!
RLWAlker
I haven't had a chance to zip the database yet. I will be able to send it later this evening. I am on the east coast so it shouldn't be too late over there in Washington state. My database so far is not very good. THis is my first time working with Access and I am working through the Complete Idiot's Guide. I am only half way to becoming a complete idiot.
ove WA, went there two years ago.... and I did not want to leave.
Rachel
jmcwk
Rachel,
Thank You for the kind words on this wonderful State! I feel the same way don't worry we ALL started at square "Complete Idiots Guide" thats pretty funny frown.gif anyway I am anxious to see what you have thus far and will take a look and see what I can do. Don't be so hard on yourself there are many fine folks here on the Forum that are more than willing to help those that need advice or help regardless of the skill level.
RLWAlker
Here is what I have so far. I haven't finished putting in all the possible Animal Handlers but as you see I have it set up so that they can be synchronised as well. I also haven't set up weather type or anything.
Thank you for your time.
Rachel
Humby
My recommendation would be to include the list of animals from each programme in a seperate table, linked to the programme's primary key, e.g.
blAnimalsInProgramme
fldAnimalUsageID as AutoNumber
fldAnimalID <-- ForeignKey to the Animals Table
fldProgrammeID <-- Foreign Key to the Programme table
Then, on your form use the combo boxes to choose an animal, have a button to add the animal to the programme, and have a listbox to show all the chosen animals (and possibly another button to remove them).
jmcwk
Rachel,
See Attached open the frmScheduleProgram you will see a button at the bottom of the form Select Or Remove Animals click it. Another form will open and you can either select or remove an existing selection. After closing the form hit the refresh button to the left of the Select Or remove Animals. Let me know if you need more help or changes, additions, etc.
HTH
jmcwk
Rachel,
It is just that as I filled out the tables, you can see that not all the locations are appropriate to the site and the same with the staff. That is why I was really hoping to synchronise certain boxes e.g. make it so that when you clicked Franklin Park, only the possible locations would show, and the same with the staff, if you clicked on Education staff then it would only list those particular staf members. Some of the staff catagories don'y have any particular person listed and in these cases it would be a box where you could type in a name (we have too many zoo teens that change to list them all).
<
The way it works is that the program person would fill out the form before they leave with the animals and then when they get back they would fill out the time returned. Would it be possible to make it so that you just click a button at time in and it will enter the current time and then the same on time out...with the option of entering it manually as there are a lot of people who may forget.
Another box which I just thought about would be a box for someone who is a animal keper to "sign" the animal back in..i.e. a box at the bottom that they could fill their name in. I only just thought of that....
Have you ever seen the Northwind example database. I sort of thought that would work the way that it allows you to put in order items in a list format with as many lines as possible. That is where I got the idea for the sychronised boxes.
jmcwk
Rachel,
You have a Site Table and a Location Table. Based on the Site Table what are the Locations set to?
In other words you have
Sites
Local
Franklin Park
Stone Zoo
Off-Site
And You Have
Locations
Meeting Barn
TF Classroom
Animal Exhibit
Outside Pet Place
Farmyard
Little Critters
Pine Knoll
Can Franklin Park and Stone Zoo have the same locations? Or does for example the location of Meeting Barn only apply to Stone Zoo?
RLWAlker
Hi John,
JUst have a few moments at the moment.
THe meeting barn is only at Franklin Park Zoo. In the tables that I set up I did SiteID which is either 1, 2, or 3...
1 is franklin park zoo, 2 is stone zoo and 3 is offsite. Each location should have a siteID with it I believe. All the ones listed above are for FPZ. then we need CDC, outside Gift Shop, Auditorium, Animal Exhibit, other for Stone Zoo...also Tiger Slope is also at FPZ. Each location that I listed should have a number near it 1, 2, 3.
The reason why I was thinking about the selection of animals like that of northwind was so that I could have them choose their animal type and then just then have a choice of those animals, click on one then a new line appears to give them their next option. THe other way is fine though, it just means that they will have to go through the whole list rather than it being broken down.
Thank you again.
jmcwk
Rachel,
Yl,
Your Site Table like you say has 3 Location Id's however Your Location Table did not have a Site ID Field to link the two together. If you could either 1 provide me with the appropriate site ID for each location I can take care of it OR open your location table and place a SiteID field in in Number Data Type and save. then place the correct SiteID Number against each location and just send me that table zipped Or the entire Db either way. No problem from tis end. frown.giffrown.gif
RLWAlker
I will do. I was just looking at the database and realised that I hadn't been able to choose some of the animals and then realised that they had already been chosen in other records. Is there a way of being able to reuse them one the time that they have finished being used, i.e. signed back in, they would become available again?
Thanks
jmcwk
Rachel,
will fix that yes basically I am going to make it much more user friendly now that I know what you are looking for. It will be completely different from what you currently have. Send Me what you have at present regarding the Location/Site thing and any other additional table/s that you have added to ie. Staff mambers,Animals, etc.
RLWAlker
I judt looked at the database that I sent you and the second column in the frmLocation gives the site ID.
It is as follows:
LocationID SiteID Location
1 1 Meeting Barn
2 1 TF Classroom
3 1 Animal Exhibit
4 1 Outside Pet Place
5 1 Farmyard
6 1 Little Critters
7 1 Pine Knoll
8 1 Tiger Slope
9 1 Eco Center
10 1 Other Event Area
11 2 CDC
12 2 Animal Exhibit
13 2 Outside Gift Shop
14 2 Petting Corral
15 2 Other Event Area
16 2 Auditorium
17 3 School
18 3 Organization
19 3 Hospital
20 3 Elder Care
21 3 Public Meeting Place
22 3 Government
23 3 Conference
24 3 TV Station
Hope this helps.
Jack Cowley
John -
If you want to see some incredible artwork go to Rachel's profile then go to her web site. Absolutely amazing stuff...
Jack
RLWAlker
Thank you Jack.
achel
Jack Cowley
Rachel -
You are very welcome! The pen and ink of the cheeta's is incedible, as are all your other animals. Your Pointillism techique is absolutely stunning. I hope you get millions of commissions and make tons of money, though most artists aren't that fortuanate. Hopefully you will be the exception.
Truly wonderful and amazing art...
Jack
jmcwk
Jack,

Yes I have seen her work quite talented and gifted! I agree with you 100% have been offline awhile had to renew my Norton subscription what fun crazy.gif
Edited by: jmcwk on Fri Apr 28 19:04:40 EDT 2006.
Jack Cowley
John -
25 well spent! I always update with Norton as I do not want to get bitten...
Yep, Rachel is an amazing woman as well as an extremely talented artist. I am sure she appreciates YOUR talents and your continuing support with her database. I, too, applaud you for giving of yourself so she can have the db that she needs.
Well done, John!
Jack
jmcwk
Jack,
Appreciate that Jack however your talents should be recognized as well in the help of the List Box fiasco. I think after reading her latest posts I am going to take another approach completely.
Jack Cowley
John -
The List Box thingo was an exciting time! Did you get it working?
Good luck on the new approach and if you think I can add anything, other than pure chaos, give me a shout, but I think Rachel is in good hands...
Jack
RLWAlker
THank you both. I don't know about being an amazing woman...LOL
nd, yes, I am one of the many artists that does not make money very much. Most of my efforts (Cheetah Pointillism for one) was done to raise money.
Odid a 24" * 36" painting of three scenes from oregon (I will send a low res image when I get on my laptop later). THis is the most I have ever gotten for a pic $300, but it took me about 30 hours to do...LOL
If you know anyone who needs a portrait of their pet done I will give them a reaaly good deal.
*grin*
Take Care
Rachel
RLWAlker
And I will learn to spell also...
Jack Cowley
Rachel -
eing old makes me wise (so says conventional wisdom) and I say you are an amazing woman, therefore you are! Accept it!
I wish I had a pet as I would commission a portrait, but pets don't like curmudgeonly old men...They tend to run off in hopes of finding someone a bit more timely when it comes to feeding time....
A Liverpudlian, huh? Last one that I recall was a 'Beatle' and he/they got very rich... There may be a trend... I hope so!!
I love your altruism and your commitment to making a difference... Thank you for all that you do to make this a better world...
Jack
Jack Cowley
Rachel -
Yl -
Your spelling is excellent! Colour does have a 'u'...
Jack
jmcwk
Rachel,
Am on it and having fun doing it as slow as I may be and Jack Thank You for the offer I am sure somewhere along the trail I will need some help. Yes I did get it to work however found it to be cumbrsome and really not practical from the users standpoint. Right Now I am in the process of synchronizing the various combo boxes and will go from there. Sites to locations, Staff Types to Staff etc.
Jack Cowley
John -
You are welcome and it sounds like you are on the right track. Sorry that the listbox thing didn't work, but if if you don't give it a go how will you know?
Jack
RLWAlker
I will not make a habit of including non-access files but here is the art that I was talking about. Original size 24" by 48" , it is three photos of it put together hence the tone differences.
Jack Cowley
Rachel -
eautiful! The right side reminds my of the Northern California coast. And you were right - The buyer got a real deal!!!
Thank you so much for sharing your art!!! If you have anything else you would like to share my email address is in my profile...
Jack
jmcwk
Rachel,
eautiful! I have attached a new version of the Db check it out and see if that works better for you. Needs more tweaking let me know.
RLWAlker
the form is exactly what I wanted for entering data. You are so clever.
just want to know one thing...is there a way to make the animal input area blank before inputting animals? It is just that if the record before has 3 animals listed but the next program only uses one animal then the next two lines still show the other animals even though I hit refresh.
Thank you
Rachel
RLWAlker
Oops...one other question John,
How can I set up a table like the tbProgramDetail but instead of showing the IDs of each animal, weather type and the like it shows the actual name of it?
Gosh, I am sorry....this is exactly what I was looking for and all the credit is going to be given to you. I am not sure if it is possible but as we are a non-profit I may be able to get your time as a charity donation.
Thank you, thank you
Rachel
P.S. I am going to try my hardest to do the reports myself so that I can learn that process but I may have an odd question...but I really should do something myself!!
jmcwk
Rachel,

You should store the Ids in the tables If you want to show the Names corresponding to a particular ID then use a query and then base your report on the Query. Look at the qryProgramDetail in design view and then how the relationship/s are made with the IDs. Are the Combo Boxes on the Main and subform the way you wanted? Is the Date and time like you wanted? Need any help do not hesitate to ask. I am at home so I do not see the replys but I will keep an eye open.

Rachel an example would be in the qryProgramDetail you have a temperature ID however it does not show the actual temperature representing that ID

add the tblTemperature to th query
after doing so double click on the temperature field and it will be added to the grid and presto you now have the temperature.

HTH


Edited by: jmcwk on Sat Apr 29 15:02:17 EDT 2006.
RLWAlker
John,
Yes, everything else is fine. The only issue is that when I click on PR or another Staff Type that doesn't have options it says there is an error, and I am unable to type in a name in the next box. I have to close the whole window and begin again. Also I am still having issues with choosing the animals. Is there any way of blanking them out each time you open a new record to fill out.
Thanks
Rachel
P.S. I am leaving now definately but I will check back when I return.
Take Care
jmcwk
Jack,

Heres one for you Rachel has combo boxes on the mainform one of which selects an employee type and based on the type the second combobox is used to select the Employee, what can be done to eliminate the error (no employee) for the second combo to choose from?

This is the code in the AfterUpdate:

CODE
  Private Sub Category_AfterUpdate()
roduct.Requery
Product = Me.Product.ItemData(0)
End Sub


There are 6 stafftypes in the first combo only two of the 6 having relationships meaning there are none for four of them.

This is the error:

You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)
You tried to assign a Null value to a variable that is not a Variant data type. Use the Dim statement to declare the variable as a Variant, and then try the operation again.
Edited by: jmcwk on Sat Apr 29 15:38:56 EDT 2006.
jmcwk
Do not know if tis is what it was lookinfg for however this is what I am using now and it seems to work. I also am doing a requery when the second combo gets the focus????????????
CODE
Private Sub Category_AfterUpdate()
Dim Product As Variant
Product = Null
Product = Me.Product.ItemData(0)
Jack Cowley
John -
I have not used the ItemData thing for cascading combo boxes but a query for the second combo that is filtered by the first. If there are no record in the second combo, based on the selection made in the first combo, there is no error. This article is the method I use.
Sorry to take so long to get back to you as I was deep into a db and did not hear the computer ding...
Jack
jmcwk
Rachel,
Can you elaborate a little more on this?
<
When you go to a NEW record the subform (where you select the Animals) should be blank???? until you enter an animal/s.
jmcwk
The Computer Ding! Your ears are bettr than mine I need a GONG! anyway I will look at the article I seem to have it working now but am sure there is a better way. Thank You Jack. Just sitting here unlike yesterday it is quite overcast,cloudy, and cool sad.gif
Jack Cowley
John -
My day started overcast and cool too, but the sun came out about an hour ago so I am a happy dude once again...
I want to say that I think it is very special that you are willing to give your time to help Rachel with her db. You are supporting a non-profit cause that I believe in and by giving of your time and talents you are helping to make something better that would not happen without your help. Thank you from me!!!
Jack
RLWAlker
Hello John,
When I go to a new record the subform records don't refresh to blank, so I have to reuse the lines that are full and if I don't have enough animals to do this then the last few from last time stay.
HAs I mentioned though the overall database is exactly what I am looking for.
Rachel
Jack Cowley
John -
Not sure if you got this post or not as Rachel attached it to my last post. You will find her post here.
I will tell here how to add a post so that you get notified.
Jack
Jack Cowley
Good Afternoon Rachel -
This is Jack and I wanted to let you know that since you used the Quick Reply I got the email that said someone had posted a response. Since you want John to see your post here is how to do that - Pick his last post and to very far right of his avatar you will see a list of things you can do and the second one is Reply. If you click on that a new window will open up and you can type in your question in the Reply box. When you click the Continue button the post wil be added to the thread and John will get an email, just like you got notified that I have responed, and he will know to take a look to see what you said... I did notify John of your latest post so if he is around then he should be contacting you shortly.
Jack
jmcwk
Rachel,
I have been out for awhile and am about to go out again to dinner, I will take a look and see if I can figure out what is going on when I return. I do know that when I go to a new record. Mainform is completely blank and the subform is completely blank. Once I populate the main form and go to the subform the selections are available.
Jack, Thanks for helping out!
RLWAlker
John,
The animal subform worked this time. I have no idea why it didn't before, so the only issue seems to be the stafftype and staff...
Sorry.
Rachel
jmcwk
Rachel,
just figured out what you have been trying to tell me.
You enter Domestic Horse for example in record Number 1 and then go to record number 2 Domestic Horse is not provided as one of your selections. Is that what you were saying? I understand the part about the Combo Box and am working on that. Hang in there we will get it sooner or later :frown.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.