I have a training database that I have set up and utilize to track attendee data and who is registered to which course.I have a course registration table that has a course title field which is a drop down list populated by course titles from the courses table, and a trainee id field.
For whatever reason, when I view reports or queries that draw from this table, access will make changes to the first record in the course registration table course title field i.e. it will take whoever is in the first record, and change the course they were registered to.
Currently I keep getting this error: "You cannot add or change a record because a related record is required in table"..My current tables are this:
Primary Table with persons info:
Primary Key - Auto number generated Name Address Email Phone
I have 4 other tables with use check boxes.
ex:
Table 1 - Geographic locations visited
ID - Auto generated USA CANADA ASIA ECT...
Table 2 - Languages Spoken ID - Auto generated Spanish Chinese English
Table 3 - Skills ID - Auto generated Hunting Dance Singing Weaving
Is this not a genuine one-to-one relationship table? I mean No two people would have had visited the same places and speak the same language no? I tried to create a one to one relationship with the primary key to the auto generated ID of the child tables but I'm sure that is not how you do it. Also when I try to save the check boxes in my form and I close it and come back it doesnt save and is blank again. Is it because my form gets its information from a query that takes all the information from all the tables.
how I can get this to work properly? Am i to make use of a foreign key? I've read a lot about it online and watched youtube videos but I dont see why I need it here in this case. Is there a way to set the IDs in the child tables to be the ones from the primary table? Or do I have to use a foreign key and manually input the primary ID into them?
Or would it be better to have all these child tables in the primary table and have one large table instead? I just didnt do that because one of them has like 20 checkboxes with cities and locations
I have a query that allows the user to see how many hours we've flown, sorted by month for the current year.
Problem is that in the query I have to manually put in the criteria "january 2007" or "february 2007" or etc....
What I would like it to be able to do is based on the current year (2007 for instance) set the criteria by itself. Someday I might not be around to reset the year in the criteria so therefore it should be automated.
Anybody have an idea what I'm talking about and a simple solution a hack of a programmer could figure out?
When I create a new control, or change the location of an existing one, the tab order index is changed for almost all the controls across the form.
I have many controls in this form. The controls are a combination of textfields and comboboxes. I have carefully selected the tab index for each control manually, and the user can now tabulate through the fields in the correct order. If I later change the position of just one control, or add a new one, all the manually set tab indexes for the controls automatically change so the order is horizontal across the form.
How do I stop the automatic change of tab order index? It is frustrating to manually set this for all fields, when just a small change is needed.
I am creating a database that tracks the selling of products amongst other things.
The user will enter in an order and may delay invoicing until the customer approves the quote - at which time the order is turned into an invoice.
In the transactions table the OrderID, ProductID and CustomerID constitute a composite key.
I want to be able to view the order and change it by either deleting or adding ordered items (obviously prior to invoicing) but because the ProductID is part of the composite key I cannot delete a line item.
Perhaps the solution is to remove ProductID from the index or is there a better way?
I have linked tables from SQL Server using ODBC connection that their location never changes. I have used certain fields of those tables to create queries and make table queries to derive to the information I needed.
On these tables on SQL Server, there is new data added daily. Every day, midnight, there's new data records added of whatever transactions took place in that working day. how often do I need to refresh linked tables in this case to get the latest data added. I mean, once I am linked, the make table query using those defined fields, would it get the latest data added by default when the query is executed, or I must refresh linked tables using Linked Table Manager and then run make table query.
Also, if I want the access to automatically refresh linked tables, can I use the following code? I have added this code, and executing it through a button, but I don't see anything happen, the database becomes inactive for couple seconds (I guess while it is updating) but I don't know is it updating the tables for sure or not, though I am not receiving any error when executing the code through the button.
Function RefreshLinkedTables() Dim tdf As TableDef For Each tdf In CurrentDb.TableDefs If Len(tdf.Connect) > 0 Then tdf.RefreshLink End If Next td End Function
I have been trying to improve a form used to add/edit names in a mailing list. What I want to do is check the database for possible duplicate names and alert the user to the fact that their new person may already be in the db.
I used the following VBA code in the After Update event as follows:
Private Sub txtLastName_AfterUpdate()
On Error Resume Next Dim strLastName As String Dim NbrNames As Variant Dim strNameSQL As String
txtLastName = Trim(txtLastName) If Not Mid(txtLastName, 1, 1) = "=" Then txtLastName = StrConv(txtLastName, vbProperCase) Else txtLastName = Mid(txtLastName, 2, 999) End If
' Test for duplicate last name in data base NbrNames = DCount("[tblMemberListings].[mlLastName]", "tblMemberListings", "[tblMemberListings].[mlLastName]= '" & txtLastName & " '") If NbrNames > 0 Then strNameSQL = "SELECT ALL tblMemberListings.mlID, tblMemberListings.mlLastName, tblMemberListings.mlFirstName, tblMemberListings.mlSpouseSO, tblMemberListings.mlAddress " & _ "FROM tblMemberListings " & _ "WHERE ((tblMemberListings.mlLastName) LIKE '" & txtLastName & "*') " & _ "ORDER BY tblMemberListings.mlLastName, tblMemberListings.mlFirstName, tblMemberListings.mlSpouseSO;" lstDuplicateNames.RowSource = strNameSQL Beep lstDuplicateNames.Visible = True
End If
End Sub
This generates a useful list of potential persons already in the db. The user then has the choice of continuing to add the new name and address or they should be able to select one of the names in the listbox and refresh the form with that person's data for possible modification.
I currently have these two Event Procedures in place:
Private Sub lstDuplicateNames_AfterUpdate() On Error Resume Next 'MsgBox "Sub lstDuplicateNames_Click value = " & lstDuplicateNames.Column(0)
If Not IsNull(lstDuplicateNames.Column(0)) Then ' User chooses and existing record Me.RecordsetClone.FindFirst "[mlID] = " & lstDuplicateNames.Column(0) If Not Me.RecordsetClone.NoMatch Then Me.Bookmark = Me.RecordsetClone.Bookmark End If Me.lstDuplicateNames.Visible = False Me.Refresh End If
End Sub
and
Private Sub lstDuplicateNames_Click() Me.txtHidden.SetFocus Me.lstDuplicateNames.Visible = False
End Sub
The problem is that when one clicks on one of the listed names just two things happen:
1 - a fine dotted box outline appears around the *first* record in the listbox 2 - the MS Exclamation wav sound is generated each time the mouse button is clicked. :confused:
Short of enrolling in a local junior college that offers Access 101 what can I do to get this addition working? I'm also not sure that my code to refresh the form is going to work, but right now I don't ever get there. ;)
Oh, this problem is occurring in MS Access 2000.
Any help would be greatly appreciated by this NOOB.
I have programed an access aplication that downloads some data from ORACLE, to do so it used some linked tables, my problem is that the first time that the user runs the 'insert into local_table (colum_1) select column_1 fom linked_table' access displays the 'Microsoft ODBC for ORACLE Connect' form, I'd like to control this conection programatically and not to show this window. ¿can this be done?
I've created a report which i'm exporting to PDF,but when I open the PDF I can see that some characters are replaced by a question-mark in a box.
for example : RMA Number: 5002408...here the empty characters after number and the : are replaced by a questionmark in a box...if I export to other programs i don't have this problem..
I have a huge problem, I have a form where there are companys with different contact-persons. I sometimes need to add contact-persons for that company without typing adress and those kind of things, so i created a copy record button, But now i want the following: If i copy a record (using the button) i want it to sort on [company] automaticly
Is this Possible, And if so Can some1 tell me hoe to do this
Hi all, We have an Access 2000 db on a server that I can open. Problem is, when other users open it up and use the Navigation buttons to say, view the next record, Access crashes with the following error: "Method 'Requery' of object '_Subform' failed." (this is the Access supplied record navigation functionality - I havent customised it in any way)
Other users have been using it before with no problem (upto last week). I can still open the db and do not have any errors... So, it appears to be a user related problem.
Any suggestions or things I should check out, please?
I have a number of records in my database and I use a form to input information, update fields etc. One of these fields is a ratio. I would like to create another field, in the record, input from the form, that automatically generate the ranking of the record in the database using the ratio, in descending (or ascending) order. So if I have 1000 records, the field would return, right after I update the ratio, which ranking within these 1000 it now holds.
Is this possible? In the event two (or more) records share the same ratio, what happens?
I would like access to automatically create a record in the contracts table when I change the status in one of the Customer Fields from prospective to Customer.
The two tables are linked using the Customer ID field.
Currently I'm working on an invoicing database in which I can register my customers (or partners) and also make invoices and purchase orders. So far, it does exactly what I want it to do, but I'm struggling with the invoice numbers. What I want to have is that whenever I make a new record, the next invoice number in sequence is automatically assigned to it. Another desire is that this number is prefixed with the current year, and the counter should reset with every new year. So, for example:
2015001 2015002 2015003 etc.
Then
2016001 2016002 etc.
Don't worry about the maximum of 999 invoices, because I won't ever go beyond that limit.
I have a quick question on how to enter the current date in a record automatically when the record is created.
Here is the situation. Say I have a Products table that lists the products that have come into the inventory. There is a field that denotes the day the product was introduced into the inventory. So ideally when the user creates a new record for the product, I would like this field to be populated automatically with the actual date that this filed was created by the user. Note that in future the user may edit the product (i.e other fields in this record), but the entry_date field should not be altered from what it was the first time is was created.
Is there a way I can do this? Any help much appreciated. thanks.
I would like that when the user opens the form and adds a new record the combobox showed the row immediately following the condition defined in the previous record.
For example.
First time that I open the form I select the combobox the condition A. Second time that I open the form the combobox automatically selects the condition B Third time that I open the form then the combobox automatically show me the condition 3
I use Access 2013. Is there an easy way to do the following: I have a contract that starts on eg 01/07/2015. Tenant has to pay 100 each month. Is there a way to create a query/table/... where access automatically makes a due date? EG: Joe needs to pay me 100 each month, starting 01/01/2015 until 31/12/2018.
This means:
01/01/2015 - due 100 from Joe 01/02/2015 - due 100 from Joe ... 01/12/2018 - due 100 from Joe
how to automatically populate a certain field. To add some context, I have a form which registers the details of a contact with standard information of contact details. There is a subform which shows the different products that the client from the main form is interested in. This is a actually a data sheet which returns the results of a query (selecting from the relevant table the client in question and the products he/she wants).
I have added a button which opens up another form and allows a product (and hence a new record) to be added for that particular client. I would like that the form automatically populates one of the fields in the form that is the client id. Given that the subform is opened from a form which already identifies the client, how do I do this?
I am working on school project. I created priority table that has primary key (ID number) and other data, and three tables(also with data) that are linked to this table, with ID number of priority table and primary keys of other tables (relations many-to-many, I formed tables between them).
Problem: - my solution is created so that I can fill all neccessary data of ALL tables in ONE FORM. But ofcourse Access wants you to enter at least one field in primary table for opening new record, so that It links you to all other tables (in my case with ID number) - then It is possible to enter data of all other tables in whatever tab order you like.
What I want to do is that my autonumber field from primary table would automatically open a new record number when opening the form, without entering any data to primary table. Is that even possible ?
So far I tried to add another field in my primary table (Date/time format) and set the date to =Now(), and used macro of Refresh on form, but nothing happens. Date is shown in field, but Autonumber doesn't generate new number of record. maybe I did it wrong...
Any option to lock Tab order and force user to fullfill data only in desiring order ? I want to make solution more user friendly
I am trying to come up with automatic line numbers for each specific order. So for example, I have job number 123456 that has ordered 3 items, what I would like is that item 1 has a field with a 1 in it automatically, and item 2 has a 2 in the field and so on. But the trick is that when order 123457 gets entered and has 5 items entered, I would like it to start over at 1 and go to 5. Does this make sense? Is it possible to do this in a table? Or can this happen on the form? That I assign a value to a field. I am using Access 2010, have been for a few years now, but I have lots to learn.
I'm currently building a bookings database and have encountered an alert message that I cant seem to rectify -
"You cannot add or change a record because a related record is required in the table 'Booking Details'"
Basically - I have a 'Customer Database' form that is linked (via command button) to a 'Booking Details' form. Within 'Booking Details' I have 2 sub forms - 'Booking Quote' and 'Booking Payments'. Both subforms are linked to the 'Booking Details' form by the 'booking ref' field with RI.
I have no problem updating information in the 'Booking Quote' subform, but when I try to add information to 'Booking Payments' it states the above message.
Can anyone please advise as to how I can prevent this happening? I'm slowly losing my mind....!!!
I have created three tables, all of them are connected by one-to-one relationship by same field, as you can see in the screenshot. and at the same time I have created three forms for each table. then I brought two forms in one remaining form. so whenever I entered data in first form and click on the next tab in which another form exist, it gives me this error: "you cannot add or change a record because a related record is in table".
I have a problem with my access form, it said "You cannot add or change a record because a related record is required in table". I have attached the access file.
