I have a query based on a table that has a column called Calculated Card Expiration:
IIf([Card Type Issued]="PIV",[Issue Date]+1095,IIf([Card Type Issued]="Non-PIV",[Issue Date]+1095,IIf([Card Type Issued]="Flash",[Issue Date]+365,Null)))
It worked fine when I ran the query the first time, but now when a Card Type Issued and Issue Date is added or changed it doesn't work.
I have created a database and have a form (searchpartsfrm) with multiple combo boxes and a text box that builds up all the entries and creates a perfect SQL statement and stores this in strisql variable.
I have a report (showallpartsrpt) embedded on the page that is currently showing every record in the database.
Is there anyway to have this form "refresh" and show only the results of the query by clicking a "search" button?
I believe I need to use the filter command somehow but im not sure how to reference the embedded form. If I try with some test code it cannot locate showallpartsrpt.
well i am trying to make a very simple program that would help me learn the capitals of each country. yes it is a weird thing to do but i just it might help. anyway i started doing it using access and made a table with three columns primary key number, country and capital. I started making a form and im tryin to make it so the program would randomly pick a country and i would have to choose from a list box the capital and i would have a command button which when i click will tell me if i am right or not and if i am not then it would say the right capital. How should i do this? p.s ino the basics of using the code builder help much appreciated.
I have a form which is used for entering holiday requests.
It has fields such as name, type of holiday, date from and date to but it also has a list box value. This list box contains the number of days between the date from and date to and exludes weekends and bankholidays using data from a table. The data from the form is inserted into a table using an sql string when the submit button is pressed. The problem I have is that the sql does not pick up the number of days from the list box unless I click on the list box before I click on submit. It is almost as if the value is in the list box but it is simply not recognised by the sql unless I select the list box using the mouse. I have tried to select the list box using code when the submit button is pressed but to no avail. Does anyone have any ideas how I can over come this or perhaps how to physically select the list box so it is highlighted when the submit button is pressed??
I am trying to issue multiple INSERT statements in a row - but it seems that only the first succeeds. If I put a 1-second delay between attempts, suddenly they all succeed (so I know the statements themselves are all valid, it's not a data issue).
These are all being issued in a loop from the same thread, so as far as I can see unless the data is being inserted asynchronously, I can't see any problem. If it is being done asynchronously - how are we supposed to know one INSERT succeeded and thus we can issue the next one?
Anyone have any idea why this might happen? I don't want to leave 'magic delays' in the code!
Hello I'm helping a friend with an access project and the Referential Integrity check is failing for some reason unknown to me. I recreated the entire database from scratch and added all relationships before adding any data and then added in the data. The tables giving me problems are the following:
Orders Table: OrderID - Primary Key Autonumber, Format:1000, Indexed: Yes(No Dupes) CustID - Number, foreign key to a customers table, 1-many relationship SalesID - Number, foreign key to a sales table, 1-many relationship ShippingID - Number, foreign key to a shipping table, 1-many relationship 3 different date fields and a price field
This table is filled with data.
Order Details Table: ItemID - Primary Key Autonumber, Indexed: Yes(no dupes) OrderID - Number, Dec. Places: Auto, Required: Yes, Indexed: Yes(dupes OK) RingID - foreign key to rings table, 1-many relationship StoneID - foreign key to gemstones table, 1-many relationship Ring Size - number
There is no data in the order details table.
The Orders to Order Details is a 1 to Many relationship with ref integrity and cascading updates and deletes enforced.
When I try to manually type in a record I get the "You cannot add or change a record because a related record is required in table 'Orders'" even though I added a lookup list box listing all the OrderIDs from the Order table. I know I'm typing in a valid OrderID number, but it's not taking it and throwing this error.
I have inherited a buggy database thas has a simple piece of code that won't behave the way I think it should! The code should add a new record and have that new record appear on the list form (where the button On_Click event is called) making it available for user edditing. In 99% of cases though, the blank new record does not appear. The record is getting created, as i can see it in the table and have added a watch to the RecordCount property, it just doesn't display automatically. The existing workaround for users is to click a second button that invokes a simple one line function sub that performs only a Me.Requery. The user presses that up to two or three times before the new blank record finally appears.
One solution I read about, but hesitated in trying due to the unprofessional 'flash' that occurs, was to close the form and the re-open it...anyone experienced this or can comeup with a neat workaround?
The code
Private Sub CmdAddRule_Click()
Dim db As AO.Database, intnextseq As Integer Dim TestThis As String Set db = CurrentDb() Dim rstDao As DAO.Recordset Set rstDao = db.OpenRecordset("select * from stblAllocRules order by intRUSequence desc;", dbOpenDynaset) If rstDao.RecordCount > 0 Then intnextseq = rstDao!intRUSequence + 1 Else intnextseq = 1 End If With rstDao .AddNew !AutRU_id = 0 !intRUSequence = intnextseq !bytAppendBack = 1 !bytUpdateBack = 0 !bytDeleteBack = 0 .Update .Close End With
Set rstDao = Nothing Set db = Nothing
Me.Requery Me.Repaint 'I have used this staement in addition to and instead of the Me.Requery to no effect Me.Recordsource = Me.RecordSource 'I have used this statement also in addition to the above to and in isolation, to no effect. End Sub
I have created a FE/BE database which is operating on a network. There is one FE for each person (approx 150) all accessing 1 BE. There are at most, maybe 10 people using it at once. The database has been working well for about 2 months.
My problem is not with data corruption (touch-wood!), but with a complex form.
The Form is split into a Header/Footer, with a Detail section containing a subform. The main form filters records that appear on the subform. The subform is continous, with several conditional formatting cells per record.
Most of the time it works fine, but occasionally, the main form opens, but the subform fails to load/open. All I see is a white page with the main form header displaying correctly along the top 1/3rd of the screen. No error message is displayed. If I print the page using a Report (The subform and Report use the same query) the page prints perfectly with all records showing.
I have seen this problem on several different workstations, only to try again later to see it working (having closed the db, logging off then back on again)
I am at a loss with this one. Is it a problem with Access or the network? I am unable to find anything similar to this problem on the forum. Can anyone shed any light on it?
I have two date fields in a table. I need to find the days between these dates and, if its greater than 7 days, I want to display the record in a report, so far, I have found a hand function that allows me to enter two dates and it returns a long data value representing the number of days in between the two dates. (google "I've developed the following code to count the business days between 2 dates." and its the second one that starts as SOLVED I made no mods to it as it does what i need it to do.
So, i added this to its own module within the data base for use within a query. My test query basically displays the unique ID, the start date and the end date and then displays the values returned from the function. here is the SQL:
SELECT [Main Table].[Unique ID], [Main Table].[Start date], [Main Table].[End Date], (Business_Days_Between([End Date],[Start date])) AS [Days between] FROM [Main Table] WHERE ((([Main Table].[Start date]) Is Not Null) AND (([Main Table].[End Date]) Is Not Null));
When ran, this Query works... However, when I enter a criteria like =2 or > 7, it says data type mismatch. I have even attempted the CInt() function to make sure its formated as int but i continue to get the same error.
I am using an unbound object frame to display an .png image file which is set in vba.
The image is an excel chart saved as a .png
The problem I have however is that the image I save from Excel is great quality, and is pretty tack sharp, but when I display the same file in the unbound object frame in access, it is not nearly as sharp.
I am setting the picture property of the control as:
ubImageUserChart = "c: empmyChart.png"
It displays alright, but is just a bit fuzzy - still quite legible, but it is a complex graph with a lot going on - has regressions and formulas etc on the graph, and they need to be very clear.
BTW - the unbound frame is the same size as the Excel chart which gets saved as a .png file. If I tile the images (Access unbound frame and original file in picassa preview) side by side - they are identical - size, orientation etc.
It is not practical for me to try and do the chart natively in access as it is way to complex. I am using access vba to drive an excel session to do all the statistical yack work and chart rendering, then displaying a png image of the resulting chart in an unbound object frame in access.
I have a Report containing a sub-report which holds the pictures. Typically there are 3-4 pictures per A4 page and 2-3 pages, so not millions, but for some reason whenever I load I get between 1-3 pictures a page and the rest show as blank. I can repeatedly reload time after time and get a seemingly random number of successful images.
The original images are on a network drive (but connectivity is good). They are high quality images but the load time isn't that bad (Is there a 'normal' load time for these things?) I wonder if it's just timing out after the first X pictures..
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click() 'Creates quote date and prints quote Me.QuoteDate = Now() Me.cbAgentID.Requery DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
SELECT (Int([age]/10)*10) AS MYG, Count([MYG]) AS Frequency FROM Data WHERE ((([Data].[Date of referal]) Between #1/1/2006# And #1/1/2007#) And (([Data].[Age])>10)) GROUP BY (Int([age]/10)*10);
the [MYG] field is basically an integer 10,20,30,40,50 etc I wish to change this field to 10 - 20, 20 - 30, 30 - 40 etc.
Hi all, I have been trying to make 2 tables join (the end goal is to overwrite some data in one table, with data in another). The table layouts are as follows
All data types are matching, so that shouldnt be the problem. I want to grab the value fware from the firmware table and put it into the dbo_device 'dev_memory' field, where the dev_name & name are the same. Here is my query...
UPDATE dbo_device SET dev_memory = f.fware FROM firmware f INNER JOIN dbo_device d ON d.dev_name = f.name
------------------/SQL--------------------------------- I am getting the error 'Syntax error (missing operator) in query expression 'f.fware FROM firmware f INNER join dbo_device d ON d.dev_name = f.name'
I have 2 fields in a form called 'Employee No's' which use a combo box (this information for these comes from a query). However, I have another form called 'Employee Details' (this form comes from the same table as the query is taken from).
The 'Employee No's' form opens by a command button in the 'Employee Details' Form. However, what I need to be able to do is for the query (used for the combo box) to update with information I have just put in the 'Employee Details' form while I have not yet closed the 'Employee Details' Form.
Would I use an SQL statement for this? If so what would I put?
In the attached databse, there is an Available Days function in qryMain. I'd like it so that when a day of the week tickbox is checked, to update the query. However I can't seem to get the Available Days function to access information from the query. I've had this working using a form previously, but can't seem to get it working with a query. I know my code is messy, still figuring out access, and VBA stuff.
I appreciate I also posted about this a while back, and that was a big help. Since then though, I've had to rebuild the database, and this is the best version so far. Would love to get it working like I imagine in my head! :confused:
I'd also like to get the frmUserList page working so that when Open User button is selected, it will open the users page, but give access to all user so you can switch through them. There doesn't seem to be a wizard way of doing this, do I need to do more VBA code?
I have a query that is running a search form. The query is made up of three tables:
1.tblMain 2.tblSpouse 3.tblChildren
When I have the query looking at just tables 1 and 2 it works fine. But when I put in table three my query doesn't work correctly.
Here is what it is doing. On my search form I have a main window that lists all of the entries into the database. I then have two subforms one for spouse and one for children. The spouse now shows this:
Last Name Washington Washington Washington Washington Washington Washington
First Name Janine Janine Janine Sally Sally Sally
(two wives, 1 current, 1 past)
And the children show this
Last Name Washington Washington Washington Washington Washington Washington
First Name
David David Mines Mines Foy Foy
And the one entry that does not have any children is no longer showing up in the main window with the other entries.
Here is the sql of the query:
SELECT tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber, tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName, tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber, tblChildren.LName, tblChildren.FName, tblChildren.MInitial, tblChildren.DriversLicNumber, * FROM (tblMain INNER JOIN tblChildren ON tblMain.EntryNumber = tblChildren.EntryNumber) INNER JOIN tblSpouse ON tblMain.EntryNumber = tblSpouse.EntryNumber WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And [tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber, tblChildren.EntryNumber;
Sorry for the long post but I wanted to make sure most if not everything was covered.
If I had a Form 'FrmForecastReview' Based on a Query 'QryForecastReview' I am wondering whether i can dispense with having a query and just use SQL statement in the properties of the form to query and retrieve the data i am looking for?
If yes would this be a technically and/or faster way for Access to produce datasets?
This is my first post and would like help with the following:
I have a warranty database that I have to create adjustment claims. There a re two pricing structures depending on the customer type. One customer type is SH and the other is DD.
I need to create an expression within the query that will allow me to add 1.0639 to the SH pricing (SH*1.0639) for all customers = DD.
So, when I pull a DD customer 1.0639 (6%) will be automatically be added to the List Price.
When I pull a SH customer the price will be the regular list price already housed in the database.
What type of statement will I use here. IF, SELECT or what? and if so, what would it be?
I hope this is clear and appreciate any help or feedback that I can get on this.
I am stumped and need some help I have a simple select query used in a combo box to filter what is shown. As shown below.
SELECT [Task Type Drop].[Task Type Id], [Task Type Drop].[Task Type], [Task Type Drop].[Task Category], [Task Type Drop].[Task Type Definition] FROM [Task Type Drop] WHERE ((([Task Type Drop].[Task Category])=[Forms]![Task Category and Area Look up]![Combo158])) ORDER BY [Task Type Drop].[Task Type];
Is is possible to only have it use the criteria if a nother field [Combo158] is not null?
Simply put I would like it to be if [Combo158] is null
SELECT [Task Type Drop].[Task Type Id], [Task Type Drop].[Task Type], [Task Type Drop].[Task Category], [Task Type Drop].[Task Type Definition] FROM [Task Type Drop] ORDER BY [Task Type Drop].[Task Type];
If not null
SELECT [Task Type Drop].[Task Type Id], [Task Type Drop].[Task Type], [Task Type Drop].[Task Category], [Task Type Drop].[Task Type Definition] FROM [Task Type Drop] WHERE ((([Task Type Drop].[Task Category])=[Forms]![Task Category and Area Look up]![Combo158])) ORDER BY [Task Type Drop].[Task Type];
I was using this IIF statment in my query but it is giving me a parmeter box with dd and then pulls up another box with sh before running the query. The query is not working.
I'd appreciate if someone can help me fix this issue.
I want to show cell values in a form from a table the form is not sourcing data from where the primarykey of both tables commensurates and tried do this thru a query, but id doesn't work. what would be the expression in the text box or the SQL statement for this.
