What is the best way to accomplish this? I have a database that split into the Front end and the Back end. The form that the users entering the information uses the queries from two sources. One is the a table within Access and the other is an excel spreadsheet that I link to it. How can I update /replace the Excel spreadsheet while users are using the form of the front End?
View RepliesOne of the forms in my database has a calculated text box control. The calculation is conditional, one condition being based on current date. One of the queries display that calculation. If the the form with the calcualted control is not opened before running the query, the calculated control uses the day the form was last opened as the "current date" which is not what I want. I want to do one of two things:
Either: (Preferably): update the form everytime the database is opened, so that the current date is actually the current date and not when the form in question was last opened.
Or: Update the form first before the query in question is run. This is not preferred because, if I use the calculated control in another query or something, I have to have another "update" step.
Please refer to the attached file. The form of concern is frmAssessmentInput.
Select the appropriate criteria per instructions (in red), including entries on the continuous subform. Then click the "Input data" command button. A 2nd form opens (frmObservations) that contains a textbox labeled DocumentStreamID, and this textbox SHOULD contain the value from the corresponding DocumentStreamID textbox contained in the master form, where you clicked the command button. However, even though the sources are linked properly, the value doesn't pass if you click the command button immediately after adding a new record to the continuous form; it only passes if 1) the master form is closed and reopened first, or 2) if you close the 2nd form, toggle to design view and back to form view for the master form, and then click the "Input data" button again.
What is going on here? Given that the value passes once another procedure is run, do you think this a requery problem? I tried placing a VBA control requery in the 2nd form's On Current and On Load events, but this didn't solve things for me so I took out those event procedures (code could have been wrong though).
I would like to add a spreadsheet to my form on a tab just like on the asset tracking data base template in access 2010. I can add an unbound object frame but then I can not edit it on the form??
Pic of what I want to do : excel sheet.jpg
Hello.
I currently have a data-entry form that users fill-out with data (duh!). The data comes an excel spreadsheet that is created during the work-flow process.
I would like to add an "Import" button the field that, when clicked, would prompt the user to select a spreadsheet (different each time) and once that was selected import data into the form (and ultimately the table). The data will always be in the same location (cells and sheet) within every speadsheet.
Any ideas?
Thanks in advance.
Sean
how to import an excel spreadsheet into my existing database to just update the records. I don't want to add any records at the end or link the sheet to the table.I also don't want to just copy and paste as I don't want my users to have access to the main table but they will need to have the facility to upload.
The reason for this is we have to send clients a copy of their relevant section of the table in a spreadsheet that matches the table format exactly and contains the correct ID numbers. They then send the spreadsheets back to us completed and I would like to update the fields of the existing records using the ID numbers.
I have a query that runs the saved export that exports data to an excel spreadsheet. When I go to run it again, it does not update the spreadsheet with the new data.
View 1 Replies View RelatedI have an access database with a linked table to an Excel spreadsheet. I have a form based on the linked table so the presentation of the Excel date is better. The spreadsheet is used by other staff in my office to record sales which I need to register with an external organisation. What I want to do is update the spreadsheet with the registration date from my access form rather than going back into excel to do it.
View 2 Replies View Relatedwhat I have in Access is a table which lists jobs via their HB Number (as well as a ID number access gives them). For each job there is a bunch of details and some Yes/No fields.Each day I will get a dump from another system that will list jobs like this, I then need to "Update" the Access table with any new information from the excel dump. The update would need to:
1. Insert new records (jobs) from Excel dump into Access Table
2. Update any of the records fields (except HB of course as its unique) in the access table from the Excel dump (the Excel dump obviously wont have access ID numbers, but will have the HB)For a real basic example:
Access Table Like So:
ID
HB
Finished
1
5A
Yes
2
5B
No
[code]...
the actually data has lots of different fields and many many many more records. But yeah basically need to update the table from an excel dump.I thought it was as simple as doing a Excel Import > Append Table thing in Access, but that just seems to add the new records and ignore the updated fields?
We are developing a work evaluation for all of out employees on the shop floor.
While Access is way easier to update and allow for quicker ways to gather information, our requirements is that we must display a training matrix. While access has great reporting features, it is tough to get it a format with all users listed on top, tasks listed on the LH side and knowledge levels showing what level each user is at for each task.
I am limited by standard practice on how to display it so I was curious to know if information updated in Access can be updated on an Excel spreadsheet as well. I know in Excel you can format one spreadsheet to update another but I can't figure out away to have Access do the same thing.
I have an Access crosstab query that I have exported to an Excel Spreadsheet. I have the spreadsheet formatted using conditional formatting and I'd rather not have to reset it every morning. It's a single spreadsheet (the columns/rows will not deviate greatly day to day) and should be very simple, but I'm not getting it for some reason.
So if I have "Test.accdb" and it contains "qryX" as my crosstab and "Sheet1.xls" is my formatted Excel spreadsheet, how do I code for the latest "QryX" to go in and replace the old "QryX" data in "Sheet1.xls" ?
I have an access 2010 database that looks after all the engine data for a fleet of engines. Each engine has a unique serial number and that serial number is displayed in a test box on a form using a search function.
Each engine serial number has a corresponding excel worksheet where the engine data is displayed as a graph.
Is there anyway that I can have a control button on the form that will allow me to display the excel spreadsheet corresponding to the engine serial number displayed in the text box?
I have a workbook which has links to access to import data. I have to have the information in Excel for a couple reasons:
1.) Many of our customers don't have Access.
2.) The customers use a "discount calculator" to apply their own discounts.
They also use it to then apply their own margin mark ups to be able to then use to quote to their customers. So a static report/pdf for them to look at doesn't work.
I've begun to create links from a number of queries in Access, which work fine. My problem is when I go to update the data. I'm not getting the following error coming up:
The database definitely hasn't moved. And I've run the queries in q, and there aren't any problems there. I've looked this error up and I'm seeing that it could mean that I've got some corruption going on.
Before I hit my main q, I should also note: I'm running all of this on a Citrix network. I should also note that it's a consulting gig. Once I'm done, I won't be available to fix major issues like this.
I have a database with three users, only one of whom has editing privileges. As such, I didn't see the need to setup a front end and back end (nor did I know how at the time). Anyway, that's not the issue. The database is saved on a networked computer that all users can access. Unfortunately, when accessing the database in the shared network folder, only the first user can access it. Other users will double click the file and virtually nothing happens.
However, users can instead open Access first, and open the file through the program fine. Why this would be the case and how I can have multiple users open the file from the shared folder?
So, I hope that i'm just making a simple error, because as far as I know this should work!
I have two tables. (and i'll just simplify it because the other info isn't necessary to aide me!)
tblProduction
ProductionID - Autonumber - PK
ProductionLocation
tblStorage
StorageID - Autonumber - PK
StorageLocation
ProductionID
Now. I have two forms. One is frmProduction and the other is frmProduction_Stored.
A user inputs in the frmProduction form and then has a drop down that determines the "disposition" of the items produced.
Once that disposition is selected it will open up one of three forms(depending on the value selected), in this example it is frmProduction_Stored.
It then opens up that form and fills in tblStorage.ProductionID with tblProduction.ProducitonID. And then the user can fill out the rest of information required for tblStorage.
But, the problem i'm having is that the records are not getting created in tblStorage. I have created the relationship and tried different things and have found out that they do NOT create only IF i have "Enforce Referential Integrity" selected on the reltionships! Otherwise, it creates the records perfectly fine, with the right ProductionID on tblStorage, but then the relationship isn't appropriate.
hmmm
Any ideas how I can fix this problem and make tblStorage create the records, with the right relationship?
Thanks!
It's easy enough to link to someone else's Excel spreadsheet and get their data into my Access database but I have to do it each month. They create a new tab each month (OCT-2007,NOV-2007,DEC-2007) for the month and the spreadsheets always have the same structure.
Is there anyway to tell my database to go out and look to see if the new spreadsheet has been created and if it has then import the data?
I have a Main Form that utilizes tabs to control several other forms by opening, closing and making them visible/invisible. One of the forms needs to be closed when its tab on the Main form is unselected. The problem is that once the form is closed, when it opens up again it opens right infront of my Main form's tab control so I need to move it down an inch to get to the tab control.
I've positioned this form where I want it and saved it numerous times. But once I select the tab, it opens up right ontop of the Main form's tab control again. I have the AutoResize set to No and the Auto Center also set to No.
Any ideas how I can get this form to stay put?
Any help is most appreciated. Thanks.
hopefully someone will know what to do...
i have a form that has a bunch of check boxes and labels that are hidden to start with,and become visible when a confirmation button is pressed. Wen i set this up in the design view i.e making their visible status = false, everything runs accordingly - however when the next person comes to update their details the form will not format properly. i think the reason has something to do with the check boxes having different visibilty statuses after the initial confirmation i therefore used code on the form open event
If Check2.Visible = True Then
check.Visible = False
End If
however this tends to fail at the first checkbox, however if i then press run on the VBA screen everything else seems to then run fine and so only the first checkbox is left visible...
can anybody help....
thanks in advance i hope i have made myself clear..
Hi
Is there a way I can make a form open to maximum size to fill the work area without click the forms Max button
dave
In excel we have this option were we can link a spreadsheet into a web information source , is there any similar and easy method in access ?
View 1 Replies View RelatedHey there, when a user opens my database, I want the form to enter records to automatically open. Can someone plz help with that... thx
View 3 Replies View RelatedHi all, I have a main form which contains buttons to control all other window. I'd like to prevent user to work on two forms at the same time. Can anyone advise me how to disable the main window while the second window is opened?
thanks
I have forms that show commitments by Client, by Cost Centre, by Provider etc.
If I add new commitments or edit commitments it is done through the Commitments by Client form, as all other forms lead back to this one.
To create a new or edit an existing commitment, it is done through a Commitment Form, which goes into a query (query A)which does all the calculations. My client form has subforms based on Query A, however my other forms (commitments by Cost Centre etc) have subforms which have querys that are based on Query A . I hope that made sense.
My problem is that If I edit a commitment, the client form and subforms refrlect the changes imediately, even though they are open and I can see them changing when I change the commitments. However the other ones don't unless I close them and re-open them.
My navigation within the database is based on once a form has been opened it is set to be invisible untill called on again (i.e. visible = False).
How to requery all the forms without having to close them.
I have main form with everything on it using tabs and so on and everything is working fine. What I would like to do is this. Since I am jumping frequently between different records and many actions require main form to be reopened or refreshed and when this happens the form goes to random records, I would like for form to go to last record i was on before it was refreshed or opened.
I was thinking using tempVars to set the id but the problem is that I am not sure what can trigger the TempVars since I am jumping between the records and not making any changes to them.
How can i deactivate mouse second button click on form?
Can anyone help me pleae? :)
I have two subforms in a tab formation sitting on a main form. They can both open up a single popup form (via a button) and that popup form is opened via openargs with the autoID field.
What I need to do is pass back a value on the popup forms [On Unload] event to the subform which opened it.
As the popup form is Modal, the subform which opened it can't be changed.
Am I right in thinking I can pass back the value to the form which had previous focus?
Previously, to pass a value from a popup form to a single form I've been using
Code:
If CurrentProject.AllForms("MyForm").IsLoaded Then
do this
else do this
end if
But in this instance, how do I code the On Unload event in which to tell Access which form opened up the popup form, and pass a value back to it?
I'm guessing
Screen.PreviousControl.SetFocus
has something to do with it?