I am trying to learn Access form building by creating a training database. I created a form that has all of my class information and the students who are attending the class as show in the attached jpg.
If a student has already attended a previous class I don't want to enter his information again. I want to do a lookup for the student and if the student does not exist then create a new student. Is there a way of doing this without coding?
I want to be able to choose a record from the combo box and then the rest of the fields fill in themselves... Also some of the fields are in a subform but are all linked to the entry in the combo box... can someone help me ? :confused:
I have a sales form that contains a combo box lookup to tblCUSTOMERS. It allows me to select a previous customer if that person is already entered into the system. If the person is NOT in the system, a form pops up to add the customer into the table.
What I need to know is, if the person IS already in the table BUT their address etc. has changed, what is the best way to get to that record to edit it? I'm THINKING a button with a command to go to that customer's profile.
What is the code to open a form to the customer selected in the drop down.
Hi, I have a form called Edit Column, which contains two text boxes: ID and Description. The user types something in these two boxes and presses a button, and this button should take the value of ID and edit description for that particular ID in the table "Map". Here is the code I have written for it:
Dim d As Database Set d = CurrentDb Dim r As Recordset Set r = d.OpenRecordset("Map")
r.MoveFirst If r! = Me.Text1 Then [I]('Text1 is the text box where the user enters an ID) Do While Not r.EOF r.Edit r![Description] = Me.Text3 r.Update r.MoveNext Loop MsgBox "Successful", vbInformation Else MsgBox "This column does not exist in the Store", vbCritical End If
I've created an ADP project containingnumerous tables. One (and only one!) table in the set will not open in edit mode while in Access. Here are the particulars:
1) The table was created from scratch in SQL Enterprise Manager, 2) The table is editable in SQL Enterprise Manager, 3) The table permissions appear to be set correctly (and are the same as all the other tables which all allow editting in Access) 4) I open the Access project and double-click the table. The "add record" navigation button is disabled, and any attempt to edit a field elicits a "This recordset is not updatalbe" error. 5) However, I can open the table in Design mode in Access, make changes, and save them.
I am trying to edit a specific record in my table. I want to know how to go about referencing it. I'm dealing with a table of books each having its own Book # assigned to it.
Example: The table has Book#(primary key), title, author, quantity.
How would i go about increasing/decreasing the the quantity by a value(i.e. 1, 2, 3, etc.) in a form that i'm in and then saving it to that specific books quantity? I'm attempting to keep track of our inventory.
My research has shown that Access will not allow edits through a form that 1) has subqueries in its SELECT clause, or 2) uses aggregation such as First(). Is there any tricky way around this?
Long version of question:
I have a database form that shows an overview of orders for products (it's based on a query that pulls all current orders from the big table). I would like to handle both of these on each row:
Show where we built it last time, and Allow the user to select where it will be built this time.
The problem is that I cannot figure a way to change the form/query such that it doesn't aggregate to find out where we built it last time, or doesn't rely on subqueries that do just that.
But I feel like there should be some way to do it--since each row displayed does rely on one and only one record in the complete table of orders. Which obviously is the record I want to update.
Is there something I can do that will accomplish this? I really do not want to have to make the user open up another form to see last time or choose this time.
We are using access 97 and I have a linked table to sql 2000. A couple of weeks ago, our sql server crashed and we had to completely rebuild it / restore all of the data. Ever since then, only one of our linked sql tables in access is acting up. Whenever we go to edit a line (sometimes, not always), we get an error which states the following:
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
Options are Save Record (greyed out), Copy to Clipboard, and Drop Changes.
About all I have tried is re-linking the table...any other ideas?
I have been developing a form that will allow me to input, edit and delete data in a table.I have the input, delete, clear, and close command buttons working ok, but I am having trouble getting the edit function to work.
What happens is that I highlight the line in the table (shown on the form as a sub form) click the edit command button, and the field are populated with the current data. I then edit the field that I want, and the add, button, changes to Update. When I go to update I keep getting an error message "Cannot update 'ID'; field not updatable.Below is the code I am using, when I go to the debug the arrow points to the "WHERE ID=" line.
Private Sub cmdAdd_Click() 'when we click on button Add there are two options '1. for insert '2. for update If Me.txtID.Tag & "" = "" Then
I have a report that I run which has PT_Ins_ID in it, I am trying to edit that row with the following code.
Code: Private Sub cmd_Deact_Click() Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("dbo_Patient_Insurance") If rs.RecordCount <> 0 Then With rs
[code]....
I've also tried this.
Code: Dim rs_PI As DAO.Recordset Set rs_PI = CurrentDb.OpenRecordset("dbo_Patient_Insurance")
I have a database that has 2 tables. Table A and Table B. Table A is my primary table. On this table I have 2 fields. The first field is a LOOKUP Field that looks up information from Table B and displays my selection in the field on Table A. Then using DLOOKUP I automatically input the information in the Second Field on Table A based upon the selection from the First Field.
This is working mostly correctly. However, the problem is, when I click on the next record in the table, it automatically changes the Second Field on that record to the same value as the record before it and continues this trend each time I click on another record. This occurs without me making a selection in the first field. If I make a selection in the first field it does change the Second Field to the Correct Value, but then the next Record has the same issue.
How do I go about fixing this so it doesn't change the value with the change of the record. Only change if I change that particular field within that 1 record?is there a way to restrict the Value's in my lookup field to only include the Values from Table B that aren't already in Table A?
I want an append query to add records to one of several tables, depending upon the value of a variable entered into a form. This variable is also used to select the appropriate records. The query is invoked from the visual basic code behind the form.
Can anyone tell me how to edit the destination table from within the visual basic?
Hi I have a form, which displays data from a query recordsource. One of the textboxes is a "Comments" textbox, which I would like the user to write in directly, which in turn is a direct link to the data in the table so it is updated immediately.
At the moment, the textbox does not seem to be editable. Is there a standard way to do something like this ?
While the form itself is bound to tbl_Orders I decided to use unbound text boxes to enter the data (knowing I was in for some work!). Reason is that if textboxes were bound would need to use a subform with multiple combo boxes and that UI did not seem suitable for the application.
I’ve completed the code to add new records to tbl_Orders and tbl_Junction from the unbound textboxes. I'm using the VBA .AddNew method and is working well. Now I need to integrate code that will allow users to edit existing records while they are viewing them on the form.
I have the code to look up the record set I want to edit in tbl_Junction and am familiar with the .Edit method but the issue is that the required changes MAY include not just editing existing records but also deleting and adding new records. For example, the initial order may have been for Apples and Oranges and the revised order may change the number of Apples, eliminate Oranges entirely, and add some Bananas. So, it seems I need to .Edit Apples, .Delete Oranges, and .AddNew Bananas?
Should I try to write code to determine where I need to .Edit/.AddNew/.Delete? -- not looking forward to that. Thought maybe could delete the entire junction record set and then just .AddNew for everything per the revised order. That should work as the display on the form would be correct for the whole order. But maybe would cause a problem as would remove the FK in tbl_Junction that corresponds to the PK in tbl_Orders?
I have a commitment table in my database, and normally people will input commitments through a form.
However each month I will have a flat file upload from excel directly into the Commitment Table, these will be up to 1500 lines. (they are downloads from other systems, that we want to have in our database and i can't link directly to these systems).
I created a macro to do this, and attached it to a button and tested it and it works fine.
My issue is this, once I've uploaded it once, next month what I would like to happen is when I upload it again that it changes the original commitment should any of the details be different or it inserts a new commitment if it didn't already exist. (After I have uploaded the original data, I can download it to get the commitment ID to attach to the excel files if necessary.)
I have a Union query that is linked into about 10 different excel spreadsheets on our drive and it pulls back only 3 columns of data (Document Number, Date Registered and Date Implemented). I then have another report which refers to the results of this query and does its own thing from thereon.
Here is a sample of the SQL:
SELECT CINT(RIGHT(RT_01000.[Rev-Trac Number],5)) as [Document Number], RT_01000.[CC_RT Issue Date] as [Date Registered], RT_01000.[Actual Delivery Date] as [Date Implemented] FROM RT_01000;
UNION SELECT CINT(RIGHT(CC_RT_01000.[Rev-Trac Number],5)) as [Document Number], CC_RT_01000.[CC_RT Issue Date] as [Date Registered], CC_RT_01000.[Actual Delivery Date] as [Date Implemented] FROM CC_RT_01000;
My Issue:
As it is above, the query will run, but we end up with duplicate numbers in the "Document Number". It only brings back the final 5 digits from each of these files and ignores the differenciating part of the number, the "CC_RT_" or the "RT_". This causes huge issues in the file which then goes onto use these results afterwards.
My Plan: (failed)
I thought I could simply update the "5" to an "11" to catch anything named "RT_00001" and also "CC_RT_00001" serperately so I can clearly see which is which without any duplicates. However, I recieve the below error when doing so:
"Data type mismatch in criteria expression".
I've been playing with this for hours and unfortunately many many reports reply on these results being correct. It is also not possible to change the names of the RT and CC_RT files that it links into.
I have a table "Product" and in this table I have two fields "StoragePlaceID1" and "StoragePlaceID2". Both these fields link to the look-up table "StoragePlace"
*Attached Image "Product_StoragePlace" from the Access Relationship Window"*
When I want to add a new Product from my inter face i get the error you can see in the Attached Image "Save_Error".I think the problem is that the Relationship is defined as One-to-Many,there a way to define the relationship as Zero-to-Many.
When I have a 3rd table looking at the row source of the 2nd table, which is looking at the row source of the 1st table. I only get numbers.
Everything I have read so far points to using a query as a solution. However, the query I made is not updating the information from the 2nd table.
I have attached a few examples.
Attachment 6247
this below shows. I changed "Bakersfield-test1" in the original table. However, the query I made does not update, still showing "Bakersfield-test". When you click on the drop down, it shows the updated "Bakersfield-test1", but it does not repopulate my column with the updated info from the table.
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'm creating a form that has combo boxes pertaining to each of the following fields from table "Documents": DocumentID (primary key), DocumentTitle, DocumentAuthor, and DocumentYear. No combo box is used with any priority over the others. The working form will allow a user to retrieve the full document record (data for all fields) by using any combo box they want, as well as any combination of combo boxes. This means that if a selection is made in one field's combo box, the drop-down lists in the other boxes need to update based on that preliminary selection. The filtered results for each field, based on any and all combo box selections, are always shown in a single datasheet on the form.
The kicker is that when a user starts filtering records by making selections from the drop down list in a combo box, but then decides to TYPE in another field's combo box, I want the combo box they typed in to do two things: 1) filter the records for that field based on what they typed, if any records meet that criteria (e.g. they typed "B" so only records beginning with B are shown), and 2) if no records match the typed criteria, the search starts all over (at the top of the cascade), filtering all library records based only on what they typed in the most recent combo box.
Does that make sense? Any tips welcomed.
I don't know how to cascade combo boxes in this way.
I have ODBC linked tables to a DB2 database.When I try to edit the values in some of the tables (open the table in dataview and edit one record) I get a 3021 Error -No current record.On other tables (linked the same way) I don't get this error
If I try the update by running a query on the same table, I get no errors.
The system I have is a german Access 2010 on german WinXP and a connection to DB2 LUW v9.7
Is there any set of settings necessary for ODBC to work with access and vice versa?
I have a small database where we enter audit log data. In my add add screen from the switchboard, one of the fields is the user the audit is assigned to. So that our group wouldn't be typing their names a bunch of different ways I created a reference table called "Ref Data 005 - Contact Name". So i pre-populate the Contact Names such that they will be more consistent.
The add works perfectly. I built all my screens using the forms wizards.
Now for the modify routine. If the user wants to go in an modify one of the other data elements, I force them in the wizard to do this modify by choosing thier name. Therefore, when i built this screen using the wizard I reference the "ref table" mentioned above. I then throw open another form displaying all of the fields based on the records that it finds for that user when they choose the name from the Ref data 005 table. Hope that makes sense. Ie.. they choose the records they want to possibly modify by first choosing their name from the ref table.
It correctly pulls the records for whatever user you choose. However, when you make the update and save, it is changing the Ref table name as well.
So for example if three users were in my ref as : bill joe john
And you launch the form, lets say joe shows up in the list, when i change it to bill to retrieve bill's records , it will do that but when i make the change, then Joe is changed to bill in the reference table.
I have a form to add, edit, and delete Records from a table. I am using the following VBA
Private Sub cmdEdit_Click() If Not (Me.frmlEmpDetailsSub.Form.Recordset.EOF And Me.frmlEmpDetailsSub.Form.Recordset.BOF) Then With Me.frmlEmpDetailsSub.Form.Recordset Me.txtAddEditname = .Fields("Name") Me.cboRoster = .Fields("Roster") Me.cboPermFctn = .Fields("PermFctn") End With End If End Sub
Instead of referring to the Subform to load the data i would like to refer to a combobox:
cboSearchName
Its not an issue but This Combobox contains 5 columns...
