Date Dependant Data
Jun 18, 2007
What's the correct syntax to search for in the forums, for this question.
Have a customer database, where the customers records are split across two tables. The second table holds the customers address; when this changes I need to record the date so that in the future, any invoices etc always have the correct address on them for that particular date.
I have a cross tab query which works quite happily and when you create the record it adds a date/time stamp. However what I cannot seem to figure out is how to save any ammendments to the address etc as a new record, whilst not adding a new record to the non "dative information" such as Name, DOB etc. The PK for table2 is made up of the Customer Number And Date/Time Stamp. At the moment any changes I make to the existing record in table2 simply over writes the edited fields..
Can I do this through the QBE, or does this have to done using recordsets?
Anyone help please?
View Replies
ADVERTISEMENT
Mar 21, 2015
Is it possible to have a drop down list dependant on the date selected? The form has date, then period time. Would it be possible for the period time drop down list to change if the date is selected on a saturday?
View 8 Replies
View Related
Apr 14, 2006
How Do You Do Dependant Fields?
Example being:
I have a list of Stores of which there are 4 Formats (Super, Extra, Metro, Express). Each of these formats have their own specific grades.
What i want to do is when entering a new store via a form, In the Format box i would choose one of the formats from a combo box then when i progress to the Grade Entry, i would only want to see the Grades for that particular Store Format.
Would i need to have different lookup tables for each of the format grades & how do i achieve the above?
Ive seen this done on Airline web sites, ie select outgoing airport then the destinations change to only those that can be reached by flights from the Outgoing airport.
View 3 Replies
View Related
Sep 12, 2006
Quick question for you.
I know how to set a required field, but how do I edit two field so that they are dependant on each other? IE. How do I set my form to make FIELD1 required ONLY if FIELD2 is empty and vice versa?
Thanks very much and I hope I've explained myself correctly.
View 1 Replies
View Related
Mar 18, 2005
Hi Everyone,
I have a question about lookup fields. I have the tables below. The main table is [vid] and i reference other tables like [manufacturer], [model].. etc. to fill in [vid]. I have pull down lookups for all of [vid] but it is showing all models, makes, etc. I need helping making the lookup row dependant. For example when I am in [vid] and click on the [model] combo box for vid=1, I only want to see the models for that manufacturer(Ford), so it will only show mustang, escape, and navigator; not all of the models. For vid=2, when I click the pull down for model I only want to see Gm Models, corvette and cavalier. So this is what i mean be row dependant. Is this possible?
Thanks so much in advanced,
Michael
[vid]
vid,year,mft, make, model, submodel, ...
1,2005,ford,ford,Mustang,GT
2,2005,GM,Chevrolet,Cavalier, LS
3,2005,dcx,Jeep,Wrangler,base
...
30,2005,ford,Lincoln,Navigator, base
[manufacturer]
mid,mftName
1,ford
2,gm
3,dcx
[Model]
modelID,modelname,modelmft
1, Mustang,Ford
2, Escape, Ford
3, Corvette, GM
4, Cavalier, GM
5, Navigator, Ford
6, Wrangler, DCX
View 2 Replies
View Related
Feb 26, 2005
Hi, im kind of new to access and im not sure whether this is possible or not, but i have a problem which needs to be solved by 4 list boxes on the same form.
The first list box will have 4 choices in it and depending on which one is chosen the second list box will be updated. This continues on to the 4th list box, which when a choice has been made a new form will open with the relevant information from the tables.
I hope this is enough information to enable someone to aid me with my problem, thankyou very much for your time.
Craig.
View 1 Replies
View Related
Feb 7, 2006
Hi
I would like to know how can i reference a combo box to the value of a text box on the form it is for a purchasing system.
if i select product one i only want the the order quantity for that item to show and the same with the price field as all the reorder levels and cost information is stored in a table. i have created queries only selecting the product code and reorder quantity and the product code and the cost price.
hope someone will be able to help
regards
melanie
View 14 Replies
View Related
Jun 2, 2006
i have a fault logging system for a school. i am trying to change a background Colour of a field to red when another field contains certain values.
any ideas much appriciated :)
View 3 Replies
View Related
Apr 22, 2014
I have a control called Pnummer (its personell number).After a user enters this number i want my combobox called Kenteken (Licenseplate number) to fill with only the licence plate numbers of the employee from the table Parkeerbeheer (Parking management).Should be simple enough, its like cascading comboboxes but then with only one combobox.So i put this code in the afterupdate event of the Pnummer control :
Code:
On Error Resume Next
kzlKenteken.RowSource = "Select parkeerbeheer.kenteken " & _
"FROM parkeerbeheer " & _
"WHERE parkeerbeheer.pnummer = '" & fldPnummer.Value & "' " & _
"ORDER BY parkeerbeheer.kenteken;"
I have left the rowsource blanc as above code handles that.
The only thing that comes to mind why it doesn't work is that the table bound to this form is NOT the source where i pull the licenceplate numbers from.
View 5 Replies
View Related
Apr 26, 2005
I have a list box containing various items. I would like to have another field return a numeric value depending on what is selected in the list box. For example, if Closed is selected from the list box, the other field would return a 1. How can I do this. The new field also needs to be linked to a table so the values are saved in the table. Thanks for the help.
View 1 Replies
View Related
Sep 15, 2005
Is it possible to hide text boxes/controls on forms dependant on the user ? I have a had a (quick) look at access security and it appears that it just enables read/write access to objects not controls on the objects.
Many Thanks in anticipation
Dave Smith
View 1 Replies
View Related
Jan 17, 2006
I would like to somehow put the link to the picture in a field in a table, and then have the picture change depending on the record I am viewing (which is altered by the combo box pertaining to another field) ok thanks!
View 14 Replies
View Related
Jun 8, 2006
Hi
Any ideas on how to autofill the rest of a form when i select from a combo box?
ie when i choose a name from the list i want the rest of the form to populate with the rest of the data relevant to the selection.
Cheers
View 2 Replies
View Related
Jun 28, 2006
Hello,
First please accept my apologies if this has been done before. In the attached database I am going to have a form which is continous what I am looking to do is change the colour of the box which is in the background to the associated colour from the quote table for example
if they choose water then it will look at the TBLQuoteType and change the box colour to the colour which has been assigned to that value.
Hope this explains it ok I will continue to try and figure it out but I thought I could also use your expert knowledge.
14030
Thanks again
View 5 Replies
View Related
Sep 2, 2007
I am sorry to have to ask. I have been researching for the last two weeks and still can't find the solution.
Could you help me. I have a web page in asp, which has a "submit" button. When this is pressed I want to access a MS Access database called "Passliabcapture"
within this single database - I want to examine each record in one table (results), and then dependant on the results of the examination (ie if the value of a status field - "D"), add a new record to a second table (transaction file), and then continue looping through this function until the end of the first table.
Sorry if it seems simple - but I think I am - simple that is...
The code I have tried is as follows. All I get is an error saying
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/jon.asp, line 10
INSERT INTO "Transaction File" (Policy_No, Insured Name, fin_totalmnthpremium, ActionDate, Daterun, Description)
<%
[If Request.ServerVariables("REQUEST_METHOD") = "SUBMIT" Then
Set objCon = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objconn.Open Application("passliabcapture_ConnectionString")
obj_rs.Open "Results"",Transaction File", objCon, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
INSERT INTO "Transaction File" (Policy_No, Insured Name, fin_totalmnthpremium, ActionDate, Daterun, Description)
SELECT Policy_No, Insured Name, Transaction Amount, ActionDate, Daterun, Description, FROM "Results" WHERE Status = "D"
objconn.AddNew
Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
Set objCon = Nothing
%>
<html><head><title>Debit Run Page</title></head><body style="background-image: url('_themes/expeditn/exptextb.jpg')">
<input name="Submit1" type="submit" value="run"> </body></html>]
View 2 Replies
View Related
Aug 7, 2005
I have a database i am playing around with in Access 2003
I should state im new to Access
Anyway i have a field with a dropdown menu with the choice of Yes or No
I want another field to be populated from an entry in the table Extras called ElectricityCharge but only when the Yes is chosen above
Had a look around and cant see any sort of If then type function or anything of that ilk, how can i do this ?
View 4 Replies
View Related
Apr 20, 2007
I have used and maintained Access databases in the past but this is my first experience with building a new database. I started by borrowing every Access book my Tech department has on hand and going through them. I have gotten to the point where my tech department can not help me because my question is beyond their own knowledge. I have searched through many different website’s forums and have failed to find what I am looking for. It very well may be out there and I am using the wrong terminology in my searches. The most important piece of information to be stored in my entire database is of course the one that I am having the hardest time figuring out how to set up.
I have a table called “Document Index” which will be a master log of every document that passes through my department. This table has the three fields which are important to my problem/question.
1. The “LOC” field represents the location at which the document was logged. The “LOC” field has a working validation rule that only allows a “D” or “S”, representing the department head’s office or the site office.
2. The “DUO” field value represents the three digit unique number assigned to a project by another department (accounting)
3. The “ID Number” field is a unique number we will assign to each document that is this table’s primary key. It is also where my problem sits. The “ID Number” format has been decided upon by my boss and the rest of the office has already begun stamping these ID Numbers onto every document (which I will have to go through and enter once the database is complete). I am stuck with the format as it is shown in the example below.
“ID Number” format is: value of “LOC”- value of “DUO”-number
I know what I want the field to do but I have no idea how to turn my ideas into the proper code to see if it works. So here is my idea of a solution with example values entered.
A.Fill in form for “LOC” and “DUO”
B.At this point Access would run a behind the scenes search and return all ID #’s that had a that “LOC and “DUO”
C.At this point Access would run a behind the scenes search through only those records found in step B to find the highest “ID Number” previously assigned
D.At this point Access would make a calculation: adding one (+1) to the “ID Number” found in step C
E.At this point Access would insert the result found in step D into the “ID Number” field of my current entry (which until this step the record only contained the “LOC” and “DUO”)
For example: if my first 4 entries had this info:
LOCDUOID NumberDocument Description
D410D-410-1 Wendy’s Ransom Note
S410S-410-1 Priate's Demands
D415D-415-1 Cinderella’s Birthday Invitation
D410D-410-2 Peter Pan’s Response to Ransom Note
Then I complete step A by typing into the form:
LOCDUOID NumberDocument Description
D410D-410-1 Wendy’s Ransom Note
S410S-410-1 Priate's Demands
D415D-415-1 Cinderella’s Birthday Invitation
D410D-410-2 Peter's Response to Ransom Note
D410
Access would execute step B finding:
LOCDUOID NumberDocument Description
D410D-410-1 Wendy’s Ransom Note
D410D-410-2 Peter's Response to Ransom Note
Access would execute step C finding:
LOCDUOID NumberDocument Description
D410D-410-2 Peter's Response to Ransom Note
Access would execute step D finding:
D-410-2 + 1 = D-410-3
Access would execute step E:
LOCDUOID NumberDocument Description
D410D-410-1 Wendy’s Ransom Note
S410S-410-1 Priate's Demands
D415D-415-1 Cinderella’s Birthday Invitation
D410D-410-2 Peter's Response to Ransom Note
D410D-410-3
Then I would finish entering the information for that record.
I would appreciate any help you can offer, even if just pointing me in the right direction or correct terminology for my searches.
View 5 Replies
View Related
Sep 18, 2006
I am very new to Access and all that it entails but I have really learned a lot viewing everyones posts. I cannot seem to get my current problem solved.
I am trying to have a combo box appear depending on a preivous combo box selection.
1st combo box is "ApplianceCombo"
2nd combo box is "SpeedCombo" Set to Visible = No
1st Combo Box has
Dishwasher
Vacuum
Washing Machine etc
If they choose Washing Machine I would like my 2nd combo box to appear which they can then choose
1000
1100
1200
1300
Hope I have explained this well enough.
Thank you in advance for any help
Kim
View 2 Replies
View Related
May 16, 2005
Hi,
How can I get access to hide certain fields or controls on a form if a certain field has no data?
Allow me to explain further:
I have a form that displays data = frmPlantMain
and a associated image = Image1
The image is stored outside of the db as a jpeg and the field in the record source table (Image1) contains a link to the image. For example: C:databasePicture1.jpg
There is also a hidden text box that contains the message "No Image available" = lbl_NoPix
This would be displayed in place of the image if no image has been inserted.
I also have a seperate "insert new image" command button = cmdInsertPic
If the current record has a image then I want to have the controls Image1,lbl_NoPix & cmdInsertPic hidden. This seems fairly easy but it gets more complicated now.
If there is no image I would like these controls visable so that the user can add a new image. I would then need the record to be requiried or refreshed to reflect the fact that a new image has been added.
This is where I fall down - I do not know how to tell access to look at the field in the table (Image1) and if there is no linked or associated image then set the visable propertys of controls a,b & c on the form to true.
I hope I have explained my problem correctly and I thank you for your time in advance.
Regards,
Kenny
View 5 Replies
View Related
Jun 21, 2015
I'm creating a database which contains information for keeping dog grooming clients. It has 3 tables, table 1 is customer, table 2 is dog, and table 3 is booking.I want to be able to choose the customer table and within that you can see the pet linked to that customer and within the dog table you can see any bookings with that dog.What I have done is linked the pet and booking table to the customer details via mobile no. being the primary key.
I want a form called appointments which contains fields *ID, date, mobile, dog name, grooming stlye and other. What i want to happen is this, once the mobile number is entered, i then when it to lookup the mobile number in the dog table and pull the pet name from that, then in the dog name box it will then provide a drop down of dogs related to that mobile number.
I understand a query needs to be made in the dog name field, so in that field i created a query to look up the mobile no. in the appointment table, and to then lookup the pet name in the pets table. This kind of works. The two problems are this, the drop down in the dog name field shows all of the dogs in the database not just the ones assigned to the mobile no. in the field before. If i choose one of the dogs not related to that mobile it changes the mobile number in the field before to the relevant one (but i don't want to be able to see the dogs which arent related to the mobile no. in the field before.The other issue is that on one of the customers there are two dogs, now on the drop down you can see both dogs, but it will only allow you to select the dog that was entered first into the database. If i select the other dog it simply chooses the data for the one first entered.
I have read about doing Requeries on the afterupdate of the field and attempted this by choosing the dog name field and in the afterupdate telling it to requery pet name (I've also tried mobile), this hasn't worked. I have a feeling I'm getting a bit confused by which fields i'm supposed to use etc.
View 1 Replies
View Related
Mar 4, 2014
I have a report that looks up a previous date then finds the data that goes with that date. I went to run the report and the data now is blank. I am thinking it is because of the formula is looking for 1/28/2013 instead of 1/31/2013.
Code:
DLookUp("[Net Insured]","[CashFlow Link]","[RCNAME]=" & "'" & [RCNAME] & "'" & " And [Date1]= DateAdd('m',-1,DateSerial(Year(Date()),Month(Date())-0,0))")
View 7 Replies
View Related
Nov 15, 2004
I have created a database that is used to track various programs that our organisation runs, and keeps a record of which Division the program is in.
at the moment I have a main form which has a combo box that lists Divisions, then I have a subform that has a combo box on it that lists the purchasers.
what I would like to do, is to have the Purchasers list update when a Division is selected in that main form, as depending on which Division is chosen the contents of the Purchasers list changes drastically.
Is anyone able to offer some assistance?
View 1 Replies
View Related
Nov 1, 2004
I have written the following Function as part of an Access program:
Public Function TotalWl() As Long
Dim sqlWL As String
Dim rsWL As Recordset
Dim dbWL As Database
Dim sendlDate As Date
Set dbWL = CurrentDb
sqlWL = "SELECT compul FROM qryStatisticsWL WHERE sendDate between 01/09/2004 and 30/10/2004 " 'this is all written on one line because I do not know how to split it in more than 'one lines
Set rsWL = dbWL.OpenRecordset(sqlWL, dbOpenSnapshot)
If Not rsWL.EOF Then
rsWL.MoveLast
TotalWl = rsWL.RecordCount
End If
End Function
When I call this function the result is always 0. The program does not like :
WHERE sendDate between 01/09/2004 and 30/10/2004
I have tried: WHERE sendDate >= 01/09/2004 and sendDate <= 30/10/2004
also: WHERE sendDate >= 01/09/2004 and <= 30/10/2004
I also used single quotes, like '01/09/2004 '
But nothing solved the problem. By the way, thee are data between these two dates.
Any suggestions how to proceed?
View 2 Replies
View Related
Jul 1, 2005
Im looking for something that filters information for 6 months prior to any current, non-specific date. Like it will automatically get the data dated from 6 months ago from june, july, or august, without anyone having to go in an change the date criteria.
Below is a non working example of what I am trying to come up with. If I could get something like this, it would save me a ton of time going back and updating all the date criteria for my databases.
Forgot to post it:
>=Now()-Day(180)
View 1 Replies
View Related
Jul 28, 2005
I need to run a query for a particular time frame. The only problem is that the CurrentDate field in the table has its data type set to Text.
I thought I could do something like this but it didn't work:
WHERE cdate(myTable.CurrentDate) >= #01/01/2004#
Isn't there a way to "convert" this field so that it behaves as a date field when the query runs?
Thanks,
crhodus
View 1 Replies
View Related
Jul 25, 2014
I have a database with two tables: taskTable and volumeTable. On the task table I have the list of task performed in my team and the time it takes to perform the task related to a category, and on the volume tasks I have the volumes of workload based on the category moth after month. Then I have a query that multiplies the time it takes to perform a task with the volume in the category and returns me results for each month.
Now, if a task changes on march and the time to perform it its reduced or increase I change it on the table, howerver, ALL results for all months. I would like to know if there is a way for the change to be applied from the month it is changed and not retroactively.
Example Task Table:
Task Time to Perform Task Category
Buy ticket 5 min Business Visitor
Create profile 10 min Business Visitor
Example Volumes Table:
Month Year Category Volume
March 2014 Business Visitor 39
View 3 Replies
View Related
