General :: Automated Order Status Field
Feb 25, 2014
Basically working on a Customer Order Database for my carpet shop.
I have a Customer, Job and Order table. The order table contains the line items for each job and each customer can have many jobs.
I have a field in the Order table showing the item order status being either "Instock", "To be Ordered" or "Ordered"
I also have a Job Status field in the Job Table
I would like that Field to be automated depending on the item order status for each item on the job.
At the moment the Job status is a manually selected drop down box of either; 'Ready To Fit', 'Awaiting Stock', 'Items To Order' or 'Job Complete'
For example if all of the items had a item order status of 'Instock' then the Job Status would display as 'Ready To Fit'
If any of the items were 'To Be Ordered' it would display as 'Awaiting Stock'
I dont know how i would solve the situation of some marked as say 'In Stock' some 'Ordered' and some 'To Be Ordered' ????
Also the 'Job Complete' would come from another field in the Job Table marked with a check box...
View Replies
ADVERTISEMENT
Jan 8, 2014
I have a subform with two fields [status] and [process], i am trying to get the backcolor of procces to change to red if the status field contains the text "expired".
I have tried vba. plced on the after update
if me.status = "expired" then
me.process.backcolor = vbred
else me.process backcolor = vbwhite
end if
this
Changes the color of the field [process] but also all process field colors to red even if the status field does not contain "expired".
I think conditional formatting is the way to go but i think i need an expression to complete this.
View 7 Replies
View Related
Mar 7, 2014
I am in the process of putting together a database for use in a hotel to log details of customers, enquiries, and sales.
I have one table for storing customer contact details in ("Customers"), another table for storing enquiry details in ("Enquiries") and a third table for storing sales data in ("Sales").
Each record in Sales is linked to a customer from the Customer table, so with a simple query you can see what any given customer has spent their money on.
My boss would like to have a list of who her "top 10" customers are, over a given period, so she can work out who spends the most. I was wondering if there is a simple way to do this?
The only way to do this that I can think of is to run a query for every customer (to isolate their sales data), use the total function to add their sales together, note down the total, and input into excel (to work out the ranking). However, seeing as we have been collecting data for around a month now and have around 400 customers in the database this would clearly take a very long time. Is it possible to automate this process?
View 3 Replies
View Related
Aug 28, 2013
I have after much stress gotten my comments history field to appear on a form.
The following code gives me what I needed
Code:
=ColumnHistory([RecordSource],"LastUpdateBy","[ID]=" & Nz([ID],0))
I have only one simple thing left, that is perplexing to say the least.
Some comment fields will be many short notes, spread over time.
My desire is to change the sort order of the results so the most recent (rather than the initial) comment is at the top.
View 5 Replies
View Related
Sep 30, 2013
I want to automate a task in access....
I have a query that may have 6 - 7 different records for example each record contains a different email address, i need to get the first email then send and email to that address then go to the next record get that email address send an email to that email address until its gone through all the records...
I use this code to send an email, dont know how to loop through records and get the email...
Code:
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim strPath As String
Dim strFileName As String
Set appOutLook = CreateObject("Outlook.Application")
[Code] .....
View 1 Replies
View Related
Jun 22, 2005
Hello, I have been learning how to use Ms Access in order to design my own Database, I have done quiet a bit but i found myself kind of stuck witth the following issue. My question is pretty simple (I think…) Please, can someone help me? :o
I need a field that update itself automatically for each record.
For example, if I have 2 initial fields called [Stock balance] and [Quantity delivered]; I need to find a way to have the [Stock balance] updating itself just by entering a value for [Quantity delivered] where [Stock balance (actual record)] = [Stock balance (previous record)] – [Quantity delivered]
I could create a third field Called [Quantity left]
with [Quantity left (actual record)] = [Stock balance (previous record)]
and [Stock balance] = [Quantity left]-[Quantity delivered]
but it doesn’t seem right to create a third field that is equal to an other field just with one record difference.
Anyone with an advice?
View 4 Replies
View Related
Apr 17, 2005
Is it possible to get Access to automatically change the colour in a form field via code etc. My problem is that i would like to easily pick/see any out of date quotations for my customers, so when the quotation date is 30 days old the text changes to red. Any advice would be gratefully received.
View 1 Replies
View Related
Aug 15, 2014
I am using Access 2010. I Have the 'Display Status Bar' box ticked in Application Options but the Status Bar is not being displayed.
View 8 Replies
View Related
Mar 11, 2015
Is there a place within an Access database besides a table where you can store a flag (text or boolean or number) that persists after the database is closed and can be checked when the database is opened (using VBA)?
View 5 Replies
View Related
Jan 30, 2015
I'm creating an automated system on access, basically it uploads client's files and analyses their data. The files will always be different, with the amount of fields changing and with different field names each time
One part of it, is appending new contacts to their data. This means records which we can add new contacts to, needs to be duplicated with the new contact placed at the end. So it needs to be like
Company Name New Contact Name
A
B
B Tom
B Harry
Because it's automated with different field names each time, the duplicating part is an issue. I can use the * rule which appends all fields, however this will not work in this case, if we are adding more than 1 new contact, the new contact will be duplicated rather than having 2 new different contacts.
Ideally I want rule saying, append all fields EXCEPT the fields where the new contacts are placed, but I don't think this is possible
I'm using Access 07 for this. Using a mix of VBA and SQL in the modules
View 2 Replies
View Related
May 24, 2006
I have a form that resembles a spreadsheet and have data in certain fields that is longer than the field is wide. (The widths are set according to client preference and widening the field is not the solution.) I was thinking perhaps I could use the status bar to show the data when the focus is on that particular field. Right now, the only help on the StatusBarText topic that I can find indicates that the status bar can be "programmed" with static text only, but I was hoping to make it dynamic, depending on the field in focus.
If this is the wrong approach (i.e. can't be done), does anyone have any ideas about how to use an external control to show (and possibly edit) data on a spreadsheet-like form?
The client has been using MS Works and is being dragged into Access only if the transition isn't too painful (for him, lol) and having an ability to look and feel like the Works interface is important to him.
Thanks,
- Terri J.
View 3 Replies
View Related
Jul 11, 2013
I have a working report which groups by a status field. Let's say I have Status (AA, BB, CC, DD, EE). It is grouping and summarizing fine. I would like to be able to merge some groups. So I would like to be able to Group on (AA/CC, BB, DD/EE).
What would be the best way to approach this?
View 4 Replies
View Related
Apr 26, 2013
When editing a record in Access 2003, the status bar would show the value of each field's description from the table design grid. Is there a way to get Access 2010 to do the same?
View 1 Replies
View Related
Jun 22, 2014
I have several buttons on a form and I want to set enabled = false on one of them when I open the form - that's fine
I have two txt fields - ReceiptNo and BankPaymtId if the both have a value I want to make the button enabled = True
This is the code I have used on the BankPaymentId On Dirty event
Private Sub BankPaymentId_Dirty(Cancel As Integer)
If Me.[ReceiptNo] <> "" Then
If Me.[BankPaymtId] <> "" Then
Me.CreateOutlay.Enabled = True
End If
End If
End Sub
This is the one for ReceiptNum on Dirty event
Private Sub ReceiptNum_Dirty(Cancel As Integer)
If Me.[ReceiptNo] <> "" Then
If Me.[BankPaymtId] <> "" Then
Me.CreateOutlay.Enabled = True
End If
End If
End Sub
Nothing seems to be happening.
Even the Form on Dirty is not working now....
View 4 Replies
View Related
Jun 18, 2013
I have a query that gets it's data from tblContacts. In this table is a yes/no field for archiving and the query gets only those records with a No in the field. The default is no. Next I have a form based on this query, which I am using to hold a set of command buttons which act on a contact chosen from an unbound list. So far so good but now I would like to add a command that changes the contact's status in the archived field of the table to Yes, so that I can use the form to view/edit, or email, or archive.
View 1 Replies
View Related
Mar 8, 2013
My database has a text field "Status" where the text is either, A, W, C. There is a form to update this field; currently it uses a standard Access created text field. The users want a checkbox which will show up as checked when the status is W, if the status is anything else, the checkbox will be blank. If the user clicks the checkbox within the form the status will be changed to W.
If [table.status] = 'W'
then checkbox = 0
else
checkbox = -1
and then somewhere on the update it would be
if checkbox = -1 then [table.status] = R
Except that Access doesn't think the way I do.
View 1 Replies
View Related
Aug 2, 2013
I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.
I want to prepare a report from the projects table that lists projects IF they have any open tasks.
In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'
View 7 Replies
View Related
May 6, 2013
I am using access 2010. I technically have an unbound form but I am changing the record source by command buttons. Switching between 3 command buttons. My problem is that I have a subform that I was linking to the form to an unbound search field. Now I want to switch the master and child links to a field on the form when I switch the record source which happen to be the same field as the unbound search field; however; it still pulls from the unbound search field rather than the field on the form. Do I need to delete the search field in order to get is to point to the appropriate field?
View 1 Replies
View Related
Mar 25, 2013
I need to design a query in which order shortage is calculated with respect to fifo method for orders .
Attached is the logic
How to create it. and if i need to incorporate more than 10 orders , what is the best method for this ?
View 3 Replies
View Related
Aug 13, 2015
i am going to use the autonumber as an order number but I want " SC2015 in front of it so i end up with " sc20151 , sc20152 but next year i want it to change the year SC201650 .how to put the sc2015 in my report without any input from the user
View 2 Replies
View Related
Jul 11, 2013
I have a table with about 290 record. When I'm in the "Form View" and click the "Next Record" arrow it scrolls by order of Employee instead of the primary key "Record Number".
View 11 Replies
View Related
Oct 17, 2012
Because of certain design considerations and the amount of data I'm maintaining, I have chosen to use tabbed documents rather than floating windows or subforms.
The hierarchy is supposed to be a person who is categorized as either a client, family member, donor, etc. Further, a client may use one or more services. In the example, our client uses Specialized Home Care. However, the tabs are intuitively out of order: the people tab is left-most, followed by the Specilaized Home Care tab, and lastly the Client tab. It should be People, People:Client, People:Client:Service. If a person uses a lot of our services, there will be separate People:Client:Service tabs and the People:Client tab will scroll off to the right. I'd like Access to order them properly but see no way to do it.when the forms are opened, it's people first followed by the high-level classification (i.e. Client), and the lastly the specific Client:Service tabs. I would expect Access to open and display them left to right, but, as you can see, it doesn't.
View 7 Replies
View Related
Nov 10, 2014
I have a table with entries describing a sequence of Tasks (tblTask)
tskID TaskDescription
10 Clean Room
12 Wash Dishes
etc
I have second table called Steps (tblSteps) that is related to tblTask via the field "StepNumber" which is a concatenation of [tskID] & "." & [StepOrder]
So if we look at washing dishes as a task then the steps are as follows:
StepID, StepDescription, StepOrder, StepNumber
202 Put plug in the sink , 1, 12.1
512 Turn on tap, 2, 12.2
205 Put in detergent, 3, 12.3
210 Place dishes in sink, 4, 12.4
435 Turn off tap, 5, 12.5
etc
If I decide delete the third step I get the following:-
StepID, StepDescription, StepOrder, StepNumber
202 Put plug in the sink, 1, 12.1
512 Turn on tap, 2, 12.2
210 Place dishes in sink, 4, 12.4
435 Turn off tap, 5, 12.5
Is there a simple way renumbering the StepOrder entries when one step is totally removed to give me the following?
StepID, StepDescription, StepOrder, StepNumber
202 Put plug in the sink, 1, 12.1
512 Turn on tap, 2, 12.2
210 Place dishes in sink, 3, 12.3
435 Turn off tap, 4, 12.4
View 6 Replies
View Related
Nov 22, 2012
I am new to Access. I am creating a simple order/payment database.
I have tables for the orders and payments and have a relationship setup.
I created an order form with a subform for the payments.
[URL] .....
View 2 Replies
View Related
Jul 17, 2015
how to get some information to show up in chronological order.
For example,
It is showing up like this..
Week 1
Week 10
Week 2
Week 20
Week 3
Week 4
and so on
I want it like week 1
week 2
week 3
week 4
week 10
week 20
View 8 Replies
View Related
Dec 6, 2013
I am currently studying Robert Jennings book entitled Using Microsoft Access 2007 Special Edition. In reading about specifying of the sort order and top value limits established by inner joins (pg 455), the author makes this statement.Access displays query result sets in the order of the index on the primary key field of the table that represents the one side of the top most one-to-many relationship of query tables.An example of a query with Orders, Order Details, and Products tables displays rows in productID (the primary key field in the Products table) sequence. He says that it does so because the Products table has a one-to-many relationship with the Order Details table and indirectly with the Orders table. I do not understand why the Products one-to-many relationship with the Order Details table is ranked as the top most relationship in that the Orders table also has a one-to-many relationship (OrderID is the primary key field) with the Order Details table and an indirect relationship with the Products table. The Order Details table has two primary keys (ProductID and OrderID). My attempts to change the sequence in which the table relationships were formed as well as the location of the tables in the database had no effect on the result.
View 2 Replies
View Related
