I'm trying to add records to a junction table based on additional record added to one table via form in the many to many relationship. Junction table = ClientAssociation Main table = ClientMain (qry_NewClientNullAssociation identifies new record added) Many to many table = Associations
I'm trying to do this upon clicking a command button that then opens ClientAssociation based on Client_ID. Then allows user to select mutliple associations (Asoc_ID) for Client_ID.
Here's what I have - I'm new at this...
Private Sub ClickAssociations_Click() On Error GoTo Err_ClickAssociations_Click
'Open recordset with new client records Dim db As Database Set db = CurrentDb Dim recClient As DAO.Recordset Dim strClientID As String
Set recClient = db.OpenRecordset("qry_NewClientNullAssociation", dbOpenDynaset) 'Loop through new client ids Do While Not recClient.EOF strClientID = ClientMain.Client_ID
Dim recAsoc As DAO.Recordset Dim strAsocID As String
Set recAsoc = db.OpenRecordset("Association", dbOpenDynaset) 'Loop through asoc ids Do While Not recAsoc.EOF strAsocID = Asoc_ID
'Open junction table and update records Dim recClientAsoc As DAO.Recordset
Set recClientAsoc = db.OpenRecordset("ClientAssociation", dbOpenDynaset) With recClientAsoc .AddNew !Client_ID = strClientID !Asoc_ID = strAsocID !CheckBox = False .Update End With
recAsoc.MoveNext Loop
recClient.MoveNext Loop
Dim stDocName As String Dim stLinkCriteria As String
While I was trying to test one of my forms (which was NOT working and I had to totally redesign it), I somehow created a bunch of stores in my Store Information table...some with no names, some with the first letter of an existing store, and there was even a duplicate of the store which is creating a problem now because some products exist in Store 1A and others exist in Store 1B.
I've created a query to identify the wayward products and am changing those. I'll delete that store then.
My issue is...can I set it so that the ONLY place a new store can be created is in the Store Information Input form? That if someone makes a typo when entering products, it doesn't create a new store?
I have a table that collects training information for our company. Some training is limited to one or two employees and that is easily entered by using a form linked to the table. However, some training is mandatory, company-wide training that all employees must attend. How can I add a record for each employee reflecting this training most efficiently? Is there a way to do a mass update?
I'm not sure if there is an easy way to do this...... Maybe someone has run into this problem. Have a DB.....Did a split...BE is on the server "F" drive.. fe is on stations. I retained a full copy of the DB NOT split. I still wanted to expand it. So now I have problems by working this way..I have not altered the tables that are on the "F" drive.. But I have added more tables to my unplit version. I need to copy the tables and info from the BE on the server BACK to my full DB. Then split it again. IS there an easier way??????????? Thanks
I made a little Access app for a friend, to do his invoices. Access wouldn't allow me to copy the db to a CD, but advised me to convert it to a Master. I followed all the instructions (honest!). Afterwards, I could burn his db to a CD and, on his computer, it opened but wouldn't allow edits or new records - not terribly helpful for an invoicing program :eek:
Since then I've tried creating a new admin in his name, re-setting all permissions, blah blah ... Basically, it looks like there is loads of help on securing a database but very little on un-securing it enough to be transferred to another PC!
The dang thing persists in allowing everything except edits & additions :confused:
I can't find out how to convert it back from a Master to ... what, a slave? :p Even so, that wouldn't be the answer coz I'd be back at square1.
I use a PC running XP and Office 2003. My invoice-less friend has a PC with Win[NT]2000 and Office 2000. I converted the database to Access 2000. I am way out my depth here, but hoping you Access swimmers will be able to advise :cool:
I want to have a dropdown box in a form but allow additions to be made, but after they are made, instead of them not showing up in the box like usual, I want them to become a part of the list. Plz help! Thanks
can anyone tell me how to stop additions and edits on a particular record in a form, plus the subform that the main form contains, im trying to put the event behind the change of a text box, shipped date, so when its changed you cant add or edit the record which is the order.
I have been searching and can't seem to find how to solve my problem. I have a sub form that allows adding new rows to a database. The detail contains a combobox and a few text fields. When I enter data everything works fine. However when I accidently tab into the next row (new blank row) and then try to get out of the form without adding a new row I get an error about "Index or Primary Key cannot be null" or something like that.
It is very frustrating because I can't exit the form until I add data to the new row and then leave without tabbing to much (thus creating another new row with no data).
How do I trap the new row and only allow the insert if it has data in it?
I have a form which acts as a search function for users looking to amend existing records. This is based on a query, where users enter a parameter (PolicyNumber) and this returns only the records which match the PolicyNumber in a form view. I have also added basic navigation buttons to move between all records with the same policy number. I have a problem in that I can't disable the option to add a new record in this form once people navigate to the end of the records.
The user clicks a 'search' button, manually inputs the policy number, and the records are returned in form view. The code for the button is as follows:
How I can disable the addition of records in this? Also as a sidenote if no records are found I would like it to display a MsgBox, but despite trawling the internet have been unable to find a solution I could get to work.
I've got a database that allows all users to review an address register. If they find errors or mostly get updated information they have another form they can fill out to make the recommended changes. Only I have access to the main DB to make the change permanent.
The question I have is there a way for Access to send me a notification that someone has requested the change?
I know that sending an email is doable - in Access. However, that's not an option for our network here. I was thinking something along the command prompt "Net Send" command. However I've not been able to get that to work by itself - let alone within Access...
I have used VB code in the past to store what PC is using the database, I could modify that to alert me whenever I open the DB up from my PC. But I was trying to get away from having to open this particular DB up every day and check for changes.
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code: SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next FROM calls WHERE (((calls.firm_id)=[firms].[id])) ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
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.
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
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?
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
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?
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
