Queries :: Use Of Arrays In Forms
Dec 27, 2014
I have a form that allows me to sell animals in a group. While some fields, (i.e. buyersname, sellingunits, datesold) are good for all, some fields (i.e. AnimalID, SellingWgt, Sellingprice, Picture) need to be specific.
Having said this, Using a query for GroupSales, I filled a list box of all the animals that are to be sold. Below the list box two command buttons (1-ALL) and (2-by Group).The only difference being here, is that by clicking cmdALL all animalIDs listed in the list Box will be highlighted. The By-Group means the user has to highlight the IDs they want to sell..
In either event, there will multiple IDs. If I can use arrays here, I could include the other fields for each animal that has been chosen. These would then be updated/appended to other tbls.
View Replies
ADVERTISEMENT
Jun 25, 2007
Hi all
I need some help with an array.
I have 10 fields each with 5 possible values. At the moment I've written 50 if then statements.
is there a cleaner way to do this?? I thought perhaps an array of some sort may do the trick.
Any advice is appreciated
Damon
soc5 = DLookup("[effort]", "values_effort", "ID=5")
soc4 = DLookup("[effort]", "values_effort", "ID=4")
soc3 = DLookup("[effort]", "values_effort", "ID=3")
soc2 = DLookup("[effort]", "values_effort", "ID=2")
soc1 = DLookup("[effort]", "values_effort", "ID=1")
soc0 = DLookup("[effort]", "values_effort", "ID=0")
's1
If Me.txtFieldS1 = 5 Then Me.txtS1 = soc5
If Me.txtFieldS1 = 4 Then Me.txtS1 = soc4
If Me.txtFieldS1 = 3 Then Me.txtS1 = soc3
If Me.txtFieldS1 = 2 Then Me.txtS1 = soc2
If Me.txtFieldS1 = 1 Then Me.txtS1 = soc1
If Me.txtFieldS1 = 0 Then Me.txtS1 = soc0
's2
's3
's4
..
...
...
View 2 Replies
View Related
Jun 23, 2005
I am not even totally sure that is what you call it. . but I have most of my experience in VB.NET and this VBA in Access 2002 is killin me. HA. . .
I am trying to dynamically create checkboxes (50 of them) in 10 rows and 5 columns. This is part of a "im bored" project and would like to be able to tackle the dynamic controls area.
I am thinking I want to keep the naming convention as "chkBox01. . .02. . .03" so that I can use the mid() function to determine its "value" and apply the value in the proper place.
I suppose the only thing I need is an example if anyone has one of a checkbox or any control for that matter being made by VBA and not placed on the form manually. 50 check boxes suck when trying to rename them all. sigh
View 11 Replies
View Related
Jan 24, 2008
Hi,
How can I determine the length (size) of a dynamic array so I can iterate over it?
Thanks,
Mike
View 2 Replies
View Related
Jun 1, 2006
Is there anyway to name text boxes in an array format?
i.e. Text(1,23)
Or if not is there anyway to use VB to cycle through the text boxes
i.e
i = 1
Loop until i = 25
Text(i) = codehere
i = i + 1
Exit Loop
Matt
View 3 Replies
View Related
Nov 11, 2013
What's the best way of storing arrays of data within a cell? Or should I be creating fields to the size of the array.The size of the arrays are unknown at the moment.
View 7 Replies
View Related
Aug 28, 2012
Are there no arrays in Access? I am looking for a way to put sequential dates onto a calendar. An array would work fine, with an offset for Weekday(FirstDayOfMonth).
View 1 Replies
View Related
Mar 4, 2014
Question 1:
I am attempting to make a macro to pull information based on a clicked button to provide information to another form. While my example below will be rudimentary, I am attempting to create so that it can be applied to 450 buttons, hence the need for it to be a macro and not simply code per button.
For example if I have 3 buttons, captioned: red, green and blue. If I click on the blue button, it will open another form with a text-box that will say 'blue'. Likewise, if I click the red button the text-box will be changed to 'red'.
In the code below the section that I need to change is: Command1.Caption. Using this data I can pull from the one button to create the value, but I need it to be based on an OnClick or something in order to pull the value from the right button.
Example:
Function Macro1()
On Error GoTo Macro1_Err
DoCmd.OpenForm "Form2", acNormal, "", "", , acNormal
Forms!Form2!Text0.Text = Forms!Form1!Label0.Caption
DoCmd.GoToControl "[Text2]"
Forms!Form2!Text2.Text = Forms!Form1!Command1.Caption
[code]...
Question 2:
I am trying to use a query to change button colour on the basis of values stored in a table.What I want to do is change the button background after comparing the button name to the same variable in a table, and determining another variable.
IE:
Button1, Button2. In table: Button1, Val = Y, and Button 2, Val = Z
Pseudo Code:
Array: From Button1 to Button2
Query for Button1 against table
If Val = Y, Button1.background = red
Else if Val = Z, Button1.background = blue
Else Button1.background = black
End if
View 4 Replies
View Related
Jan 21, 2014
I am building a database which tracks tasks assigned to employees. I want to have one of my forms displaying a view which shows at a glance which tasks are assigned to whom at what times of the day, with a click opening a task for editing. My current attempt is to create a crosstab query with the employees as the column headings ("dynamic crosstab" query) and a table of time values for the hours of the day providing the row headings, with the task as the value.
I then created a form based on the query with Datasheet view as the default view (apparently necessary to set properties/event handlers to query values. I got the idea from the Northwind sample database) However, the form fields do not update to reflect changes to the employee list and therefore the onClick handlers are not applied to newly added employees. What to do, besides for programmatically setting the event handlers?
View 2 Replies
View Related
Sep 4, 2013
I have a couple forms with Combo boxes that look up data in queries. I noticed in testing that I could accidently type a "~" (and other characters) in the field and once I do, it causes a runtime error that shuts down the entire app (as opposed to letting the user backspace out of it or resetting the field). Before I put in some "Before Update" code to prevent the system from crashing, I want to make sure I am on the right track. I have already set the combo box to just show the list, not allow edits, limit to list, etc. Is there a way to prevent the user from doing this either by preventing the keyboard from working on combo boxes (not preferred) or by trapping it before the system crashes? I would think I could validate with a recordset, and create my own path out for the user, but I was hoping the combo box would have sort of done that work for me.
View 1 Replies
View Related
Oct 21, 2013
How to make my queries run when they are in a navigation subform. I am having a hard time finding something on line that explains this process well? I keep getting prompted to enter a parameter when i go to the form in the navigation setup, I have the query running off a combobox in he stand alone form, and i can bypass the error if i make the form a popup, but i want to learn how to keep it as part of the navigation form itself.
View 5 Replies
View Related
Jul 8, 2007
Is there any way to make a form that will allow me to select the query which i wish to use, rather than just having a different button that will execute each query individually?
View 6 Replies
View Related
Nov 26, 2005
I have a database that I use to track my workouts. I am very new to Access and am just starting to learn some simple VBA code.
I have the following tables:
tblBodyFocus
Body Type
tblExcercises
Excercise_ID
Excercise
Body Type (Foreign)
tblWorkoutLog
Log_ID
Excercise_ID (Foreign)
Date
What I would like to do is generate a query that will give me a summary of workout dates and Body Type. Each date can have several excercises but each excercise only has 1 body type associated with it. What I want to avoid is multiple listings for body types. In other words I want to generate this:
Date -Body Type
10/25/05 -Arms
10/25/05 -Legs
11/2/05 -Arms
11/2/05 -Abs
but not:
Date -Body Type
10/25/05 -Arms
10/25/05 -Arms
10/25/05 -Arms
I'm new to this so I may not have given you enough information. I don't have any code to post at this point but please let me know if I you need any additional info.
Thanks.
View 2 Replies
View Related
Mar 15, 2006
Hi All,
1st post or should it be last post..:) I am a complete novice with access so please be gentle. My problem is simple well not to me. I have a small db in access 2003 with which i created a form with 6 buttons each to supposedly run a querie..the queries have all been written previous as the db was originally created in access1.0. I am unfortunately unable to do this can anyone point me in the right direction please...thank you Williebear
View 7 Replies
View Related
May 4, 2006
I don't know if this could be done but is there a way that a form can be created where users type in Employee Info such as name or SSN and it calculates the total number of vacation, sick and person time he has left.
I've already created the queries, but i don't know how or IF i can integrate the two.
There are several tables
Employee
SickTime
PersonalTime
VacationTime
For each employee in the "Time" tables, a sum of their time taken and time accrued is calculated to provide the total time available.
Thanks!
View 2 Replies
View Related
Jul 1, 2005
I've read quite a few posts and noticed that quite a few people have said that forms should be created from queries. Is this personal preference or a must.
Like I said on a previous post I am going on a one day Access soon, so maybe I should stop reading and playing until I have completed that, but my mind is running away with what I want to do.
View 14 Replies
View Related
Feb 1, 2007
Hi There,
I am trying to open an Access database which was created a few years ago... When I open it to edit one of the forms I fail to see the forms etc that are in there.
Is there a hot key you hold down when opening the database to be able to open Access in full edit mode showing all forms queries etc
View 9 Replies
View Related
Aug 28, 2007
Hi
I have just put together a small database, and have used the user-level security wizzard to set up groups so i can restrict access to the database.
I have also been through all the user and group permissions and tried to set the security up so that the data entry staff can enter data but not change any of the forms or queries.
This is where i am hitting the wall.... although i have set up this protection, when i access the database under the staff name and password I am still able to view the forms and queries in design view and change them.
Can someone plaese point out where I am going wrong
Thanks very much :)
View 5 Replies
View Related
Feb 18, 2006
I'm new to the forum as well as I am new in using MS Access, so please don't lough :) I wasn't even sure whether this is the right subforum to post to....
Anyway here it goes....I have this really big database (nearly 5000 records) and I was wondering how could I make a query, perhaps outside of the Design View, and making it appear like a form e.g. let's say I want the query to display in form style (or even report...) the rest of the fields concerning a certain record when I just give the ID number of a person.
Should I turn to code solution?
Help anyone?????
View 2 Replies
View Related
Jul 23, 2007
Hey,
I am working on my first access project (please be patient and try to explain like to a child). My problems however are past the basic level so please bear with me ....
I am working on a datbase which deals with laboratory data from patients which have different date stamps - ie. each record has a 'valid time' (time of blood drowing) a 'transaction time' (time of entry into the DB) and 'delete time' (deleted recordes are just given a date and not really deleted).
The idea is that the user should be able to watch the status of the DB at different past dates (ie what did the doctor know about a patient blood tests a month ago when he made the decision to give him a medication which eventually harmed his kideneys). The user should also be able to change values of records, delete records (which means to add a date to the deleted date field) and to add new records.
I want to do it all using one form.
Things I have done so far:
1. Query by form which locates a specific record (Main-Form & singleRecQuery)
2. A subform in Main-Form which displays the results of singleRecQuery (Single-subform). The query and subform aer updated by a macro once I enter all the data into main-form.
3. A qury by form derived from Main-Form which locates a range of values (Range-subfrom & Range-Query)
My problem spans both forms and queries (so I am posting in both forums):
1. I want to create an update query that will draw its data from Single-subform (the form which displays the results of singleRecQuery). I have created the qury without a problem but the fields in the form are locked for entering data (maybe because they are bound). Is there any way to enter data in such a form which is based on a query and then use the new data in another query ? (I tried allowing data entery in the forms properties but it deos't work).
2. I have tried to put a criterion in the queries on the "Delete Date" field so that the quries will not display the deleted records (I have tried "is null" "is empty" and "is missing") but when I run the query with these parameters I get no records back.
3. In the form I have a command buton which runs a macro that runs the qury and then update dthe subform. Is there a way to get this button to run different macros after different events in the form or do I have to add a button for every specific task I want.
4. As I have mentioned the Main-fom is supposed to run 3 differnt queries and display the results on the same form. I know how to make a subform invisable but is there a way to place all three result subforms in the same spot ?
Sorry it is so long
Thanks
EJ
View 1 Replies
View Related
Nov 23, 2005
Hi,
I have a form and its source is a query. There are only 5 things on the form that can be modified, the rest is just displayed.
the 5 things are within a table b. In my design view i link up table b with the already made crosstab query. (everything within the crosstab query are just for display on the form). Within the design view I put * from the crosstab query (everything) and then the fields from table b (all 5 of them).Everything on the form is working and is linked up properly, but when i go to type in them it is locked. (well they arn't within the property), but i cant type in them. This is for all 5 fields on the form.
When i then go back into the source and remove the crosstab query, i can then type into the fields.
Any ideas?
** p.s When i create the same query normal it doesn't allow me to type in the datasheet view either **
Thanks
k0r54
View 12 Replies
View Related
Jun 19, 2006
Please help!!
I am creating a program that will allow the user to select a sale and pull up only those product items associated to that sale in the details. Any help would be appreciated. Then with in the details section the user will/should be able to enter in the Quantity needed to order. Any suggestions on how to do this. My tblSale does provide the data needed so how do I get the data to go to the form?
I am new to this forum so if I have not asked properly I apologise now.
The Database is attached. Form Orders is the form that I am using to accomplish my task. The getproducts4Sale Query is the information needed.
View 2 Replies
View Related
Mar 2, 2005
I currently have a query and a table which I'd like to create a form for. The query has people's information in it and the table has RSVP information in it (if they need parking, etc.). I'd like to create a form so that I can type in the person's name and then it brings up their address (just to confirm it's the right person) and then i can enter in the RSVP information. Any suggestions?
View 14 Replies
View Related
Jun 15, 2014
I am creating an end of week report, which needs information from Sat-Fri, so basically it will be one sub form, but used 6 times.
I'd prefer not to have to make 6 subforms, with 6 queries, but rather using 1 subform and 1 query 6 times.
For example, on the parent form, I will add the subforms, and name them "Sat", "Mon", "Tue" etc...
I want to make one query that filters the information based on the name of the subform:
Day: Format([Date],"ddd")
Where: Day = Me.Name
I can't figure out what to ask the query to refer to, to retrieve the name of the subform it's currently sat in.
View 3 Replies
View Related
Mar 8, 2005
I am building a very simple Form that will check prices against my main database. So far the user can input a barcode into a text box, which then drives a query which in turn opens a datasheet with the results.
What i'm trying to do is get the query to return the results in a Subform on the original form. But currently i can't get the subform to refresh upon entry of a barcode into the text box.
Can anyone help? Apologies if my explanation is not very clear.
View 2 Replies
View Related
Jun 11, 2007
Hello i am a college student and i was just getting on with my database assignment. i don't want any one to do it for me i just want someone to point me in the right direction. i am making a basic Encyclopedia on planets for my project. i have made one form which i want all my other forms to look like. this form is getting its information out of queries. this form is opened when i click a picture of the planet. so i was wondering if it was possible if i could link all the picture up to the one form but each picture opens a different set of data with in this form. i have already thought of just copying the forms and changing the control source. just wondering if there is a different way of doing it.
thanx
tom
View 3 Replies
View Related
