Hi everyone,
I was wondering if this is possible to do:
I have a Form that reads from one Linked Table and Updates a Local Table. Is it possible to move the Local Table to the Back End and still be able to append fields and change field names etc?
If anyone can help, that would be greatly appreciated. Thank you in advance.
Bear
Hello
I have a table with 3 fields:
- ID no (primany key), Time1 and Time2
I have 3 records with ID no 1 to 3.
So my table look like this
ID no: Time1: Time2:
1 [emty] [emty]
2 [emty] [emty]
3 [emty] [emty]
I have an Excel document with the data for field Time1 and another Excel dokument with the data for field Time2. Excel doc1 have 2 colums named ID no and Time1. And Excel doc2 have 2 colums named ID no and Time2.
How do I import/update these data into my table?
I have tried wiht a adding-quiry but it only works if the table is complety emty and only with one Excel doc. Trying the second Excel doc afterwards only makes an error and no fields are updated.:confused:
Hello,
Been wondering how I can update fields in my Access database table using data that lies in an excel spreadsheet.
They have a common row ie say account number and other common fields that need to be updated.
thanks
I have two forms linked by a field "JUVIS". My problem is that I can't get the second form to show data newly entered on the first form without moving to another record on the first form. I have a command button on the first form that
opens the second form. All works well, meaning the forms are synchronized,based on the JUVIS field, except in the case of entering new data. In this case the second form opens to a blank screen instead of showing the JUVIS field. The forms are both linked to tables not to queries. Any help would be appreciated
I'm using the "Linked table manager" to update one of my tables. I added an attachment field to the table, and in order to add attachments I need to refresh the link in my front end. However, when I refresh the table, it prompts me to choose a new location.
I updated several other tables,I'm not sure if I should save it to a new location or what? I don't want to have to create a new location, I just simply want it to refresh the link.I actually deleted a table that had a similar name in my back end, Well I never deleted that table from my front end. I had accidentally been clicking that table to update instead of the one that was actually requiring the update.
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.
I am not sure where to start with this and any help in the right direction would be gratefully appreciated.
I have a linked table to Client's table in Access (accessed via a VPN Connection) for jobs carried out by me. I have to update this table with information from my system (not access) and the information in the fields will change daily as work progresses on these jobs. Rather than going into each record individually, I have the information exported from my other system (in excel format). I have a unique Order_Reference number that will not change but all other fields may change as and when.
At the moment I have simply been deleting all the records in the linked table and re importing them from an excel spreadsheet. Ideally, I would like to just update them rather than delete them first. What is the best way to go about this please?
Hi, I have the following structure:
Products 1-M ORDER DETAILS M-1 ORDERS M-1 CUSTOMERS
I have ORDER DETAILS set as a junction table so that many products can be recorded within one order. All is good apart from when i go into ORDERS and create a new order. I click the subform which links to the ORDERDETAILS. I then pick a product number(look up from products table). The problem is this: In the ORDER DETAILS I want to display the unit price of this product simply by picking the product id.
Eventually this would form the basis of an order form where I can pick Product Id and have it display unit price.
Any ideas on this one, I'm sure its quite simple!!
Thanks in advance
I have a form that users enter billing logs in. There is a button users can click to the right of their billing data that opens another form where they can enter details regarding the billing log. Beside this button is a check box that shows them if details for this particular billing log has been created.
I can not for the life of me work out how to get the check box to update with a check after I enter Billing details without opening and closing the form again. I felt I have tried requery and refresh in all ways I have seen here, yet it is not working.
The main form is frmDslEnter and the billing log is entered in a subform from here called tblSessions subform. The checkbox on this subform is called chkProgressEntered, and it's control source is =Not IsNull([DSLIDNumber]). I tried putting the refresh on the Close button on the Billing Details page, on the Got Focus of the subform and the main form and haven't had any luck.
Hello Tech gurus,
I have a database with existing data, that is not normalized, :eek: and all the data is in one field... This what I am dealing with:
As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY on the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.
Field1:
C10A CHOLEST&TRIGLY
ATORVASTATIN
LIPITOR
PFIZER
SIMVASTATIN
SIMVASTATIN TEVA
TEVA
M1A ANTIRHEUMATIC N-STEROID
ETODOLAC
ETOPAN
TARO PHARMA
Thanks so much for your help... :confused:
Nana :D
Hi,
How can I get a field to update automatically after data has been imported into the record.
Manually it works using the DLOOKUP in the "After Update" properties but this does not work when importing data.
TIA
Mike C
WorkID is a look-up field, so its Data Type is Number.
In the same table, DailyGrind is a field with the Data Type of text.
I would like to update WorkID with the jobs in DailyGrind.
Surely there must be a way to do it, gurus?
I have 2 tables.
Table 1: Master table
Table 2: Temporary table
This is my current process:
Every quarter I run a report that pulls loans that meet specific criteria.I export this report into excel (the loans fall into column A)I add a file number and box number in columns B and C.I import the excel spreadsheet to table 2 (they're linked so I don't need to import, it's automatic)
Now that I have the updated information back into the database (table 2), how can I get this information back into table 1? The excel spreadsheet only contains a few loans that need to be updated in table 1. I have tried creating an update query with both tables linked and use the "update to" field. However, when I tried to run the query, it says I have 0 records updated.
My update query is as follows:
Field: Access Bar Code
Table: Table 1
Update To: [Table 2].[Access Bar Code]
Field: Access Box Number
Table: Table 1
Update To: [Table 2].[Access Box Number]
Basically I'm trying to have the query update specific fields in table 1 based on the information from table 2.
I have a database consisting of three linked tables, with ID as unique identifier (primary key).
I would have expected the data type for my unique identifier, especially in the main table, to be Autonumber. However, I discovered with some surprise that the primary key (ID) in
Table 1 (Main table) is data type: number, field size: double
whereas in table 2 and table 3 the ID is data type: autonumber (field size: long integer)
It must have happend "SOMEHOW" when I divided the original single table.
Is this as it should be? Or could it lead to trouble later on?
If not as should be, is there anything I can do right now to rectify the situation with risking upsetting the relationships and all?
What precautions should I take, apart from taking a backup?
Thanks for your help.
Adrian
I have two tables, one is a list of customer details including email addresses and the other a list of customers and email addresses that have unsubscribed from my mailing list.
How can I use the unsubcribe table to update a field in the details table so I know who's unsubscribed?
I am linking a table from another database, and for some reason a few columns are not showing up on the front end.
Access 2007
I'm running an update query that's based on a select query (that runs some calculations). The update query is updating ALL the rows that should be updated with the information in the select query with data from the final row in the select query, and not on a per ID basis as I think I have it set-up to do. The data looks correct in the update query, but again it's not coming out right.
Here's the SQL for what I've written so far:
UPDATE [Customer_Data Query], Customer_Data INNER JOIN Baseline ON Customer_Data.ID = Baseline.ID SET Baseline.[Unit Hours] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units], Baseline.Availability = [Customer_Data]![Perceived_Avail], Baseline.[Hours Available] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units]*[Customer_Data]![Perceived_Avail],
[Code] ....
I've also tried to force the update to the proper row by adding a criteria based on ID.
Select query, here it is:
SELECT Customer_Data.ID, Customer_Data.Data_Set_Version, Customer_Data.Number_Units, Customer_Data.Perceived_Avail, Customer_Data.MTTR_MTBF, Customer_Data.MT_TR_OR_BF_Hours, Customer_Data.Utilization, Customer_Data.Percent_Scheduled, Customer_Data.Sched_Percent_of_PM, Customer_Data.Sched_PM_Duration, Customer_Data.Sched_CBM_Duration, Customer_Data.Sched_CBM_From_PM, Customer_Data.React_Detect, Customer_Data.React_Rework, Customer_Data.React_False_Alarms,
[Code] ....
Hello everybody. I am new to Access and as such I don’t know how to perform the following. Please help me out.
The project is about a library loan system. I have a database consisting of the following tables: member (MemCode, LName, …), book (BkCode, Title, TotalCopies,…), purchase (PurDate, BkCode, CopiesBought,…) and transaction (MemCode, BkCode, RentalDate,ReturnDate,Returned).
TotalCopies is the total no. of copies there are of the book
CopiesBought is the number of copies that have been purchased
RentalDate is the date on which book is being loaned
ReturnDate is the last date on which the book has to be returned
Returned is a Yes/No field to indicate if the book has been returned
In the library a member can take only one book at a time.
Problem 1:
The library may have several copies of a particular book, but all will have the same code. For example, a book, let’s say Harry Potter 6 has code 97. Initially only 1 copy of the book is purchased. When the librarian sees the book has great demand, he decides to purchase 3 more copies. All the copies will have the same code, i.e., 97. Only the TotalCopies will become 4.
BkCode is Autonumber in book table and Number in Purchase table. Relationship is 1:many.
I have a Purchase form to record details of books being purchased. I have a command button “Book form” that opens the book form when the user clicks on it. He will use it if the book being purchased is not already available in the library. What I want is:
(i)If he opens the book form to fill in details of the book, then when he closes the form I want the book details to automatically appear in the corresponding fields on the Purchase form.
(ii)The CopiesBought will have to be used to update the TotalCopies in the book table. How should I do this?
Problem 2:
When the user wants to record a loan, the system will need to check if that member already has a book which he has not yet returned. If it is so, then the system will need to provide a prompt to inform the user of this and consequently blocks the user from completing the transaction. How do I do this?
Thanking you in advance for your help.
I've been working on this problme for several weeks, thinking about it for the last month or so, and am at a loss of what to try next. I'm working on a database for my office that tracks all of the permits we have issued. Every year our permit fees increase by the CPI (consumer price index) and the permit fee entry in the table needs to be updated (75 to 80 entries).
Rather than typing them in every year (too many chances for errors) I'd like to be able to update our fee schedule table (8 entries, look up table) and have that table update all of our fee entries in the permit table.
Any ideas of how to go about this? Thank you for your help.
Scott
I have a data entry form where country(ies) (there can be more than one for
each project) is/are entered in a sub-form on a tab control. Countries not
listed are entered by the following:
Private Sub CountryName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl
strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to
add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tblCOUNTRY (CountryName) "
strSQL = strSQL & "VALUES('" & NewData & "');"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub
This works fine. However, if I restrict the country names by a region
selection on the main form (for example if region Africa is selected on the
the African countries are preselected) the NotIn List does not work. Any way to work around this?
Another question, I want the user to select a region (a combo box ) on the
main form and when a new country (using the NotInList event) is entered in the subform both values go into
the same record in tblCountry (which has two fields CountryName and Region).
For example, I enter AFRICA for region on the main form and on the sub-form I enter Malawi which is not listed. I have tried the following:
Private Sub CountryName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl
strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to
add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tblCOUNTRY (CountryName, Region) "
strSQL = strSQL & "VALUES('" & NewData & "', FORMS!frmAddPro!Region);"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub
But it does not work (Region is the control on the main form named frmAddPro).
Any suggestions would be welcome. Thanks.
Niels
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded
121/2/201310:00:00 AM3:00:00 PM
221/3/20132:00:00 AM11:00:00 AM
321/4/201312:15:00 AM11:30:00 AM
421/5/201310:25:00 PM11:00:00 AM
531/2/201311:00:00 AM3:30:00 PM
631/3/201312:00:00 PM10:00:00 PM
731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
I decide to add a new field to its related table. I always wait to create the form until I think my table is complete, but sometimes I just end up needing to add more info. Is there a quick way to update the form to include my new fields?
View 7 Replies View RelatedI need to create a form that automatically populates Dealer_ID in my Customers table when a user selects Dealership from a dropdown box.
The form is updating all of the user controlled fields in the Customer table as the user inputs the information. I have a separate Dealership table who's Primary Key is Dealer_ID where dealership information (including the dealer name) is stored. A one to many relationship is in place that connects Dealer_ID from the Dealership table to the same field in the Customer table. The user inputting the information will know what the dealership name is but will not know their ID. I need a solution that will allow the user to select a dealership name in the form and auto-populate the Dealer_ID field in the Customer table with the appropriate ID from the Dealership table.
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?