What I would like to do it take all 'RoleTitle' from RolesList, all 'CourseID' from courselist and show them as a pivot table/matrix with a checkbox that creates or deletes entries from the RoleRequirements table. So, if it exists in the RoleRequirements table then it should appear a checked, unchecking it would delete it from the table.
I am using access 2010 with a SQL server 2005 backend.
May be this is easy for your guys. I want to freeze or lock the mainform data fields while allowing users to input, edit, delete data on a subform. I tried to change mainform properties so it can not edit, add or delete data on mainform. By doing so, I cannot do anything with subform, it seem read only as well.
Thank you for help
Peter
I have a table that is a list of all of my events. Each record of events should have a child table that list all of the things that happened at the event. When a new record (event) is added how can I have a new child table created and linked to that record. Also I have a blank table to serve as a template for what each child should look like. How do I make sure this occurs? The child tables can have the same name as the index. I am just using numbers 1- for the index with 1 being the first event and so on?
View 8 Replies View RelatedI have a form with a sub-form and a sub-sub-form :p
If I try to delete the record being viewed in the sub-form using
With DoCmd
.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End With
Then I here a ding (like you do when you try to delete a record) but I see no record delete confirmation box come up, if I hit enter, it deletes. So its there, but I can't see it, it's not behind the form either.
Any ideas?
Cheers
hiya
i have a subform which won't allow me to delete a record unless i'm in the datasheet view. is there anyway to change this. the properties have been set to allow deletions.
thanks
trish
Hi,
The user wants all of the records in the database to have continuous numbering. I have used AutoNumber in the past but if you delete a record, it produces gaps in the numbering.
I was wondering if there is a way to number the records so they are always numbered continuously (1,2,3,etc.) even if some record is deleted.
I have tried using the following:
Set db = CurrentDb()
Set recordlist = db.Openrecordset("SELECT Max(tblContactHistory.ItemID) AS MaxID FROM tblContactHistory")
ItemID = (recordlist!MaxID + 1)
recordlist.Close
Set recordlist = Nothing
but I still cannot get the records to have continuous numbering after a record is deleted.
Any helped would be appreciated.
Thanks!
I want to create a custom sequence when deleting a record but I'm having a problem.
What I want to do is prompt the user for the reason he/she is deleting the record and then archive this so it can be viewed in a log at a later time. This extra step is not as easy as I thought it would be.
What I did was put some coding into the On Delete event of the form. The code opens a form with a text box. The user can enter the reason for the delete in the text box and click OK or click cancel to abort the deletion.
My problem here is opening another form from the On Delete event. As soon as the form is open, the code just passes to the next step of the sequence and deletes the record.
There are 2 approaches that I've thought of but I'm not happy with either.
Approach 1:
In the On Delete event, set Cancel to True. That way the deletion is not handled by the delete sequence but rather by the new form that opens. The problem is that deleting a record from another form is not that simple. What I've tried is to execute a SQL statement that grabs the ID of the record from the open form and delete it. The problem is that although the record is deleted, all the fields on the form say #Deleted in them which is not that pretty. (Maybe there's some other way to do the deletion without this problem that I'm not aware of...?)
Approach 2:
Use an Inputbox. Unlike a custom form, the Inputbox delays execution of the code so the code can determine from the results (i.e. should it cancel the delete if the user clicked Cancel). The problem with Inputboxes is that they are not very flexible in the layout, they look pretty "packaged" (i.e. they look to me like the programmer didn't bother creating a means to enter a value). There's no way to place the buttons where you want or have it do better validation (e.g. I want the FORM to check that there's at least a few words describing why the user is deleting the invoice rather than have the form with the record get the input message passed back and then give an error and then pop open the Inputbox again)
I think that especially when you're creating an application with anything financial, you'll want to log a deleted invoice including the date, amount, possibly the client's name, invoice number, reason for deletion and the login name of the person doing the deletion. All of these are easy to record other than the reason.
Help would be much appreciated.
SHADOW
I have a form and a subform. There are a number of buttons on the form, allowing me to add, update, delete, etc. records from the subform. The process is that I select a record on the subform, its details are displayed on the form, then clicking on <Delete>, for example, removes it.
Each of these processes work fine on their own and also if carried out one after the other, for the most part. The one problem I'm finding is when I delete a record.
Firstly, the subform appears to refresh and the record is no longer shown. However, if I then try to select another row from the subform, I get a message saying 'record is deleted'. I click on <OK> to remove this message and I can then select the same row or another row without any problems.
Secondly, if I now try to add a new record (the code behind the button is just 'DoCmd.GoToRecord , , acNewRec'), I get a message saying that I can't go to the selected record.
If I close and reopen the form between each activity, all works fine, suggesting that something isn't refreshing properly. I've tried putting 'Me.refresh' and 'Me.requery' immediately after running the deletion, but to no effect.
Any suggestions?
I'm using this stardard piece of code on a button that deletes a record from a simple continuous contact form.
Code:
If MsgBox("Do You Want To Delete This Record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
End If
If the form is called "CONTACTS" with a field called "Contact_Name", what code do I use to have the relevant Contact_Name appear in the MsgBox to be sure that I'm deleting the correct record ...... something like this .....
Do You Want To Delete The Record Called Paul Quinn ?
I have a table whose key is two fields. I am having no luck in selecting a given record from a form and deleting it. No problem in a table with a single field key.
View 14 Replies View RelatedWe need to reset the autonumber after the last record is deleted.
For example:
2006
2007
2008
if 2008 gets deleted, we want the autonumber to reset back to 2008 rather than move to 2009.
Is this possible? This is the code we are using but cannot get the string to recognize the variable. If the variable is replaced with a number, it works, but it defeats the purpose of adding the plus 1 to the last autonumber.
Code:
Private Sub Command0_Click()
Dim RLMax As Integer
Dim Statement As String
RLMax = DMax("[id]", "Table1")
RLMax = RLMax + 1
strSQL = "Alter table table1 Alter Column Id Autoincrement(RLMax,1)"
DoCmd.RunSQL strSQL
End Sub
I realize we don't want to depend on the autonumber for anything other than a row identifier, but the table is setup that it is important for the rows to be sequential if the last record is deleted. Only if the last record is deleted.
I have 2 tables.
1- customers table with 2 fields : customername,customerno
2-conversationstable with 4 fields: date,customername,customerno,details
The conversations table is for keeping memo of telephone conversations with the customers.
I built a simple form deriving from the conversations table. And added to it a combobox with 2 columns from customers table to select the customername an customerno for the form.
While deleting the record in the conversations table,through the form, I saw that the customername and customerno in the customers table of that particular customer record are deleted also. I made no links between both tables.
I wonder why that happened.
I have a form (frmHourEnter) with a combo box (Staff_ID) that lists staff members from a table (tblStaff). The table form (frmStaff) has a checkbox (ckEmploy) which indicates if a staff member is still employed. If the checkbox is checked how do I exclude that staff member from the combo box Staff_ID in the form frmHourEnter?
View 8 Replies View RelatedI just created a form using the form wizard. I have added records from both tables and queries and now the form will not allow me to add any new records. Is this because I've used both tables & queries?
View 5 Replies View Relatedi have a table..
staffdayid
date
timein
timeout
notes
i want to have a feature in my database like the following..
these are basically day to day clock in times for my staff..
is there a way that everday.. a new record will be created..
eg.. on 04/06/06... i turn on my pc for the first time.. and when i view the timesheet form.. a record for today is automatically created.. then throughout the rest of the day, this form will be displayed..
when i turn on my pc on 05/06/06, the database will know its a different day, so it creates a new record for today... then i can just move back and forth throughout the days to see the details..
if i then turn my computer off for 5 days, then turn it on at 10/06/06, it still should have made records for 6,7,8, and 9th June, but they will be empty, and the record im shown is for the 10/06/06,
how can i make this?
I been tasked with a project to be written in access which I am rather unfamiliar with, web design is more my area.
For part of the project I need to copy some pricing fields from a pricing table to a new record at the point that the new record is created. The new record is created when a 'new form' button is clicked.
What I would like to know is what and where I should trigger the copy query from and what function should I be using?
cheers
John
I have a subform with a lot of fields in which require input. I have set the tab order correctly so they go through in sequence. However, I have found when I tab out of the last field. Its creates a new record which I dont want.I want it to either return to the first field to just stop.
View 2 Replies View RelatedI have a problem that should have an easy solution. But I can't find it.
I need to have a user add a new record. (Created a form with the fields on it, No problem , so far.)
The problem is when the user is adding a new record if they hit the PAGE DOWN Key or the mouse scroll, they then go to a new NEW record. (And if they are not paying attention they now have two new records)
How do I prevent this.
Thanks
Mike Lester
i have a form that has a record source
Code:
SELECT tblClientDetails.FirstName, tblClientDetails.Surname,
tblClientDetails.MobileTelephoneNumber, tblClientDetails.SentTextMarketing
FROM (tblCategories INNER JOIN tblItems ON tblCategories.CategoriesID = tblItems.CategoriesID)
INNER JOIN ((tblClientDetails INNER JOIN tblOrders
[Code] .....
this does not allow edits on a continuous form. the edit i am trying to do is the check box. it will allow edits if the grouping is not on but if it is not on then i will get many duplicates of client details.
so how do i set it up to allow edits but still only get one instance of each client detail.
I have two tables; Customers and contracts.
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.
Is this possible?
My form with record source of query has stopped allowing edits.
I have reviewed property setting and do not understand what's going on.
I have a multi-select list box for selecting which faculty members apply to a project. The faculty table and project table are linked in a many-to-many relationship. I have the following code which should create entries in the link table:
Private Sub Command5_Click()
Dim varItm As Variant
rs.Open "tblFacultyLink", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
For Each varItm In lstFaculty.ItemsSelected
rs.AddNew
rs!FacultyID = Me.lstFaculty
rs!EntryID = Me.EntryID
rs.Update
Next varItm
rs.Close
Set rs = Nothing
End Sub
It successfully creates new records and enters the EntryID and LinkID (autonumber). However, FacultyID is always left blank. lstFaculty is the unbound list box which has three columns from the faculty table and is bound to FacultyID. Any ideas on why FacultyID isn't created in the link table (I get no error messages)?
Also, any ideas on how to prevent duplicate links being created every time the button is pushed? I was planning on having it first run a delete query for that EntryID in the link table so that it replaces the old links and any that have now been unselected are no longer linked. Comments on that idea?
Thanks again to everyone on the forums for your help.
Nutshell: My user DB has one menu with 4 tabs (2 for adding records, and 2 for searching/editing records). Since the form opens up in the acFormAdd mode, Access already creates a new record (blank) before they type anything. It is resulting in blank false new records that are getting added to the table and they are getting annoying. My audit table is full of "user X created new record at this time" when it's just a blank record.
There must be a way to prevent this using VBA, but I cannot find it.
I've seen ways to disable "add new record" buttons and such, but nothing that disables Access from being smarter than me and creating a new record before I decide to.
I think my question is fairly simple - fingers crossed.
I want to create a field that, whenever a record is added, it auto-adds the date & time the record was created. I'd call it Book_Date_Added, or something like that. I tried snooping around the Default Value options but couldn't figure it out.
The table exists and it already has some records, so I'd need to know how to add this field rather than how to create it when I create a table (although that would be helpful too, if it's somehow different).
I'd appreciate any help, thanks a bunch.
I have got problem with ms access report. I want to make a report which is based on
1) first master table
2) first slave table
3) second slave table
I have done some research and decided to do some form with subform. So I have got the view one record from master table and many record from slave tables in one view.
But it turned out that it has become duplicate records. (the relationship are ok - it duplicate master record as many as slave records)
So:
1) how i can do ms access report from multiple tables - one master record with multiple records form slaves tables
Basic premise:
I'd like to list all records in a table accompanied by a checkmark. If the checkmark is checked then this record will be edited (saved to a 'list' for editing). If not checked, then the record will not be edited (not saved to a list).
What I've done thus far:
I have a table with all the fields in it (pulled from a master table) and the checkmark associated with each record on a datasheet form. That works..I can select each record using the checkmark and use mod code to check if the checkmark is true or false.
But what I would like to allow is:
Filtering on a field and allow the checkmarks to be selected individually as well as a 'Select All' button. Thus, when the form is first opened - all records appear. The user then 'filters' for all Telephone Number field starting with area code '222'. With that new set of records, the user can then select each record individually by checking the checkmark box and select all records by clicking the 'Select All' button.
As an example: Lets say a company with telephone number 222-xxx-xxxx recently moved to a new area code. Instead of changing each record individually - I'd like to list them, filter them down to '222', then perform a 'global change' to all records.
Hope that makes sense,
Thanks,
Charles