I Have made a change to a field in my tables. it was was based on ethnic background and originally i had just created the field but had not added in the options ( via adding it into the row sources).
So now the tables field have been updated but unfortunately on the form it has not updated into the dropdown i had created containing the options..
I created a database with 10 tables and 8 relationships. Then, I learned about the Autonumber field type and decided that I wanted that to be the primary key of one of my existing tables, for a field that was already linked to 2 other tables. I had to delete the relationships and the original field to make the change, and Access said it had to delete an index, too. Now, Access won't allow me to re-establish those relationships.
I found that I had to change the fields in the other tables to match the Autonumber type and name of the original field, but now I am getting an error message stating, "Invalid field definition 'CustomerID' in definition of index or relationship. What does that mean? The field types and names are all the same.
I have a single table database for inventory. Every 3 months or so, some records get changed or updated. My manager wants to keep an archive of all of the records that have been changed so we can go back and look at an history of all of the records. So, My thought is to create an "Archive" table, appended all of the current records to it. Then, when changes are made, create an Append Query, or what ever works, to copy just the changed records from the Main to the Archive table. There are only 200 items in the inventory so it is not a large database.
View 1 Replies View RelatedI have some tables with data where in one column the user will choose a status between 30-40 different options. They are choosing this option to show a reason why a trade was pended and not approved immediately. These reasons seem to change frequently and sometimes we no longer want to use an old reason.
I do reporting back several years, so I cannot just 'remove' a reason or it will be removed from the table and I will have blank reasons. When selecting the specific reason, the table takes the dropdown from another table that simply lists every possible reason. I want to know how I can make it so that the reason that is no longer in use remains on that connected table, but when the person entering data clicks the dropdown button, the removed or inactive reason no longer shows up as an option.
Previously I had seen people add a column to the connected table of reasons and use a "yes/no" check box to show if the reason is now Inactive. When you checked YES on inactive, the reason would disappear from the dropdown list. My issue is that I do not know what language or formula to use (or where to use it) so that when I click Inactive = Yes, the reason disappears from the dropdown menu.
Three tables:
Employee, Sessions, EmployeeSessions.
Many sessions can have many employees - thus the joining table has been included.
When trying to delete an employee from the database using a form, I encounter the error:
The record cannot be deleted or changed because table 'tblEmployeeSessions' includes related records
Is there a problem with my table relationship structure? Or is it 'correct' that as the employee is supervising a session he/she cannot be deleted as this would interfere and maybe mess up the session record?
I created a table in a budget database without a autonumber field. I then inserted a autonumber field after creating the form by inserting a row in the table which works fine in the table, now i want to use a text box on the form with BudgetID from the autonumber field to give me the total amount of records in the database but the autonumber field is not in the record source dropdown.
View 5 Replies View RelatedAccess 2007
Unbound Form
I have a onclick tied to a label (for decoration purposes) that when clicked it launches VBA that essentially updates a form. All that part works except it will not recognize any changed value of the field I was last in?
Just to try to explain best as I can what happens.
- Form gets opened
- I change field (quantity field)
- I click the Label
- It reverts to pre-existing value.
if I click off of the text field first then do the onclick - it recognizes just fine.
Hi
I have a Form called Products. Each product is uniquely identified with a primary key called [ProductID].
Products contains a calculated field called [UnitsOnHand]. [UnitsOnHand] gets its values from a subform called ProductTransaction that exists within the Products Form.
ProductTransaction contains a field callled [Received] . At the moment [UnitsOnHand] will add up all the values it finds on the [Received] field to derive the [UnitsOnHand] value for that product at any particular time ie =Sum(nz([Received])) on [UnitsOnHand] .
I also have a Query AutoSell that outputs a value for each [ProductID].
How can I use these values for each [ProductID] generated by the Query to substract from the values on the [UnitsOnHand] calculated field on the Product form.
ViRi
I have an Access 2007 database. I have added in a LastUpdated field into my form, which will update when the record is altered. After searching around I was able to find this VBA:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![LastUpdated].Value = Now()
End Sub
This VBA works, however my problem is that when I try and switch records, using a combo box (which I made from the combo box wizard selecting the "Find a record on my form based on the value I selected in my combo box") I get Run-Time error 2448.
Need Help...
I deleted a field (Senority #) in my table and so on with the queries, forms. reports that had that field. I redone my query so that it would calculate my senority (thanks to all that helped). When I went back into my form that previously had the senority field I could not edit or change anything. All I got were beeps. I do not have any locks on the text boxes, combo boxes, forms, ect. Please help!!!!
I have a continuous form with an SQL query as its record source, it is a dynaset. Each row on the form is for a specific picture, containing a unique ID, a pre-generated caption field, and an official caption field. The idea is that users will edit the pre-generated caption field, hit a button, then their changes to the pregenerate caption will be made to the value of the official caption field.
My VBA code for the button works fine, in that it doesn't return any errors. The problem is that there is some sort of delay between when the button is hit and when the official caption field is updated. After the first press, the form requeries and the official caption field is the same, but after a second press the official caption field will display the user's changes.
First, a recordset clone is created using an SQL query, the pre-generated caption column is selected and then the results are filtered to the single record that shares the picture ID of whatever row the user was working in. Next, an SQL update runs, replacing the value of the official caption field with the value of the pre-generated caption that is contained in the recordset clone. Then the form is requeried.
I've tried adding a change of focus at the beginning of the code and a 15 second pause between when the update statement runs and when the form is requeried, neither solves the issue. The problem can't entirely be my code, because a second press of the button will make the appropriate changes.
I know what you're thinking, why have divide the captions into pre-generated and official in the first place - why not use pre-generated caption as the caption source? There is a separate feature that allows a user to mass edit captions and I decided to retain the original pre-generated caption as a field, in the event that the user makes a mistake or decides to reference the content of the pre-generated caption.
When I add new records to the form and close the form, the tables are updated with the new records, but the new records don't show in the form (navigation).
View 14 Replies View RelatedIn my Access 2010 database there is a form with a large number of checkboxes enabling me to select/deselect certain record details.
Each click on one of the checkboxes triggers a VBA routine setting true/false values in one of several tables linked to the main table.
A query over all records is filtered by the true/false values of the linked tables and the resulting set of records is displayed in the (continuous) form.
The header of the form contains a text field with the value "=GetRecCount()" displaying (correctly, but randomly fast) the number of records selected:
Code:
Function GetRecCount() As String
Dim NumRecs As Long
Dim rs As Object
On Error Resume Next
Set rs = Forms![Super Search].RecordsetClone
rs.MoveLast
NumRecs = rs.RecordCount
Set rs = Nothing
GetRecCount = Format(NumRecs, "##,###")
End Function
Now, the funny thing is that each click on one of the boxes results in 1 call of the VBA routine setting table values, but 21 calls (!) of the text field "=GetRecCount()" in the form. I have established this by incrementing a public variable at each pass.
Why a field in a form is updated 21 times when a filter is applied?
In Access 2010, I have a database with 2 tables "group" and "member". They have a many-to-one relationship with nultiple members per group. Group has identifier "ID" and Member has field "Group Code" that matches with "ID". Formerly we called this field "Bulgiin kod" but needed to translate it. When we changed "Bulgiin kod" to "Group Code", now when we expand the group records (click the + button next to the row) it gives "enter parameter value" box. I tried deleting the relationship and re-creating it but without success. If I change the Group Code back to its original value, everything works fine.
Here is another thing I noticed: if I enter the value of ID into the parameter value box, it just shows the entire Member table, regardless of the actual matches. So if I expand ID #15, and enter "15", the whole Member table displays under the record.
I have a SQL database with an Access front end that keeps giving me an error "The data has been changed... another user has edited the record.I'm the only user on it, and I'm trying to edit it directly from the table. I already checked my indexes and changed all the bits to ints.
View 6 Replies View Relatedi want to insert a calculated value of a e.g field6 (field4 * field5) in forms into the database table. by using expression builders in my forms. i have made that feild and on click it shows the calculated value but on filling in the rest of the form and clicking on Insert button does not update this calculated field but iserts all other fields.
View 3 Replies View RelatedI am importing the updated Employee Roster information from Excel to a table called "Weekly Roster Check" (contains new info). I have a table called "Current Chit Board" that has an Employee Roster that I update every week (contains old info). I want a query to find employees who are promoted and their title has changed. then I want to run an update query to update these results to the Current Chit Board table.
Every method I try, returns either no information or information that is not what I want.
I have 5 tables which comes from different departments in our company.
All of them have the same key "project no" but hold different information in other colums.
As a starting point I have secured that they all have same no. of records meaning all "project no" are in all tables but some table might not contain other information in that record than the project no.
I have put them into a quirie and on a form I am able to look at all data from all 5 tables.
I have linked them one to one and it works fine untill I add a record to one of the tables. Afterwards I am not able to write into fields from other tables because there is no automatic creation of the record in the other tables.
Please How would this be best/easiest to make for me. ?
The reason why I have to keep the 5 tables is that regular updates are avaiable form any of them.
Appricate any comments which can make me any progress.
How do I open a table and make my update querys run automatically before opening?
Fitzy
Could someone look at my code and tell me why my “Me.TargetDate.Undo” won’t work?
First, let me say that the TargetDate field has an On-Click event that calls a module written by Allen Browne), which is shown below and works great.
=CalendarFor([TargetDate],"Select a date from the calendar ")
What happens is that the Me.TargetDate.Undo event always keeps the date selected in the calendar.
Based on something I read from Rual Guy on another issue, I’ve used the TargetDate_LostFocus routine to check for some additional data entry errors. I’ve tried moving the code in “Private Sub TargetDate_Exit(Cancel As Integer)” to the LostFocus, but it doesn’t make any difference to the Undo. I’ve also tried “I’ve tried “Private Sub TargetDate_Change(),Me.Undo…” but that doesn’t work either. :confused:
Private Sub TargetDate_Enter()
If Not IsNull(Me.TargetDate) Then
OldTarget = Me.TargetDate
End If
End Sub
Private Sub TargetDate_Exit(Cancel As Integer)
If Not IsNull(Me.TargetDate) Then
If DateDiff("d", Me.DateOpened, Me.TargetDate) < 0 Then
MsgBox "You have selected a date that is before…bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
ElseIf DateDiff("d", Date, Me.TargetDate) < 0 Then
MsgBox "You have selected a date that is prior to today's date…bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
ElseIf Weekday(Me.TargetDate) = 1 Or Weekday(Me.TargetDate) = 7 Then
MsgBox "You have selected a date that falls on a weekend..bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
End If
End If
End Sub
Private Sub TargetDate_LostFocus()
If Me.TargetDate > OldTarget And Me.cboAssignToID <> OldAssignee Then
MsgBox "The target date can only be changed if …bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
End If
End Sub
Ever thankful for this forum,
Christine
Within my main table / form I have six status drop down boxes and would like to record with a date and time ever time they are changed and save this data in a second table.
If I cant do this with the six drop downs, can I do it if any change has happened to the recorded.
I understand the problems that can be brought upon ones self by creating the table with lookup fields, But if the table was orginally designed with them and then the Field is then changed to a Text box instead of a Combo box will the inherent problems associated with the lookup within a table disappear?
View 2 Replies View RelatedI have done this in Excel before, but not Access. I do not know VBA. I figure this will have to be done in a query or a macro. I don't even know if all of this is possible in Access. I need to be able to split an Address field into:
Street Number
Street Name
Street Type
Street Direction
And the purpose of this is so that I can pull out the Street Type (Drive, Road, Lane, etc) and update the abbreviations (DR, RD, LN) to the Street Type spelled completely out.
I did this in Excel by creating a Named Range "Types" on a sheet that has the abbreviations in Column A and the spelled out versions in Column B so that I could convert the abbreviations to complete street types. I broke down the entire address into each part on another sheet. Then I did a VLOOKUP to look up the Street Type in Column B in the Types range. Works great! And the only way I knew at the time to do that.
But, by doing this, I have to get the data I need from a download into a spreadsheet, break the address fields down on a second sheet, do all kinds of field update conversions (to get the field names from the download to match the field names in my Access table). This takes extensive Excel programming. I just thought there might be a way to do it by simply importing the data from the download straight into Access. That is easily done, but the Street Types aren't consistent.
There may even be a better way to do this than splitting....something like if a field CONTAINS DR update it to Drive. This would be a long process to set up because there are so many different street types to consider.
This is basically for the purpose of finding duplicates. If there is one entry called 123 Main St and another called 123 Main Street, they are not going to show up as duplicates, rather as two separate records.
Hi,
Im a relative access newbie, and am updating an already created form. I want the user to be able to change the value in a field , then when they exit the field, for it to be greyed out, so that no more editing of the field is possible.
I know how to lock the field in design view, but I want the field to lock after the user has amended it. Is this possible?
Many Thanks!
The database contains the records of a collection of thousands of photographs and negatives.
One of the fields contains information on the subject matter of each pic and can sometimes be very long. The field was changed from text to memo so as to hold more characters but they appeared in one long string, which means a lot of scrolling to see the information. That has been changed back to text and we are adding a second or third record such as xxx-xxx-xxx cont1 xxx-xxx-xxx cont2 so as to get shorter strings. Is there a way to make the text wrap onto a second or third line automatically after a specific number of characters have been entered or can a carriage return be put in to force the text to a new line?
I have a form based on a query I built that calculates time worked. you select the worker in a combo box which I created from a query that lists all active employees and their clock no. with clock no being the bound column. The drop down shows the workers last name, first name. When I choose a worker it always gives me a warning the the "Field can not be updated" but after i hit okay it stores everything in the correct field. Why am I getting this error? I tried using just my query and it works perfectly but when I try to use my form is where i run into problems. If you need more info let me know.
Thanks, :confused: