Create New Record From Multiple Controls
Aug 8, 2007
Hello,
I have two controls on my form that I want to reference in an append query to create a new record. The first is a text box, the second is a combobox that is populated with all of the dates associated with the text box. The selection of a date updates a subform based off of a hidden column (chart ID, an autonumber field based on combinations of record numbers and visit dates) in the same combobox.
I would like the user to be able to enter a new date and create a new record that would contain the next autonumber in association with the new date and the record number from the text box control. I have tried using an append query with the following SQL, but I consistently get errors referring to "type mismatch." Any help would be greatly appreciated; thanks in advance.
Code:Private Sub cboDOVSearchChart_NotInList(NewData As String, Response AsInteger)Dim ans As Variantans = MsgBox("The date you entered was not found. Do you want to add a newdate?", _vbYesNo, "Add New Date?")If ans = vbNo ThenResponse = acDataErrContinueMe.cboDOVSearchChart = NullDoCmd.GoToControl "cboDOVSearchChart"GoTo exit_itEnd If' add dateIf ans = vbYes Then Dim strSQL As String strSQL = "INSERT INTO Patient_Visits ([Medical Record Number], " _ & "[Date of Visit]) " _ & "Values (" & Me.[txtChartMR] & ", " _ & "#" & CDate(NewData) & "#)" 'this line has the arrow on debug CurrentDb.Execute strSQL Me.cboDOVSearchChart.Requery Me.cboDOVSearchChart = NewData Call cboDOVSearchChart_AfterUpdateEnd Ifexit_it:End Sub
View Replies
ADVERTISEMENT
Oct 8, 2014
I have two tables which are in a one-to-many relationship (the example I am dealing with is a mother and her children).
I want one form for the mother, with fields such as date of birth, and when you click Add Record I want the mother table to be updated with the new record.
However on this form I also want a field for "number of children" and when the Add Record button is clicked, not only does the table containing all the mothers get a record added, I would like XX records added to the children table where XX is the number of children entered into the form. These should have separate IDs but retain a common field that identifies the mother.
View 2 Replies
View Related
Oct 21, 2012
I'm pretty new to making databases outside of a basic access class..Is it possible to make a record in one table that makes a new record in 5 different tables using different bits of the initial record?I want to use the data entered in an evaluation form to create a new entry with the basic identifying information in 4 different tables.
View 12 Replies
View Related
Nov 17, 2014
I currently have a pharmaceutical lot database set up in the following format:
MFGData (table w/Manufacturing Info)
QAData (table w/ Quality Assurance Info)
QCData (table w/ Quality Control Info)
PASData (table w/ Process & Analytical Science Info)
SCData (table w/ Supply Chain Info)
[Code] ....
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?
View 13 Replies
View Related
Aug 5, 2013
I am using a cartesian query to create multiple copies of a record to use for printing labels.
Here's my query that produces the cartesian result:
SELECT tblCount.CountID, tblDeliveryOrders.DeliveryOrderNum, CurrentCY.Deliveryorderlineitemnum, CurrentCY.Quant, CurrentCY.UOM1, tblContainerSizeCodes.SizeCode, tblContainerTypeCodes.TypeCode, CurrentCY.WasteDescription, tblEtidDodaac.EtidDodaac, CurrentCY.ETIDDocNum, CurrentCY.Pounds, tblEPAWasteCodes.[EPAWasteCodes(1B)]
[Code] ....
This works just fine in creating the desired result - EXCEPT I don't get all the records.
When I remove the 'cartesian table', and right join everything, then I get the correct results. If I keep everything as-is and reintroduce the cartesian table, then I get an error about there being an ambiguous outer join.
View 3 Replies
View Related
Feb 8, 2005
Okay, so I have tried to fix this for three days. When I try to create a button, combo or list box I get the message:
ActiveX Controls Can't Create Object. I have been through Internet Options/Security. I have tried reinstalling, I have tried to look into the registry. I even check Microsoft HELP and Mircosoft website and am getting nowhere. Can anyone please, please help solve this problem because it completely baffles me on how to fix this!!!
Oh yeah, (and now I am angry!!!)
View 14 Replies
View Related
Apr 15, 2015
A2010 - on an existing form I have a few controls that I want to hightlight. I thought I would create a rectangle covering only the desired controls and then make the back colour a light grey. But if I do then I cannot see the controls. If I change the back style to transparent then the colour reverts to nothing and I'm back to square one.
It feels like I want to bring the existing controls to the font - but how?If I create new controls on an existing grey rectangle then it works fine.
View 10 Replies
View Related
Jul 14, 2006
Hello,
I have a form with multiple controls (textboxes) named:
Father
Mother
Child1
Child2
On the form there are also two checkboxes (check1 and check2).
I am using the following statement for each control and placed in in the OnCurrent event of my form:
If Not IsNull (Me.Fater) Then
Me.Check1 = "YES" (or -1)
Me.Check2.Value = False
End IF
Is there a way I can write one If statement saying:
If all controls Father, Mather, Child1 and Child2 or at least one of them has data, then Check1 should be selected and check2 emply. If all controls do not have data then ckeck1 should be empty and check2 selected.
Thank you.
View 2 Replies
View Related
Jan 22, 2007
Hello, I am reposting a question submitted some time ago to which I did not get much help, hoping for additional advice.
My database has two tables. Table1 and Table2. Table2 is a copy of Table1. Both tables have 50 fields. Data is imported into Table1 once a week. Before importing data I run a delete query which removes all data. Data imported adds records only to the following fields: FNAME, LNAME, SSN. This means that I am left with 47 empty fields.
Users can see data in Table1 using Form1. They will see the FNAME, LNAME and SSN. These fields are locked and cannot be edit or deleted. All the other 47 fields are blank and user can add data to them.
Currently I am using two buttons to:
1) Close the form (with the code Me.Undo, Me. CLose)
2) Run and append query to Table2 and deleting all added data.
I would like to combine these two functions in one button which would:
If no data has been entered in the remaining 40 fields, than close the form.
If data has been entered it should run the following procedure (Append data into Table2):
Dim MyDb As DAO.Database, MyRs As DAO.Recordset
Dim strCode As String
Dim strFilter As String
Dim stDocName As String
Dim stLinkCriteria As String
Set MyDb = CurrentDb
Set MyRs = MyDb.OpenRecordset("Table1")
stDocName = "MyForm"
stLinkCriteria = "[SSN]=" & "'" & Me![SSN] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.SetWarnings False
DoCmd.OpenQuery ("AppendtoTable2")
DoCmd.Close
The problem I am having is declaring all the 47 empty records. Any suggestion or code? Thanks.
View 1 Replies
View Related
Aug 11, 2014
I've constructed a trivia database that consists of trivia questions that are classified into different categories. The DB also keeps track of which questions were used at which establishment. I am designing a form that will generate multiple rounds of trivia based on user criteria.
As seen here the user can specify the number of rounds and questions per round. Maximum number of rounds is 8 - the tab visibility is changed AfterUpdate of the number of rounds field.I have queries set up that do what I need for a single round - they choose random questions based on category, subcategory, difficulty and if they haven't been used at that client's before.
how to deal with multiple rounds without it becoming a headache. My tab layout has controls txtCategory1, txtSubCategory1, txtDifficulty1, txtType1, txtCategory2, and so on. Currently the queries use the values in the controls for Round 1. I would have to make eight copies of these queries (one copy for each round) in order to generate each round based on specific criteria, which would make a ton of queries. The other option is to use SQL statements in VBA, but this method doesn't like the [Forms]![frmCreateTriviaNight]![cboCategory1] in the code - I have to concatenate the names of the variables in and the code would be a mess.
View 2 Replies
View Related
Jan 10, 2006
i have 15 text boxes named Qty1, Qty, Qty3, etc...
and another 15 text boxes named Comment1, Comment2, Comment3, etc...
i'd like to write an AfterUpdate event code that will apply to all of the Qty controls..instead of write 15 individual AfterUpdate events..
is this possible at all?!
View 6 Replies
View Related
Aug 7, 2013
I am trying to set event handlers on multiple controls in code. This works:
Code:
Me.Controls("txtPosition" & Right(CStr(iLoop + 100), 2)).OnClick = "=UpdateTaskList(" & CStr(iLoop) & ")"
This doesn't:
Code:
Me.Controls("txtPostIt" & Right(CStr(iLoop + 100), 2)).OnGotFocus = "=PostItGotFocusHandler2()"
The second gives an error: "The expression you entered has a function that <databasename> can't find.
I suspect it may be a parameter mismatch, but the normal GotFocus event handler does not take any parameters.
View 2 Replies
View Related
May 13, 2014
I am trying to create a form to enter data in a table. I would like to make it pull in info from a switchboard. If the record already exists I would like it to find it and allow me to edit the info. If the record doesn't exist I would like to be able to add a new record with the data input. What is the best way to accomplish this?
View 1 Replies
View Related
Mar 5, 2014
Basically I have a class event that works for all controls on any userform.
Its all working fine but I can't add an event to trap the click or mouse move of the userform itself.
Basically so when the cursor moves away from a control the label clears.
User Form Code
Code:
Option Compare Database
Option Explicit
'Define a collection and initialise the commandbutton event class
Private col As New Collection
Private newCmd As New clsEvents
[Code] .....
And here is the class Module
Code:
Option Compare Database
Option Explicit
'catch the event of Commandbuttons
Private WithEvents C0 As SubForm
'Private WithEvents C1 As CheckBox
[Code] ....
View 11 Replies
View Related
Feb 26, 2014
On the form I need to give the user the option to select a 'from date' and 'to date' and for this I have put in 2 calender controls which have the same control source(same table column). The problem I am facing is when I select a date from one calendar, the other calendar control automatically populates with the same date and vice versa.
I need to have a functionality where 2 different date can be selected and then when the Search button is clicked, then data within the date ranges should be displayed.
what changes I need to make to acomplish this ? find a screenshot of the form attached.
View 5 Replies
View Related
Nov 14, 2012
My form-based search mechanism uses controls to set the parameters for the query data source.
I have one field call quantity in stock. I could you a Between and And method to allow the end user to input the stock quantity they want.
HOWEVER, i would love it for the user to first select the Comparison Operator (e.g. > , <, >=, <=) from a combo box and then in an adjacent text box, enter the quantity.
The expression i entered in the query goes something like this.... Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity]
When i try and run this, i get the message "THe expression is too complex to be evaluated".
View 5 Replies
View Related
Jan 28, 2005
I have a continuous form based on a query. I would like to have a button or control that performs some command on only one certain record. In other words, the user sees the record he wants, clicks a button and it changes the value or changes the color. When I add a button to the form, it appears next to each record and performs the same command to each record. I have tried different controls, but get similar results with each one. Even a single form seems to do the same thing, but on different pages.
Am I missing something? Or maybe I am not following the concept of the form.
View 13 Replies
View Related
Jun 12, 2006
Hi guys. I hope someone replies to this thread....I am struggling with this one.
I am developing an Asset Management System for our IT Equipment.
We wish to record the software installed on each PC.
The Manufacturer and Software names are stored in separate tables.
The AssetMain table has a one-to-many relationship with the AssetSoftware table.
I originally had a (continuous) subform for software on my main Asset form. However, as I am using cascading combo boxes (to filter the Software Combo by the Manufacturer) it mean that changing the combo on each record would appear to change the existing values too (common problem).
I would therefore like four unbound controls.
1) Combo Box - Manufacturer
2) Combo Box - Software Package
3) Combo Box - Package Edition
3) Text Box - Serial Number
When the user selects/enters these values and clicks an "Add" button I would like add a new record into the AssetSoftware table where Asset_ID = Parent.Asset_ID.
I will then have a list box that queries the software for that particular Asset. This will requery on each CmdAdd_Click.
Attached is a screenshot of what I would like to achieve (if I click Add it should add Microsoft Office XP to the list box below)
Would really appreciate some help (do not normally get many replies to my threads :( )
Thanks
BF
View 7 Replies
View Related
Aug 22, 2005
I have a form that tracks the budget. It is a small budget. But my problem deals with req. dates and req. amounts. You will see from the example I gave you that I can input one req. date and one req.amount and my calculations shows the ending balance. But how do I make fields where the same person can buy from different vendors in one day? I want to be able to show the date and the different requisitions and have the calculation subtract from each one, thus showing the ending balance. Hope this makes since. I attached the file of the example.
So under:
Date Req. I need to be able to put mulitple dates
Amount Spent: Different amounts that correspond to the date.
Ending balance: The ending balance will be correct when all of it is done.
View 1 Replies
View Related
Dec 14, 2004
I have several queries that I'd like to consolidate into one report. Any ideas? I tried selecting fields from multiple queries, but I got an error message.
View 2 Replies
View Related
Jun 19, 2012
I have three tables:
tblClient (for basic client information),
tblDischarge (for client discharge information), and
tblAppointments (for appointments that are added to an outlook calender).
All three tables are linked using the IDNumber from the Client table. I have a form where a user inputs a discharge date for the client.
Once that date has been added I need to add 5 records to the Appointments table for different followup times based on that discharge date (such as one month, two months, three months, etc.).
I have the code working to add an individual appointment to outlook but not 5 different ones at one time. So the question I have is how to add the 5 records based on the one discharge date?
View 1 Replies
View Related
Jan 5, 2012
I am relatively new to Access and have a simple database which I use to generate Publication numbers for our new publications. I would like to have a text box and button on a form that will generate duplicate records of the current record the same number of times as specified in the text box.
E.g. If I need 6 copies of a record I would like to enter 6 in a text box, click a command button that will generate 6 duplicates of that record.
The code in order to do this 'on click'.
Table = Publications
Form = Publicationsfrm
Text Box = Lines
Command Button = Submit
Primary Key = PublicationNo
I have tried the following code but it is not working.....
Dim i as Long, myRS as Recordset
Set myRS = CurrentDB.OpenRecordset("Publications")
For i = 1 to Me![Lines]
With myRS
.AddNew
![PublicationNo] = Me![PublicationNo]
.Update
End With
Next
Alternatively if I have a duplicate record button that uses a macro is there a way to run that macro the specified number of times in the 'Lines' text box.
View 14 Replies
View Related
Mar 30, 2012
HR.zip. I'm trying to create database for HR and all the tables has to be linked with on table "PFEmployees (one employee has to be linked with all other tables "beneficiary,disciplinary,caruser,otherinfo etc...). Is it possible to do it.
Look into attachment relationship. I am really stuck with this.
View 1 Replies
View Related
Dec 18, 2007
Hi,
I'm not sure which Form topic this post should go under ...
I have an Access Database which contains contact information including email addresses in a number of tables. Each table is a reference group of individuals interested in a particular topic and has three fields (contactID, name and Email). All tables relate back to a master address list.
This allows me to have different (or multiple) people from within the same master address as contact persons for different reference groups. When I am doing labels, or printed reports for labelling I simply combine the address from the master list with the conact from the reference group list.
I would like to be able to replicate this approach for e-mail messages.
Ideally the databse would create distribution lists and export these to outlook global address list where they would be available to all users.
Alternately, I would write a module that automatically generates a new email message with all the Email Address entries from a user selected table in the To: field. The user would then edit the email message and press send.
It seems to me that eitehr way I will need to somehow concatenate all emails addresses in a table into a list separated by semi-colons.
View 1 Replies
View Related
Jun 8, 2006
Hi,
I am new to access and DB in general.
Is it possilbe to create a form that updates mutiple records at once with the click of a button? If each record has 4 fields and for each group I want to update at once there are 2 fields with the same value and 2 fields with different values?
Any help on this would be appriciated..
Thanks.
View 1 Replies
View Related
Sep 23, 2004
Alright I've been picking my brain for the past hour trying to figure this one out...
I have 3 tables...
1) Retailers-RetailerID, RetailerName, address etc. (500 entries)
2) Distributors- DistributorID, DistName (10 entries)
3) Uses- RetailerID, DistributorID
Sample data is as follows for 'Uses' table:
RetailerID DistributorID
1 1
1 3
2 1
2 6
3 4
3 5
4 8
I am trying to create a search form based on a query which will return retailer information for those retailer who use multiple distributors.
For Example...There will be 2 text boxes and a user can enter a distributorID in these text boxes. If only 1 distributorID is entered, it will display records for that one.
I have tried the following but I have been unsuccessful:
SELECT Uses.RetailerID, Retailers.Address, Count(Uses.RetailerID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address
HAVING Count(Uses.RetailerID) > 1
Is this possible to accomplish with the way I designed this database? If so, can I get a little advice? I know this is a long question that can't be answered in full but I would just need to basics in order to be on my way.
Thanks in advance!
View 1 Replies
View Related
