Forms :: Multiple Record Input At Same Time In Form
Sep 15, 2013
We store data for clients which can be found online. Our current method of getting the data so we can analyze it is to copy a table found online and pasting it into excel.
What we want to do is to copy that data into an access table. This equates to inputting multiple records at the same time. There are 5 columns and around 20 rows (records) pasted at the same time. I want to do this in Access, through a form which would them store that data in the appropriate table.
I assume there is an ActiveX control out there which can put a spreadsheet in an access form. Then i could maybe write a VBA procedure to input the data into a table.
I am building a form to create a user record and at the same time i have some yes/no options which are located in other tables but when i want add a user i cannot select any yes/no options they seem locked?
I have 5 tables that I would like to input data in. It can only be done with a single form. The fields I want to input in have the same names in all 5 tables, for example:
Table 1: Name Age DOB
Table 2: Name Age DOB
Table 3: Name Age DOB
Table 4: Name Age DOB
Table 5: Name Age DOB
Is it possible to input data into all of these fields in each table using one textbox for each field?
Preferably without having to use code but if it cannot be done without it then that would be fine.
I have a table with 2 fields in it - Type and Pallet_Case - using a form the user enters the name of the Type and then from a combo box selects either Pallet or Case. There is a button on the form that is to save the new record to the table. (code below) The strangeness that is happening is that when the new record is added to the table, whatever is the first existing record alphabetically in the table is changed to the same Pallet_Case selection that was made for the new record.
I have a list box that displays on the form that is updated after a new record is added to show what records exist in the table (this is done by a query of the table and sorts on the type), so I can see this happening without having to open the table. It doesn't matter what sort order I put the list box in nor the table it still changes the first record alphabetically. And it is only change the Pallet-Case field. I even changed the first record to something else and it still did this.
Dim dbs As DAO.Database, strSql As String Set dbs = CurrentDb strSql = "Insert into [PP Type TBL] ([Prepack Type],[Pallet Case]) Values ('" & Me.Type & "','" & Me.Pallet_Case & "' )" dbs.Execute strSql, dbFailOnError
I am attempting to create a search form where a user can search by either employee name or company name. I have 5 tables to use. Is there a simple way of creating a search method for this? I would like to be able to have the user type in a name and click a button that says search with any records matching the search come up. However, I could definitely use a method where they type it in a box and it finds it as the person types.
How to search from all five tables at the same time.
I have a "Search" form that I am using to search through a query in a list box of all of my Customers. Once the record that I am wanting to view comes up I highlight it and click a "View" button to bring up another form with the customers full record. When this form is open I am able to update the customer information and add User's to it if necessary.
My problem comes after I click on my save and close button that takes me back to me search form. I want to close the search form and the error I get is "The record cannot be deleted or changed because table "tblUserProfile" includes related records." I click OK and get a SECOND error stating "You can't save this record at this time. MS Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?"
I have already made my changes and saved them in the form with the customer record so am not sure why I am getting an error when trying to close the Search form.
I have a barcode input form. When a barcode is scanned, the value of the barcode, along with a date/time stamp using the now() function (hh:mm:ss) is entered into a table in the database as 2 separate columns. I need to make sure that I don't have any 2 barcodes with exactly the same date/time. They must be at least 1 second apart. How can I easily make this happen? I can't control how many times the barcode actually gets scanned.
A little bit of context first: I am producing a database to store information on overhead line structure renewals.
I have a table called tblStrucTasks with four fields; Task ID, Structure Type, Create (specifies whether the structure is to be created or removed) and Task. I need to enter multiple records which will have the same Structure Type and Create fields but different Task fields. There are many tasks for each structure so I'd like a form where you only have to select the Structure Type and Create fields once. Some sort of list where the tasks could be typed in would then be perfect.
I'm not sure whether this should be done with VBA or queries. I'm new to databases so I don't know a lot about queries but I have a little bit of experience with VBA from Excel.
PROCESS AS DESIGNED: The user logs in and is given a form displaying existing records that are coded (by userid) for that user to update. From that list, the user selects a record and continues to a second form (the first form closes) which displays all the fields of the selected record which should/can be edited by the user (plus a few non-editable key fields). There is nothing tricky to how the records are selected for display. A simple query behind the first form selects data records using the userID number. The second form does a simple record select using the active record ID number on the first form. Nothing tricky -- no modules just queries, tables, forms and simple macros to string it all together.
The user can open/close these two forms as often as necessary and the record will continue to show up until a value is entered into a "completion date" field. When that field is no longer blank, the record will no longer be displayed for use.
PROBLEM: About 90% of the time, things work as expected. But 10-15% of the time, somewhere between opening the record in the second form and closing the second form, the edited record totally disappears (as if it were never opened, it isn't even system date stamped) and the input information is inserted instead into a brand new record.
Users can sometimes tell when the edit record is dropped during processing, because key fields displaying previously entered data go blank and the displayed recordID (autonumber) is changed. p> ERROR PATTERN: NONE! It can happen as the edit record is first opening, in the middle of editing the form, or at the end when closing the form. It will happen on a specific PC 2 times in a row and the third time be fine. It has happened at multiple locations but not for all people and not consistently for the same person/same pc. The same record that failed can be re-selected and work just fine the next time.
I cannot reproduce the error myself. I have tested everything I can think of without causing the error. I can lose changes and avoid a system date stamp if I abort my Access sesssion, but that doesn't create/insert a new record with my new input. It just loses everything.
I have wondered if a momentary break in the network/communications could break the connection to the application to just enough that the form remains on the screen, but selected record is discarded. Input into the empty form would/does insert a new record.
I have asked the IT Operations group to investigate if a network/communication problem could be the culprit, but they haven't gotten far on this. I have never seen this happen before and am totally stumped but am desperate to resolve it before user confidence is damaged.
I have a reservations form, which will be used to store the checkin and checkout dates for customers; these are related to the reservations table. On the same form I also want room details showing such as room number, this is on a separate rooms table. I believe that the room number will be a query to show which rooms are available and therefore display the room number on the form. So I need integrating this into the form. Also I have not set it as a subform, the reservations form carries on from the customers form, as a new form.
Each table has a corresponding form for data entry in each area. The tables were subdivided in this way in order to limit each department's ability to edit the data of other departments. The only field common to each table is the drug lot number, or "Lot #" (which is the primary key of each table).
I wanted to make it so that when Manufacturing enters a new lot number on frmMFGData, it automatically creates that lot number in the other 4 tables. This process mirrors our actual real world business process, where drugs are manufactured and assigned new lot numbers by our manufacturing team, and then other departments simply reference those numbers when doing their part.
To accomplish this, I went ahead and set up 1 to 1 relationships between the various tables using their "Lot #" fields, establishing referential integrity and enabling cascading updates. However, when I attempted to enter a new lot number into frmMFGData (the manufacturing form), it didn't seem to appear in any of the other tables. If I edit an existing lot number and change it to something else, the change does carry over to the other tables, so I know that the cascading updates are working in some capacity.
If cascading updates cannot "cascade" new records, then is there any other way to accomplish this?
On my main form, I have a subform to input multiple dates, using date picker.
I also have a text box on the main form to input single dates, again using Date Picker.
On entering both the single date text box and the subform, I don't want the (default) date to be visible. I only want the date to be visible once I have selected a date from the Date Picker.
I have figured out how to do this with the single date text box. I have simply defaulted the forecolor to be white so that you can't see the font against the white backcolor. Then on the Change Event, I set the forecolor to be black. Works like a charm.
However... This same approach will not work with the subform. I've tried playing around with a bunch of different events, but so far nothing allows me to re-create what I am able to do with the single date text box on the main form.
automatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
I have a form "frm_PatientNew" based on table "tbl_patients", this form contains a button "cmd_NewVisit" which is supposed to do the following: opens the form "frm_NewVisit" for recording a new visit for the last recorded patient in "tbl_Patients", I found many approaches depending on DMax and Dlookup and they worked fine just if "frm_NewVisit" is bound to "tbl_Patients", but "frm_NewVisit" is bound to "tbl_Main" which acts as a container for all information (patient data, visit data,service done and service provider), so the form "frm_NewVisit" contains fields from different tables. I wonder if I should create "frm_NewVisit" as unbound form, then adding fields from different tables to it and using vba to populate "tbl_Main",
Hi, I have a big problem. Here is the business rule, I am trying to track meeging request(MR) made. A meeting request is made by a manager,MR can include many analysts, and MR involves 1 project. I need to generate several reports from the MR information so I am trying to make sure the info is inputed correctly. I am trying to creat an input form for the MR table (tblMR) The input form is going to contain all the fields from the tblMR except the manager_ID and and the Project_ID, I am also going to have it track which analysts attended the meeting(1 or more). The problem I am having is in creating the form but having the analyst, manager, project values come from their own tables but after selecting them through combo box or list box, for values of Manager_ID and Project_ID to be entered in the tblMR. And for the analyst involved to be stored in a a composit table called tblMR-Analyst. this table will help me track how many meeting each analyst has been to. Any suggestions will help.
I tried and failed to get this to work using a multiselect listbox..I have a list of departments in tblFunctionalArea...My main table is tblStatic..I want to be able to for each record select multiple departments affected by a record and store them in the tblStatic.After looking around i couldn't find many people successfully maanging to store listbox values in a table...
I decided to create 5 fields in tblStatic and in my form create multiple combo boxes cboFunctionalArea1, cboFunctionalArea2 etc etc which are bound to these fields.I want to be able to ensure the list for any combo box requeries and takes out any selection in the other boxes.
I have this working in a strict cascade fashion i.e. in cbo1 all dept's visible, in cbo2 it takes off whatever was selected in cbo1 etc. But if someone then jumps back and deletes the content of cbo3 then the whole thing breaks or if they amend in the wrong order it breaks
When I learnt to use vba in excel (to basic level) I was always advised in a userform to validate data at end of record input i.e. when pressing enter and next. Is this true also with access or should i be putting validation rules at the afterupdate stage of a particular control?
I wish to change the backcolor of a field on a current record being dispalyed in a 'multiple items' form. However when using me.A.backcolor = makes refrence to all fields called A on the 'multiple items' form and therefore all fields change colour not just the current record.
What I have is a table that contains plant info (plants that produce a product not vegetation plants). I also have a table that contains all the fields related to the products themselves. Finally I have a third table that contains the constituent items that make up products (think raw ingredients if you will). I have various junction tables set up to define relationships between all these.
What I'm trying to do is create a way of inputting a product and having an option on the form to send that product information to multiple plants. I don't know why but I cannot visualize an approach to doing this.
I am using Access 2007 and have reviewed many blogs and looked at the Northwind db to try to solve this problem. I would like to create a form that functions in the same manner as the "Order Details" form in the Northwind db. Specifically, I want to create a form that will allow me to input a value and have the form get data from another table and populate the table I need populated.
I would like to populate a table "Outside", field names are Key, Name, term, Value, Date.
The table that contains the data is named "CV" and the filed names are Key, Name.
In the form, I would like to enter a value for Key into a combo box, and have it put the value for Key and for Name into the form, and allow me to enter whatever I want to in the Term, Value and Date field. When I tab out, I would like all this data to be in the table "Outside.
I would like to know what the best way to input a duplicate record is with the exception of 1 field that is used to correspond with the proper parent of the record?
Here is an example of the problem, We receive a letter with pertinent information that corresponds to 30 of our facilities. I would need to enter this 30 times so that when the users of this information run their reports for their facility I can ensure that the letter would be referenced.
How to limit input data on the same month for each patient? The field is a data/text box on a subform
I found this code on Form Current Event!:
Code: Private Sub Form_Current() Dim intMaxNumRecs as Integer intMaxNumRecs = 5 'Max Number of Records to Allow If Me.NewRecord Then With Me.RecordsetClone If .RecordCount > 0 Then .MoveLast: .MoveFirst If .RecordCount >= intMaxNumRecs Then MsgBox "Can't add more than " & intMaxNumRecs & " records in the demo database!" .MoveLast Me.Bookmark = .Bookmark End If End If End With End If End Sub
I wan to build a form with a blank text box (txtbx1) where users can enter a part description, this will search records in Table1>part and return a list of parts that match the textbox input.
Here's what I've done -
txtbx1 - property sheet - control source - part event - on enter - ...
I've built an expression -
[Table1]![part] = [txtbx1]
hoping that this would match records 1n Table1 to txtbx1
I have been editing the service call management database that comes with office XP.
I have a table called "Workorders" with fields: WorkorderID (primary key) CustomerID
And another table called "Workorders by Customer" with fields CustomerID (primary key)
I want to be able to have an input form where I input a workorderID which then opens the "Workorders by customer form" by customerID which relates to the WorkorderID in "Workorders" which is typed in the input box.
Im only a novice and its the code part that im struggling with.
I'm a complete newbie at Access. A friend (she's the Secretary of our small town's Service District Board of Trustees) asked me to develop a database for keeping records of property owners here, to facilitate such things as sending the yearly fee letter, tracking mailing addresses and property addresses, whether the fee is paid, etc.
I figured out most of it by "taking apart" a database she uses for another organization. It wasn't easy, but it's all working EXCEPT the thing she uses most: a last name input form (a combo box entry field with a command button) which is supposed to bring up the appropriate "Edit Existing Owner" form by the last name entered (or give an error message if there's no owner by that name, of course). The form works - but simply brings up the last-entered record.
I've looked at the setup in the other database, I see that when I create this form relationship, the VB code is nowhere close to what's in the other database. Trying to use the code from the other database is ineffective, even though the relationship is exactly the same. I'd be happy to have someone look at the forms etc. but the database compressed to a .rar file is 818k which is pretty big - and I'm not sure how to provide just the parts which aren't working.
I can upload the database to webspace and direct someone there if they'd be willing to take a look....
