Hi,
I have a database created in Access 2000. It works OK on computer number one. When I copy the data base to another computer and open it, the table can not be found. The error message is "D:DocsdgrDocent Library Nov 2005 is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which it resides" The path mentioned is the original path on computer number one. The DB opens but it will not find the table or the queries etc. I thought that moving the DB had something to do with it but when I copied the DB to an external HD and then opened it, it works fine. But when I connect the external HD to the other computer it doesn't find the table.
Both computers have Access 2000.
Number one is XP and number two is W2000.
I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.
I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".
Order Details Table Fields and conditions/criteria: ID - primary key DiscountID - only when the DiscountID = 92 Voucher - only populated when Discount ID = 92
Codes table Fields and conditions/criteria: ID - primary key code = text field with a code like "einstein01", "einstein02" Allocated = False
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.
Hi, Newbie problem. I want to display on a form the last entry or (record) from a particular field of a table (the date field of the last club event). I have set the Form Property of 'Data Entry' to 'Yes' to prevent the user from having to scroll through records to get to the next blank record. Could someone kindly give me a clue how to go about displaying that last record of the field. Thank you.
Today is one of those days where I can't seem to wrap my head around anything, so here goes.
I have a database where the contact information is linked to an agency name via a third table which has the contact ID# and agency ID#, and nothing else.
In a fourth table there is a list of programs with three contacts associated with each program, for simplicity sake let's just say contact1, contact2 and contact3.
I am trying to create a select query which will link (via relationships) the programs, which are associated with an agency, to the contacts table via the agency/contact join table. I am getting a complete listing of the programs and their contacts, however it is coming out like:
Program Name Contact1 Contact1 Contact1 Program Name Contact2 Contact2 Contact2 etc.
I'm looking for help with this error message: "Microsoft Jet database engine can't find the input table..." And the reason it can't find it seems to be that it's looking for an DBF file (which was a format I once had the data in, a long time ago) rather than the mdb file which I have been using forever. Any ideas?
Is there a way for me to do a find/replace on a '#'? I am trying to remove all '#' in a text field. When I do a find/replace (replace '#' with ''), it removes all my numbers from the text field.
Is there a way, aside from using auto number, to find a records postion. I am using a list and want to be able to double click an entry and have it open a form and go to that record. However I can't use auto number because I allow for the deleting of records, and once deleted the auto number no longer corrilates to the records position.
If anyone knows of a different sytem that renumbers after deleting a record that would be great if you could share it with me. Or if there is a way to open a form and use goto record but instead of using record postion use where statement.
If anyone has any other ideas on this please share. Thanks.
I have a problem finding the last date entered in a table..The purpose is to find the record which entered last in a table. Is there any way to find the last record with date ? I am attaching a sample db with this. Please look that if my question is not clear here..
I've got two tables, both are indexed by customer ID, with a series of dates against the customer ID. One has a list of all dates a customer was visited, the other is a list of dates where activity happened on the customer account
I want to get a list of the dates when the customer was visited but where no activity happened on the customer account, i.e. where there is a customer visit date on the customer visit table but no record for that date on the activity table.
How do I do that? I can find all dates where was a date was on both tables, but how to find where its on one but not the other
I have a command button on a continuous form(form 1) and I need this button to open another form(form 2) when I press on it. So far so good.
When I press the button, I need some VBA to open the form(form 2) , search for a particular table name based on the open form(form 1) current record and use that table name as the newly opened form (form 2) data source. I have ways to do most of those task but for one thing:
How do I make access search for a table name containing a particular string? Here's what I am working with:
Code: Private Sub Commande26_Click() On Error GoTo Err_Commande26_Click Dim stDocName As String Dim stLinkCriteria As String Dim stDataSource As String
I'm currently busy with something for my thesis as a student and I need to use Access for this. I'm not too new at access, I know how to do the very basics, let's say on the level of [if field contains *"text*", return x].
However I am struggling right now on something that shouldn't be too hard... I could do it immediately in Excel if there werent millions of rows..I have 2 tables. Table 1 regards a list of patent publication numbers (eg. WO2012024604A3) and additional data (publication date, title, etc), only the publication number matters for me now.
Now what I'd like to do is to create a third table which has for each of [Table 1].[Publication number]:
Column 2) A count of how many times the [Table 1].[Publication number] is found in [Table 2].[Cited Refs - Patent] ...
Column 3) In case a patent is cited more than once, return the [table 2].[publication Number Citing Patents] value of the earliest citing patent (so with the lowest Publication Date value).
For Column 2 I had expected it to be an easy count(iif( [Table 2].[Cited Refs - Patent] = "*"&[Table 1].[publication number]&"*")) command but apparently it's harder than that..
I've got 2 tables, same structure, one [T-temp-Target] holds number of training units split by module a trainee needs to finish the course, the other [T-temp-Actual] holds what they've completed so far.
Both tables have structure TRAINEEID MODCODE CountOfUnits
I'm trying to find the modules that they've not done yet so I can add up the units for them, only modules that have been started are recorded in the table of what they've done [T-temp-Actual], modules they haven't started yet aren't included in it. Here's the SQL
Code: SELECT [T-temp-Target].TRAINEEID, [T-temp-Target].MODCODE, [T-temp-Actual].MODCODE FROM [T-temp-Actual] INNER JOIN [T-temp-Target] ON ([T-temp-Actual].TRAINEEID = [T-temp-Target].TRAINEEID) AND ([T-temp-Actual].MODCODE = [T-temp-Target].MODCODE) WHERE ((([T-temp-Actual].MODCODE) Is Null));
I have a requirement to export an Access 2013 table query to XML and i need to be able to replace all the invalid XML characters before I can export it. How can I do a table wide find and replace for these.
For instance: & needs to be replaced with & < needs to be replaces with < > needs to be replaced with > ' needs to be replaced with &pos; " needs to be replaced with "
I have to search the entire table and replace all these wherever it sees them and i have not found a way to do it
I have a database for work where I have a table of meters and a table of Faults which has a list of all faulty meters at one time. When a fault is repaired, I have a macro which updates the Meter Status to Working, adds a Fault Closed date, appends the record to the Closed table and then deletes it from the Faults table.
The user runs this from a form by clicking the Closed Fault button which activates the macro. I've added Echo on and off to hide that the form is temporarily closed while the Append and Delete queries are run and then it is re-opened again.
My problem is that the Form always opens at the first record in the Faults table. I would like it to open to the record which would have been next after the one that has been moved to the Closed Faults table.
Below is the code I have been using to test the Copying Meter Reference, closing and opening of the form and finding the correct record:-
Function CopyTest() On Error GoTo CopyTest_Err Dim strMeterRef As String DoCmd.SetWarnings False DoCmd.GoToRecord , "", acNext strMeterRef = Meter_Reference
[code]....
As you can see I am trying to go to the next record, copy the Meter_Reference by setting it to strMeterRef and then Find strMeterRef when the Faults form is re-opened.
I have a Macro embedded in a button which calls the above Function by using RunCode but nothing happens.
I've inherited a rather large Access DB with many tables,forms, reports, and queries. I need to duplicate a process, but I'm having trouble finding the update qry that creates new records in a table.
I could write my own, but I am trying to create an exact duplicate so I'd really like to see the code that creates the new records for a specific table.
Given the firmname and textbox name is it possible to programmically get the fieldname and table name where the data for that control is held.
I can use .controlsource and .recordsource
But is possible that .controlsouce is an alias of the actual fieldname.
Similarly the recordsource could be a query, from that I want to get the actual table, complicated say if two tables in the query had a field with the same name (even if only one was referenced)
I'm trying to make a form with a single text box that will search for the contents of the text box in a table. Once it finds a match, it moves the record into another table. Anyone know how to do this?
Access 2007 - I am using the oft used, frequently posted and shard code from srfreemen - modified as below. now I know I sort of broke commandment # 8 - Thou shalt not copy and paste other people's code without at least attempting to understand what it does. but I honestly did try to understand what it does.
I keep getting runtime error 3078 - cannot find the input table or query 'tblEnrolment_Committee_Master'. It is the name of a table in my database but I am missing the very basic element of how to get my form (which draws all of it's fields from that table) to look it up or identify it.
Likely because of a silly basic error. Normally - I wouldn't attempt such things but my work needs me to ensure no duplicates exist in this case AND it's already a primary key - but I don't want to wait until the whole form is filled out for it to identify the duplicate!
How to get the tblEnrolment_Committee_Master to be included in the search and erase this error ...
Private Sub Entitlement_File_Number_BeforeUpdate(Cancel As Integer) Dim SID As String Dim stLinkCriteria As String Dim rsc As DAO.Recordset Set rsc = Me.RecordsetClone
I have several tables which have an indexed, no dup field. When inputting a entry that is not in the referenced table, how can I be taken directly to the input form for that field.
Is it possible to have a Find & Replace in the table using access forms. ( Like the inbuild option in Excel ), I want the user to search some data & replace with the new data which may present in more than one record.
I want to do it with forms, because i dont want the user to use the tables.
I've got a Table with a string of data delimited by an "*". The first part of the string before the "*" can be variable in length, so if I use a LEFT expression to break apart the 2 parts of the string on each side of the "*", my results are incorrect. In MS Excel, I can use the following formula to solve the problem:
=LEFT(B6,(FIND("*",B6)))
You are just substituting the number of positions with a Find command that looks for the "*", and returns anything left of the "*". Unfortunately, the brilliant programmers at Microsoft, once again, don't have an Excel function in Access. how to recreate that functionality in a table in Access?