whenever I run/execute a query in Access it is modifying the first record on the table in which it is calling the data from.For example if the first record might contain the following:
Record A: John Doe, Oct, 2014, Account is Active
And lets say I am running a query to pulling records from Nov 2014.The Month and Year Fields in the above example for Record A gets modified to the query search parameters for Nov.Is there some of of record lock or controls that I need to adjust to prevent this from occurring?
I am trying to take input for my reports based on a query result dynamically for which i dont want to display the Datasheet after the execution of Query ,inturn i am displaying results in Report. Any help on this would be appreciated.
I am a new user to Access. I created couple of forms using wizard, but when I view them, they show me all the available data by default. I want to stop this, if anyone can help me plz.
I've created a form based on table DeliveryHeader with a subform based on table DeliveryDetail. The two are linked by Delivery# Supplier#.
On the form, Supplier# is a combobox that lists all suppliers from table Supplier. On the subform, Material# is a combobox that lists only the materials supplied by the supplier selected with form's Supplier#.
The problem is that this second list always shows the materials of the first listed supplier. For instance, when the form is loaded, it shows delivery #1 with all its details on the subform. Suppose supplier loaded to form is #1.
1) If I use subform combobox, it shows correctly supplier's #1 materials. But then, whatever delivery I navigate, it always shows supplier's #1 materials.
2) If I navigate to another delivery without using subform combobox, suppose I stop on a delivery where the supplier is #4, then subform combobox shows correctly supplier's #4 materials. But then, once again, whatever delivery I navigate, it always shows supplier's #4 materials.
I've spread Me.Requery here and there but without any success. What trick must I apply to force the execution of the query of the subform combobox Material# each time I navigate to a new record on the form?
i have created a new utility DB that has only one form..when it opens I set the startup form "Main" and on the OnOpen event of this form I have some code to open another database and close this one...
this means that whenever i open this database, it opens, opens up the other one and this utility DB closes...and i cannot access it coz it always opens and closes automatically...how can I stop it from executing to be able to edit its VB code?!
I am wondering if it is possible to execute a DTS package via Microsoft Access. I have done a bit of research and cannot seem to really find anything that claims this is possible or how to go about to get it to work. I am running SQL Server 2000 which has quite a few DTS packages ... it would be really neat if I could just create a small interface in Access whereby a form has a few buttons on it that can be clicked and the DTS packages can be executed on the server, this way folks won't need to log on to the server and run the DTS packages via SQL Server Enterprise Manager. What would REALLY be awesome would be if values can be passed between SQL and Access but I think if that is possible it would be quite an animal to achieve (I know it is possible via Visual Basic programming but not really worth all the work to create an independent program just to run a DTS package or pass a value). If anyone can provide me with any information or where to look I would appreciate it.
This is my problem. Almost all the time(90%), I open the main form of my application and a large part of the form is blank(white). If I open it from the Database window it often work well and all the form is visible.
When the bug occur, If I just switch from Access to another application like Internet Explorer and I come agian to Access then all the form is visible.
Is someone ever seen that kind of bug. What that can cause that problem. How can I solve it?
N.B. There is a lot of controls on my form and I guess that bug can be linked with it. Seems like Access is trying to open my form too rapidely.
I have a problem about running SQL statement in VBA code. I will appreciate a looot if you can help me solve the problem ASAP.
I tried to run the following statement in my VBA code to update a table in my database:
DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"
It turns out that the table will not be updated if I run the above SQL statement with my other VBA codes. (ACCESS doesn't give any error messages even though the database was not updated.) But if I run the SQL statement by itself rather than within the other codes, the database will be updated successfully. (When I set a breakpoint at the above SQL statement and run it manually within the other codes, the database are updated successfully too.)
Apparently, the SQL statement and the other codes don't have any syntax errors. I wonder why ACCESS doesn't execute the SQL statement when the SQL statement is put within the other VBA codes. Is it because the table it tried to manipulate was locked by the other codes?
The following are all the VBA codes including the SQL statement I'm talking about. When I run the following codes together, the database is not modified by the SQL statement (the last sentence). But if I run the last sentence and the other codes separately, the database was modified successfully.
Dim rstDataLoad As New ADODB.Recordset With rstDataLoad Set .ActiveConnection = cnnSHELL .CursorType = adOpenKeyset .LockType = adLockOptimistic .Source = "SELECT TBL_GRANTBASEDATA.* FROM TBL_GRANTBASEDATA;" .Open End With
'Calculate Retire_Date rstDataLoad.MoveFirst Do While Not rstDataLoad.EOF
Calculation omitted.
rstDataLoad.MoveNext Loop rstDataLoad.UpdateBatch rstDataLoad.Close Set rstDataLoad = Nothing
DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"
I'm using hte Shell() function but this doesn't wait for the executable file to finish so I'm looking for a command line function that would wait until the executable file is finished executing...
I saw this function WaitFor but it is not supported in MS-Access? So, what do I do?
I have this segment of code that adds data to fields FName and FPath in the table Files. It works fine but the syntax is a nightmare with all the double quotes and symbols.
I have another field in the table Files called CompletedBy that shows who updated the table (they type their name into a textbox Text1 before clicking a command button to run the code). I would like to add this data to the table during the execution of strSQL. All the records added to the table Files will have that value in the CompletedBy field (say, Bob Jones).I tried the below changes but the code doesnt work.
I have a form that loads when the database is open, and the form has buttons etc. that leads to queries and reports in the database. (Queries open in a form). Since most queries take a while to load, I was wondering if there is a way to Pop up a message box stating "Please Wait, Query is running..." (or something like that), and once the form opens the MsgBox should close automatically. (The user should not have to hit Ok button.)
Most users of this database would have restricted access such that they would not have navigation pane and toolbar visible - hence I want to make it a touch more interactive when a process is running...also if someone is new to access they might not notice that taskbar status and I don't want them thinking the program is stuck etc..
I'me running on Win7, Office 2010, Acc2003 format (.mdb), writing data to Excel 2010 (.xlsx)I have code to create an Excel file, send record set data to it, and then add some formulas and formatting. I was trying to tune up the formulas, when I got a pull back:Access VBA code suddenly stops running, and the file gets displayed in Excel
Code: 'ApXl is Application.Excel 'xlWSh is the Excel WorkSheet 'rst is my DAO.Recorset
I have finger print machine and i already connect to it and get all log.then i tray to get data and here is the code that im using
SELECT Format(CHECKINOUT.CHECKTIME,"dd/mm/yyyy") AS CDate, IIf([CHECKINOUT.CHECKTYPE]=I,Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm")) AS StartTime, Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm") AS EndTime, USERINFO.USERID, USERINFO.Name, CHECKINOUT.CHECKTYPE FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID=USERINFO.USERID WHERE (((Format([CHECKINOUT].[CHECKTIME],"dd/mm/yyyy"))='10/04/2014'));
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only. ([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null) How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
I have a link table with a field in GMT time. I want to convert the times to local time in a query. I know that I should use DateAdd, but I am just wondering how to know summer and winter time. On the summer our local time is +2 GMT and on winter +1.
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Ok, this might need to be in the macro section or something, but just incase, I have a query that I want to run at about 10pm every night (when everyone has gone home) , to make it even more complicated it queries from sage and creates a table each night (that i want to overwrite the old table) and it will need to enter in the sage login information :(
Does anyone know how to do this as to be honest, it has me at a bit of a loose end, I guess it would be a macro, but I have no idea of the commands used
I have a table that consists of [employee], [scheduledate], [starttime] and [endtime]. I need to query this table to get the total of all employees working during a certain period of time (ex. 1400 - 2200). I have been pondering this for quite a while and have yet to come up with something.
Any help you can provide would be greatly appreciated.
I have a query with the fields dtDate (Date/Time in Short Date format), NC "Number of Chats" (Long Integer), and TCT "Total Chat Time"(Date/Time in hh:nn:ss format) from tblChats. Each date will have multiple NC and TCT values. This query is totaling them by date with the following SQL.
SELECT DISTINCTROW tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date') AS ChatDate, Sum(tblChat.TCT) AS SumOfTCT, Sum(tblChat.NC) AS SumOfNC FROM tblChat GROUP BY tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date') HAVING (((tblChat.dtDate)>=Date()-7)) ORDER BY tblChat.dtDate;
The query works fine. Now my question.
I would like the query to figure the average time per chat for each day.
as the topic says, i'm a beginner playing around w/ access and i tried to make a query that used 2 tables, but the problem is that when i try to view the query, (since i'm using fields from 2 diff tables may be why) none of the data shows up and all of the fields are blank.
i'm trying to create a query that runs off 2 tables in order to better organize my data. so what i'm trying to do is (with the invoice database i'm trying to create) get some fields from table1 to automaticallly roll over to table2 on the query because table1 and 2 use a different invoice numbering system but some of the values are identical for the y/n fields.
does anyone know how i can fix my query so that i can make it that it merges 2 tables together and still shows the data?
