Modules & VBA :: Scheduling System That Automates Some Redundant Tasks - Expanding Subforms?
Jul 28, 2014
I'm working on a scheduling system for my fathers company that automates some redundant tasks. Most importantly the system manages the order of processes on different parts (ie, part "tubes" may be cut, then drilled, then welded, then shipped).
I'm trying to create a form that allows for the editing and viewing of all the parts and the processes for each part for a specific work Order. Ideally the information that applies to all the parts (Work order Number, due date etc) would appear at the top of the form with all of the processes to appear beneath (Separated into groups - all the parts to be welded shown together). My problem with using sub forms for this was that I need all the records to be shown without scrolling. Is there a way to dynamically change the size of each Subform to match the required size, or is there a better feature to use?
View Replies
ADVERTISEMENT
Sep 2, 2013
I'm making an accounts package, the user creates job reports every time they complete a job. The user then creates an invoice which contains the information from a job report.Any one invoice can contain information from multiple job reports.There is a field called J_InvoiceNumber in the job reports table and field called I_InvoiceNumber in the Invoices table. A particular job report is linked to a invoice by using the same number in J_InvoiceNumber as was in I_InvoiceNumber. let me give you an example:
JobReport1, JobReport2 and Jobreport3 all have "1" in their J_InvoiceNumber Field
Jobreport4 has "2" in it's J_InvoiceNumber field.
so now Invoice1 which has "1" in it's I_InvoiceNumber field has 3 job reports and Invoice2 which has "2" in it's I_InvoiceNumber field has 1 job report.This works for presenting the information for each invoice in a Access Report but now I'm trying to make a table view just to see all the invoices and job reports in a big list.
The problem is that using a query presents me with the same information duplicated. So in the example the query would display I_InvoiceNumber as "1" for jobreport1 then a new record showing I_InvoiceNumber as "1" for jobreport2 and then another new record for jobreport3 again showing I_InvoiceNumber as "1".
What I want is for I_InvoiceNumber to be displayed once and then prehaps a expanding tree showing all the job reports sharing this number in their J_InvoiceNumber field.
View 2 Replies
View Related
Mar 25, 2015
I found a post where it shows how to create a single collapsable/expandable section. I am needing to do this for 10 sections with a lot of information in between each section. The coding that this post provides does not seem doable for that many sections. How to create a collapsable/expandable subform?
View 1 Replies
View Related
Jul 14, 2014
I'd like to expand me tree view automatically when a node matches a string variable from a table. In other words, when a node in a tree matches the given name (variable), the tree will expand all the way down to that name. I guess I need to use "For each node" and when the node and variable match, the tree structure will show up.
I have come up with something like this:
Code:
For Each nd In Me.tree.Nodes
If nd = level1 Then
nd.Child.EnsureVisible
Exit For
End If
Next nd
Me.tree.SetFocus
But this only shows the first level of my tree ( btw. I have a 3 level tree). I get lost inside the FOR when I want to make use of another two variables - level2 and level 3
View 1 Replies
View Related
Sep 5, 2013
I have been searching the web for a solution to this problem. I have created a scheduled task on Windows 7 to automatically run a macro from a command line, however, it seems to only run when the user is logged onto the machine. Is there a way to run Access without the interactive logon? I would rather not leave the machine logged in with this account, but if that is the only solution, so be it.
View 7 Replies
View Related
Jan 3, 2014
I created a form for scheduling appointments on access 2013 using a youtube tutorial. I got most of it work apart from the combo box that functions in a way that once the time slot has booked with a client it should no longer be shown in the list.
E.g.:-2/2/2014 10:00AM is already booked with a client
The combo box should no longer display the 10:00 AM timeslot but can be shown on other days
Below is the VBA code that i typed out. How to create the code to make the combo box work in that manner.
Option Compare Database
Private Sub cboTime_Enter()
Dim i As Date, n As Integer, oRS As DAO.Recordset, sSQL As String
Dim dLowerbreak As Date, dUpperBreak As Date, dDuration As Date
Dim dLowerPrecision As Date, dUpperPrecision As Date
cboTime.RowSourceType = "Value List"
[Code] ....
I found out that the combo box does not work like it does in the video i.e the option does not dissaspear even though their has been a booking with that time slot with another client.
Coding that is required to make the combo box work like that or is their an error within the code that is preventing the combo box to work in such manner ...
View 14 Replies
View Related
Dec 3, 2006
Hi all,
I am trying to add some more professional touches to a DB to get away from the whole '8-bit' feel that Access seems to have.
I would like to have a form that displays information and pops-up during a mouse over event. This is no problem.
What I am trying to achieve is an effect - that the form 'grows' making it appear to pop-up from nowhere, and then shrinks when the control loses the focus.
Just wandering if anyone has tried to achieve this and can offer any advice?
Thanks
Robert
View 2 Replies
View Related
Mar 13, 2014
I have our primary web based inventory system that I am exporting to Excel and using this as an import to Access for the main raw data for my database. This being inventory it changes daily so I am updating this table every day. When I try to append the table it ads all the records. I am wanting an easy way to add only the new records/take out the ones that are no longer there. Basically update the table with what is currently there.The only have I have found to do this is by running non-matching queries and update queries.
View 2 Replies
View Related
Dec 13, 2007
This will be a tricky one!
I have a bill of materials screen, where my operators will input parts that they require for a job
From this they have the ability to click on the part number they require to check if there is stock available in our stores
This will open the warehouse screen which is set as a continuous form.
The structure of the warehouse records is 1 line per batch. Each batch can contain multiple qtys.
I.e Part Number - JF5650 could show Qty: 50
When the operator goes into this screen i have given them the opportunity to reserve a some or all of the existing stock qty from a batch to a work order. They can do this by selecting the work order in a reserved combo box, and by typing a qty to reserve to the qty reserved text box.
Hopefully this is all making sense so far. Everything i have explained above works fine.
My problem is,
Many different work orders may require stock of the same part, my current setup does not allow the batch line to reserve against more than 1 work order.
Can anyone think of a way around this? At the moment i am playing on the idea of creating a seperate table for reservations linked by warehouse record which will allow the multiple lines but bringing that into a continuous form still has me thinking.
Adding multiple reservation and qty boxes is not an option. And i don't really want to change design to much as this db has 80 odd forms all doing there bit.
Thanks AOG (if i make any progress i'll keep this thread updated)
View 6 Replies
View Related
Sep 14, 2004
Each pay period, I must enter data for each employee that worked an extra shift. The table is simple:
Name
Period Starting
Period Ending
Number of Extra Shifts Worked
Is there a way to bulid a form that will keep the dates I type in the period starting and ending fields until I change them?
To clarify, I have 17 employees who worked extra shifts, and I don't want to have to type in the Period starting and ending dates for EVERY record. Thanks in advance for any help. I really appreciate it.
KellyJo
View 2 Replies
View Related
Jun 30, 2006
I wasn't sure where to post this, so here goes:
Very basically, I have two related tables that I'm working with.
TABLE #1:
tbl_MAIN
FIELDS:
REC_ID (Autonumber) Primary Key [PK]
EMP_NAME
EMP_ADDRESS
DEPT_ID (Foreigh Key [FK] from the tbl_DEPT table...)
ETC...
TABLE #2:
tbl_DEPARTMENTS
FIELDS:
REC_ID (Autonumber) Primary Key [PK]
DEPT_NAME(Text)
ECT...
Now, I'd like to create a form that can enter information on the employee, including the department that they're a part of. I'd like to avoid having duplicate departments in tbl_DEPARTMENTS, but I want new department entries to be created if an employee from a new department is entered into the form. I'd like employees from a previously existing department to get associated with the department entry already in tbl_DEPARTMENTS. Is this possible? What's the best way to do this? I've tried every way I can think of, but I can't get this to work.
Thanks for any advice you can give me.
- Corey
View 2 Replies
View Related
Jul 8, 2005
More on the lot materials management program.
I have a series of related table:
Builder
Subdivision
Lot Number
Lot Option
Lot Materials Details
The [Lot Materials Details] table is populated by appending data from the [House Type Details] table tree as follows:
Builder
House Type
Elevation
Options Per Elevation
House Type Materials Details
The filtered Option Table in both branches determine which [House Type Materials Details] records get appended to the [Lot Materials Details] table.
The question is:
Is there any advantage, other than simpler queries later on, to adding the primary key from the [House Type Materials Details] table to the [Lot Materials Details] table.
It’s redundant because the [Lot Options Table] contains the [Options Per Elevation] primary key.
Any thoughts?
View 2 Replies
View Related
May 21, 2007
Dear All,
I have a table with several fields.
Concentrating only on few fields I'd like to sort out redundant information (unnecessary records).
(It is similar to using Ecxel: Data>Filter>Advanced filter>Unique records only)
So, I'd like to ask for your help how can I reduce my table to unique records.
thx
Csaba
View 5 Replies
View Related
Jun 13, 2007
Hi All
Can anyone help me with this problem?
I have an 11 million row table that lists relationships between pairs of people. There are several columns, but the relevant ones for my problem are
personA, personB, a-b_strength, b-a_strength.
because of the size of the table i'm having all sorts of problems, and i could actually cut out half of the rows, because for every row listing eg
dave, steve, 4, 5
there's another row listing the same information, but flipped round, ie
steve, dave, 5, 4
I'm sure there must be an efficient way to delete, using a query or otherwise, one of each of these pairs (it doesn't matter which). I have another column (id) that simply has a unique number for every row, ie 1,2,3->11 million, so i was thinking of something like
delete
from table t1
where exists
(select *
from table t2
where t1.personA = t2.personB
and t1.personB = t2.personA
and t1.id < t2.id)
So, this should delete every row that has a mirror version earlier in the list.(i think)
What i'm worried about though, is performance: is this going to make a new temporary table for the nested select for each new row in the outer statement? I already created a table that listed the count of friends for each unique person with a make table query, and that took 12 hours to complete. I don't mind another 12 hour query, but not a 144 hour query!
Any advice welcome - thanks! (Sorry for the longwinded post)
View 4 Replies
View Related
May 13, 2013
I'm doing up a database of hazardous chemicals, based on publicly available information from various agencies. Each chemical has a name and a number, and has different hazards associated to it. The hazards are grouped into classes, and for each class the hazard is further broken down into categories. Hence for an explosive hazard (the class) I could have categories 1, 2 and 3 based on severity. For each iteration of the hazard class and category (for example explosive hazard category 1), there is a non-unique hazard statement. Another complication is that there is a qualitative element in the classification system, and hence agencies from different countries could classify the same chemical differently. This should be captured into the database as well. Thus for every chemical, I will have to enter the Japanese classification, the EU classification, etc.
I've managed to set up what I think is a correct relationship, based on my understanding of databases. However when I generate the report, fields such as the chemical name and ID are repeated for each hazard. I would like to merge these redundant entries, so the chemical name, I.D., and any other repeated fields appear only once. In essence, I'm trying to make it such that there is only one entry for each chemical+I.D/country, with multiple hazard subentries, rather than having multiple, seemingly duplicate entries.
View 3 Replies
View Related
Dec 7, 2007
Morning all,
I'm having a problem with mdb file size. I'm importing a large amount of data from a number of tab delimited text files via a simple transfertext function. The process goes: empty the tables in the database, then import the data into the tables.
All this works fine, but the file size rockets to over 1.5Gb. When I then compact and repair, it goes down to 420Mb. I'm not deleting and recreating the tables, and at no point is there 1.5Gb worth of real data, so what's causing this?
N.B. I realise I can call compact and repair following the import, but this is going to take too long as they are user-initiated imports.
View 4 Replies
View Related
May 4, 2005
Hi,
we use access for localisation in our application. The business object which accessess the access uses DAO connectivity and we often land to
"Too many client tasks" problem.
is it wise to change the DAO to ADO? if so the above problem will go off?
what are the chances that the problem still persists?
is that access has any limits on the number of connections? like 64, 255? or is that ADO/DAO has any limitaions on the number of concurrent connections to access DB?
Pls help, your help will be highly appreciated.
Thanks,
Venkat.
View 1 Replies
View Related
Jan 2, 2007
Hello,
I am using the access program to create a mailer. The fields are really basic... Name and address....
Many of the fields are going to be identical. City, state, zip code for instance.
The way we had it set up on our first try was to create the data base with all of the fields empty and to input everything over and over. I imagine there has to be a way to set it up so you only have to enter "San Diego" or "CA" once and then simply do manual entry of the names and street address.
Any help getting me started would be well eppreciated.
Thanks
View 1 Replies
View Related
Sep 17, 2007
I am just starting to use Access and have set up many simple forms, datasheets, reports, summaries, etc. I have not worked with macros, etc. and need help for this project. I have set up a task table
F1 - DateOfCall - date - now()
F2 - Client - text
F3 - DateCompleted - date
F4 - DueDate - date
F5 - problemDescription - memo
F6 - Resolution - memo
I need to run a query and/or report every week summarizing what has been done the past week. Would also like to view previous weeks. I need help. How do I go about doing this.
View 1 Replies
View Related
Mar 31, 2014
I have an Access 2010 application running on Win 7 Pro which connects to a Back End SQL Server database. I am trying to get Access to synchronise the client pc system date & time with the Sql server date and time. Please note, the date/time does not necessarily have to be correct, it just has to be consistent between the server and all the pcs running the Access application.
I have tried two methods as follows. But both only work if Access with Administrator privileges, which isn't really practical.
Method 1:
Code:
Dim MyDateTime As Date
'Some Code here to get MyDateTime
If MsgBox("Change date & time to:" & vbCrLf & vbCrLf & MyDateTime & "?", vbYesNo, "Change") = vbYes Then
Date = DateValue(MyDateTime)
Time = TimeValue(MyDateTime)
End If
Method 2:
Code:
Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
[Code] .....
View 2 Replies
View Related
Oct 15, 2007
I work with an Access database with about 20 tables that requires considerable manual data entry. Although it is time consuming, the bigger problem is the keypunching errors that inevitably occur. A colleague of mine said that some databases can be set up so that two (or more) individuals can enter the same data into the database (basically creating two separate, temporary databases). At the conclusion of this double data entry, the two databases can be compared to see where they differ so that corrections can be made...the logic being that it's extremely unlikely two people entering the same data independently will make the same keypunching error in the same field.
Just wondering if Access has this capability, or if a reasonable facsimile of this approach exists.
Thanks!
View 2 Replies
View Related
Feb 20, 2007
The timetracker that I am building stores the history of the task in multiple rows. Here's a simplified view.
Task ID | Assignor ID | Assignee ID | Description | Status
1 | 5 | 10 | I could not make this change. Please try at your end | assigned
1 | 10 | 2 | I made this change. Please check it. | resolved
1 | 2 | 10 | Please make a few modifications. | assigned
1 | 10 | 2 | Done. | resolved
1 | 10 | 10 | Cool. You're in for a raise! | closed
If i want to select task histories of tasks which are not closed, i.e. the status in the last record is not set to closed, how would i go about doing it? As of now I am doing this through code, where I cycle through the recordset and skip displaying the rows if the task has been closed. But I'm sure there's an easier way of doing this.
Any help will be appreciated.
View 4 Replies
View Related
May 10, 2006
Hi,
I am still confuse after reading threads on producing running sum on queries. help!
This query is to produce a time line graph of Progress Tasks with 3 main categories - New, Completed and Outstanding. So I got months on the x-axis and count on the Y-axis.
sample data.
say we look back for jobs in the month in feburary
Date Added; Date Completed ; category
11/1 ; NIL ; Outstanding
11/1 ; 1/3 ; Outstanding
11/1 ; 14/2 ; Completed
1/2 ; NIL ; Outstanding
1/2 ; 1/3 ; New and Outstanding
1/2 ; 14/2 ; New and Completed
so the total count of the categories for Feburary
New = 2 ,Completed= 2, Outstanding=4
can anyone help?
thanks
galantis
View 1 Replies
View Related
Aug 5, 2015
I'm creating a fairly basic case management system in Access 2007 and i'm wanting a way of populating a set of tasks when a case is created...
e.g.
I have a case table, which contains basic information about the case, case manager, ID, date created etc.
I've also got a task table with a list of tasks
When i create a new case I want it to pull in the list of tasks from the task table, the tasks are static so i wouldnt want them to update the task table but update progress on the tasks against the case.
Do i need another table for like casetasks where it populates the blank tasks and then they're updated with an ID linked back to the Case.
View 3 Replies
View Related
Jun 14, 2014
I would like to make an action list based on tasks. I try to make a query that does the following things:
-Taken.Priority=1
-show maxium 5 tasks sorted on make date(taken.ingevoerd)
-OR with a total duration of maxium 480 minutes (Taken.[Geschatte duur (min))
The last one is to hard for me to make. For example if the duration of 4 tasks is 420 minutes, the fifth task (100 min) should not be shown, because the criteria of maximum amout of minutes is reached.This is what I have made so far:
SELECT DISTINCT TOP 5 Taken.ID, Taken.Omschrijving, Taken.Ingevoerd, Taken.Deadline, Taken.Priority, Taken.[Geschatte duur (min)]
FROM Taken
GROUP BY Taken.ID, Taken.Omschrijving, Taken.Ingevoerd, Taken.Deadline, Taken.Priority, Taken.[Geschatte duur (min)]
HAVING (((Taken.Priority)=1))
ORDER BY Taken.Ingevoerd DESC;
View 2 Replies
View Related
Jan 16, 2015
I'm making a daily renting cars system. The client should pay every day ( day by day). Everything is ok but if the client pay at these days....
dd/mm/yyyy
01/01/2015
02/01/2015
03/01/2015
10/01/2015
11/01/2015
12/01/2015
At the date 15/01/2015 i want to check the payments. How to let the system show me that the dates
04-05-06-07-08-09-13-14-15/01/2015
the client didn't pay the fees
Opening the payment form and make a new record everyday even if the client didn't pay and finally by a query I'm getting a list where the payment date field Is Null, but is there any other solution without adding the dates manually.
View 3 Replies
View Related
