Modules & VBA :: Get Value Of A Control In Another Running Database
Nov 7, 2014
I have a POS system, packaged with Access Runtime, running on a PC and have developed a database system to provide additional function. I have no documentation to the names of the Forms or Controls in POS. My ADDON system does link to the POS Tables and has some Tables of its own.
At the time in the POS system where the cashier checks out the customer, they need to AltTab to my ADDON system to enter some information for the customer. They remember to CustomerKey from the screen on the POS system and key it into a Form Control in my ADDON system.I want to, from my ADDON system, read the value of the CustomerKey in POS and fill it in so the cashier does not need to remember/type it.
Is it possible to get the data from another already running Access database?I don't know the name of the control. Is it possible to run through a list of Forms and Controls in the POS system from my ADDON system?
I do know the name of the field in the Table and expect it to be current and might have to get that?Failing all that, is there a Collection of running Access databases? I could go through its Forms and Controls one time looking for what i want.
I created a database that uses the "lngEmpName " number to give user permissions , what I need is to be able to disable the running of the database (or main form)and show an error message using vba if I have not logged in for around two weeks is this possible ? if so how and even better a demo would be fantastic.
When the user enters the quantity they require into the orders form I would like to deduct it from the quantityinstock field in the products table.
The above query does work if you add the following code to the beforeupdate event of the orders subform: If Me.NewRecord Then DoCmd.SetWarnings False DoCmd.openquery "qryupdateQuantityinStock" DoCmd. Setwarnings True End If
Although I wanted the query to run from a button (confirm order) on the order form so that the user decides when to deduct the stock.
I cannot put a button onto the orders subfrom where the query should run from and if I place the code on a button on the main orders form it only deducts one order (one product) and not all products on the order.
Can you help me find a way to run the query from a button?
I have read through the threads and found lots of information on stock control, although have not yet found anything similar to this problem. I understand that this is not the correct way to do stock control, although my database is for academic purposes and only requires a simple stock control system.
Any help would be greatly appreciated (sorry for the long post!). :)
I have a form that has five subforms on it. On Open all the subforms are unbound (so i can prevent the queries from running before the On Open event to speed up). Then i can enter my criteria for all five subforms in txtbox and click run. After I establish the source object for the subforms, i cannot establish the controlsource for several text boxes that pull the data from the subform, i get #Name?. her is a sample from the on click event... the top links the unbound subform and the bottom should then link a control in the subform to a control on the main form.
Me.Child167.SourceObject = "query.RP Sum Fuelman F1" Me.Text71.ControlSource = "=[RP Sum Fuelman F1 Subform]![cntRecords]"
I have several queries that perform an audit on data. I want to create a form that has two buttons, multiple labels and textboxes. When the first button is pressed, it runs all queries and returns a count of the records within each query into the corresponding textbox. Then I want a a button that will clear all textboxes. I don't want to see the queries, I just want them to run in the background and the count results to be populated in the textboxes.
I am using a web browser control on one of my forms The problem is that when I close my application down, after using the form with the wbc, an access process keeps running in the background. More severe: every time I start my app again and close it, another access process is added to the processes that are already running.
I have an access form (Customer) along with a sub-form (Work_done). On sub-form I have Running sum of Amount in Text box-1. I want that Total of Running sum be replaced with Main form's tables field total_amount. Is it possible that we can replace an amount of control of sub-form into main form's field?
Question: Is it possible to see if a database has stopped running (errored out, timed out, or if code builder is open) thru another database. I know how to look for the .LDB file to see if the database is open...but is it possible to know if it's broken?
The reason I ask is that I have a database on a pc (can't be put on a network) that updates/refreshes every 5 minutes. Once in awhile someone will do something and cause the database to halt (code builder window opens). Currently the way I check to see if the database is running is to VNC onto the pc from my pc and see if it's running. Does this make since?
I'm having a bit of a problem getting my database to work running under a subdomain. When going directly to the correct subfolder (http://www.nettfun.no/funmag), theres no problem. But if i so try the forwarding subdomain http://funmag.nettfun.no i get the following error:
Microsoft JET Database Engine error '80004005'
'D:.......' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
Hi all, I am trying to update a table in a database (Lets call that DbNew). I want that when DbNew form is loaded it automatically updates its underlying table (lets call it TblNew) by running a make table query (called "coversheet") that is located in another database (call that DbOld). DbNew and dbOld are all on a network and I dont have total access on DbOld (but I have created the make table query "coversheet in DbOld). I should also state that DbNew is bound to TblNew and I am not interested in keeping the old table in DbNew. Information is frequently updated in DbOld and all I am interested in is loading the most current info in DbNew so the current table can be deleted upon loading DbNew and a new one created. Here is what I tried doing and I have it in the form load event of DbNew. This however returns an error (Runtime Error 3010. That TblNew already exists)
Dim db As Database Set db = OpenDatabase("\networkdbOld.mdb") db.Execute ("Coversheet") DoEvents Set db = Nothing End Sub
P/S: I am not good at access in anyway I should state. Thank you for taking the time to read.
On occasion my access database all of a sudden begins to operate very slowly, I notice the queries take a much longer time to run.
Last week it suddenly began to operate very slowly after idling in excess of 30 mins
Today when I change the screen to design view and then back to form view once again it begins to operate very slowly. (yesterday was working fine on my desktop at home, I have not made any changes to the database with exception to saving and using the file this morning on the shared drive at work) If I shut down and re open it operates well but once again changing to design view causes the same issue. Sometimes compact and repair rectifies sometimes not.
I have a database tracking a budget. It is like a cash boom. I have deposit and make payments. Supplier, invoice# etc. I would like my form to show a running balance of my deposits and expenditure.
I have just installed the windows version of Access 2010 on OSX Mountain Lion using a program called Crossover which works very similarly to WINE. I have found that Access will install fine except for VBA form modules and BAS modules, although there seems to be no indication that they did not install as the entire install progresses fine.
I've put together an invoicing program that was created in Access 2010 in Windows but have transferred it over to Access 2010 installed on OSX. It contains a number of forms with code but when I go to VBE, form code modules are not visible. When I try to view the code for a form I get the following error message: "Microsoft Access failed to create the Visual Basic module. If your database is on a network drive, check your network connection, then try again". I was able to insert a BAS module and create a rudimentary VBA subroutine which ran without error so it does seem able to interpret VBA as per normal.
I'm trying to run an sql update query (in VBA) to update values in a table, from calculated field values in a subform (using a query as a record source).
Is this possible , and if so, can I call the subroutine from the parent form, and what would be the correct syntax for the VBA sql string (i'm guessing it would be a docmd.runSQL sqlstringname )??
I have determined the solution to my problem lies outside of normal SQL queries and I need to create a UDF.
Here is the problem. I need to create a running total based on the sum of two fields [PTS_ISSUED] & [PTS_REDUCED] with one restriction. The total can never be less than 0. If it is less than zero, the totals reset with [PTS_ISSUED] as the new starting point.
I've attached an excel spreadsheet. Column"C" contains the formula logic I am trying to replicate into access.
I will be honest, writing the UDF is beyond my current knowledge but once I see it I am able to understand it and implement it.
I believe the solution needs to store the running total into a variable, make the necessary comparisons and determine if its okay to add the [PTS_ISSUED and [PTS_REDUCED] to the stored total of the previous calculations then advance. I just have no clue in how to go about writing it.
This calculation needs to be presented in Access because the results will drive several other functions within the database.
I have 2 forms and a macro in Form1 which runs throught every record to update the records, however.I would like to run this macro from the main form (Form2)
I am running delete and update queries using docmd.runsql. I am finding that particularly after the update query is ran the record is locked and other field in that record can not be updated via a form without a warning saying another user has made changes to the record. To be more specific -- I have a vendor table which has fields vendorname, vendorstreet, vendorcity, vendoraddress. A form is used to place a street address to the vendorname. onexit of the street textbox an update query is ran using DoCmd.RunSql updateing the vendoraddress field to an 'X'. When I put the city in and then close the form this is when the error occurs stating that another user has made changes to the record. My thought is that the update query has not unlocked the record. The customer wants the X to be placed in the field without his manually placing it in the field.
I have a situation where I am using a maketable query to create a table and then I need to use append queries to then add additional records to the created table - some of these are just run once and some multiple times.
if possible, I do not want to hard code the query multiple times i.e.
I posted this within the Reports as well, but since this has a little bit of VBA decided to ask this here as well.
I have a report that is created from the following query (qryTotalProjectHours). What I am trying to do is get the total hours spent on Tasks within a given time period. A given Task can we worked on by multiple individuals and hence I need to find a way to aggregate the hours spent.
Code: SELECT TasksEntries.Project, TasksEntries.Task, Sum(TimeTracker.WorkHours) AS TotalHours FROM TasksEntries INNER JOIN TimeTracker ON (TasksEntries.EmployeeId = TimeTracker.EmployeeId) AND (TasksEntries.TaskID = TimeTracker.TaskId) GROUP BY TasksEntries.Project, TasksEntries.Task
I accept the start and end dates in a form and pass it like shown below. WorkDate is a column in the TimeTracker table and is not present in any other table.
When the report is invoked, I get a box where it says "Enter parameter value" for Workdate..
Is there anyway I can get rid of the prompt? I never thought you needed the column name in the SELECT statement to be able to run this.
I should add the I tried the query with the WorkDate hardcoded in there and it worked fine and returned the correct results
I have attached the cut down version of the database that I am working on.Open up the frmManagerReport Form Leave the Employee drop down empty Enter the start and end dates ( I have used 4/1/2015 and 4/30/2015) Select the 3rd option "Generate Tasks by Total Hours" click on Run When you do that you will see the prompt come up. Enter any date and you will see the report. The report generated uses the results from the query and does not filter on the date selected in the form. This is what I have been struggling to fix since yesterday but have reached nowhere..
I found code at this website that is supposed to determine if a specific program is running on your computer. I have a couple questions about the code here. One, when I run it just the way it is and call it in the immediate window with the ?fIsAppRunning("word") command, it tells me "True" whether I have Microsoft Word open or not.
My other question is this: After the code is working properly, how can I add new programs to it? There are two programs the end user might need open to complete a process. One is called Extra and one is called tn3270 (this is how they show up in the Processes tab of the Task Manager). I think in order to add them to this code, I need to know a "class" to associate with them.
I am pretty new to VBA coding and need running queries on specific tables. This is part of a multistep process, of importing data that needs to be transposed before appending to the "cleaned" database. First, users will save auto generated, Excel workbooks from a machine into a designated folder. I have code that will import these workbooks (an unknown number at a time) into Access in separate tables by workbook.
Each table will have a similar name, but different extension (ie Sheet101, Sheet102, etc.). This is where the issue arises. The files are in long form, not wide. I have a series of queries (unfortunately, they're not SQL queries, but I can convert them to be) that transform the data from long to wide. However, I do not know how to go about writing code that will run the queries ONLY on the imported tables (again an unknown number of tables with similar names), not the rest of the tables in my database. I'm guessing it involves a do loop, but I am not positive.
I have a query I am trying to build currently which creates a running total based on set criteria. I essentially have three columns (All times are in minutes). The visit ID, the duration of a visit and the time difference between the current and previous visit. e.g.
So a line will have the calculation of the current time + the difference between the current and previous visit if the difference is below 20. I have the following:
Code: Public Function Cont20(MyVal As Long, MyDif As Long) As Long Static OldValue As Long Dim NewValue As Long If MyDif >= 20 Then NewValue = MyVal OldValue = 0
[Code]....
Which I put as Cont20([Duration],[Difference])
The problem seems to be on the return aspect, its not calculating correctly. I had the formula in Excel which worked great, but this is proving troublesome. As a note the Excel formula was such that it would add the previous running total and then add the current duration and current diff. e.g. In cell A7 the formula would be A6+B7+C7 and so on.
I need to stop already ran procedure or function with vba code . So I use END command ans everything stops.. in some cases that is useful, but some times need I to do something else after I use END command, but after END everything stops...
LIKE THIS :
SUB () END ME.CTL.SETFOCUS 'OR CALL function EXIT SUB
I wrote some code in Excel to open and run a module in Access. The code executes, but when I check the results I get no records, however if I just step back 1 line and rerun the query, I get records. Alternately, if I run the code twice it will get results the 2nd time.
The OpenCounter opens a Session in the terminal screen then performs a macro that writes to a temporary table.
Here is the code:
Dim db As ADODB.Connection, Rst As ADODB.Recordset Dim Acc As Access.Application Set db = New ADODB.Connection db.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBPath Set Rst = New ADODB.Recordset
[Code] ....
One thing I tried with limited success was to tell the OpenCounter to open and close the table after it was done writing to it, but that seems like a lazy solution and it also doesn't always work.
The problem is lately; while testing I am running multiple queries in a routine. When it fails; its hard to identify which query has the problem. So I hit control break; debug and try to find it. After I fix it; I debug and reset; i get this continuing hourglass thing in the form of a spinning circle until I close and reopen the database. I think I need better error handling but not something really complicated because I need to put it in quite a few routines throughout the database.
