Forms :: Add New Record Or Select Existing
Sep 30, 2014
First Access 2013 form being created. (i may not use proper termonology but trying).
Tables:
tbl_Clients (PK=clientID auto#)-holds details of each client (Lname/Fname/MName/addr/preferences etc)
tbl_Encounters (PK=encounterID auto#)-could have many encounters for each client.
tbl_followup (PK=followupID auto#)-some encounters need followup, some don't, some encounters have many followups.
Have a form linking to tbl_Client, with subform to tbl_encounters (going to create another form for followups-haven't got that far yet). I would like to try to avoid duplicate clients in the tbl_Client, so when the form is opened they could look for client if not there add new. However for the existing client if say the address changed i want them to be able to do that. (i could get rid of dups in a query/report based on matching certain fields etc, however i'd like to avoid it in the first place).
View Replies
ADVERTISEMENT
May 6, 2014
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
View 1 Replies
View Related
Dec 24, 2013
I have the employee data record... I want copy of the same record to insert in table but with different employee id and Site through forms. StaffNo is the primary key.
For example
existing record
StaffNo, Site, Name, hiring date, etc
AKA-111, 10, John, 25-10-13
new record
StaffNo, Site, Name, hiring date, etc
ALM-123, 14, John, 25-10-13
View 13 Replies
View Related
Mar 19, 2014
I'm having difficulty updating an existing record in my form. I can enter a docket number on my form and when i hit my button (Find Docket) it brings up all the customers info (name, date, invoice, vat, total, etc.) What i want to do then is be able to amend/update the record. Just wondering do i need to put vba code in my save button and how to code it and is it similar to my find data code below.
Here is my save function code:
Function SaveData() As Boolean
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Skips Delivered")
[Code] .....
View 6 Replies
View Related
Nov 14, 2014
I want to open an existing record via a form and then edit that data but save the form as a new record. Basically a form used for handover/takeover process where most of the data remains the same but the new form is the basis for the next handover/takeover.
View 6 Replies
View Related
Dec 28, 2014
I am very new to VBA and I have been self-learning VBA from two month and have been assigned to new project Work Authorization & Lock Out Tag Out.I have table called tblWA & tblLOTO.Basically most of the LOTO/s are associated with WA #, following example
WA # in tblWA
Associated LOTO/s in tblLOTOs
25258
123456
123457
123458
123459
25259
123410
123411
123412
123413
However, both tables are in relationship right now..I have form called WA Switch Board where I have Datasheet View form with all WA records, fields are WA#, WAStatus, WAIssuedDate, IssuedBy, CompletionDate and etc but I have dropdown with with WAStatus with (In Field Today, On-Hold, In Approval, Cancelled, Close).Now from the main switch board when authorized people try to change the status of permit to Closed I want recordset or count to loop through tblLOTO and give me a message box if associated LOTO/s status not equal to Close.In other word, if the associated LOTO/s are not close then the selected WA # in WA Switch Board cant close.Here is the code I have been playing with no success
Private Sub PermitStatus_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
[code]....
View 3 Replies
View Related
Sep 16, 2013
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I am only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform.
View 8 Replies
View Related
Aug 11, 2013
I have a search form with a subform (it's a query). When you search for an entry, the results show up in my subform.
Is it possible to select that record from my subform and have it open up in my main form for making changes to that entry? Either with a control button, macro or coding?
Screen shots: The first is my search form, the second is my data entry form.
View 1 Replies
View Related
Nov 5, 2013
I have a form for entering property tax. The form is based on a table called investments. The form contains a combo box to select the appropriate property for data entry to the table. The two fields in the combo box are Property PIN and Property ID. The property ID is a combination of tax year+state+county+PIN. The same property can come up in more than one tax year so there are duplicate PINs but the Property ID is unique and is the key field. My problem is the combo box shows the duplicate PINs with their respective unique Property IDs as separate records but will only select the record for the earliest tax year regardless of which records I select on the combo box.
View 3 Replies
View Related
Feb 28, 2014
Attached is a snippet from my search form.
I have a search on Last Name, and in this example, I use Last Name beginning with "m". That pulls up two matching, filtered results, Mickey and Mimi.
All the controls in the detail section are disabled because I don't want the users to make any edits here. I want them to click on a row and that will take the Record Number from that row and open up a Detail form. This is where they will make their edits.
I've set the On Click property for the Detail section to run a procedure. The event procedure works fine if the user clicks on the selected row. Notice the record selector in the picture is pointing to Mimi. If the user clicks on Mimi, everything works fine. If the user wants to open Mickey's record, they have to move the record selector first and then click on Mickey (2 clicks). If they don't move the record selector first, nothing happens.
Is there a way to change the selected record when the user clicks anywhere in the row? Again, all the controls in the row are disabled, so this doesn't happen automatically like it normally would. I considered putting a command in the On Click property, but that event procedure seems to only run if the user clicks on the selected row. I just need the selected row to change if the user clicks on a row other than the currently selected row. How do I do that?
View 7 Replies
View Related
Aug 14, 2013
How to do a 'Delete from ListBox' . My listbox is populated, but I want to be able to put a button on the form, select a record from the listbox and press the button to delete that record from the list.
I should add that this is an unbound listbox so it needs to be removed from the listbox and the table that is populating it.
Listbox is List22 the table that populates it is 'tblShootingTasks'
View 7 Replies
View Related
Nov 9, 2014
I have a subform inside a form and in this subform I have an Ordering field which orders the items in the form based on this field. In the After Update event of this box I use Me.Requery so that you can actually see the change of the order in the items. The problem is that after requering the form the selector goes to the first item of the form.
Is it possible some way to keep the selector to the same item after the requery takes place?
View 1 Replies
View Related
Jun 13, 2013
I have a form that uses a combo box to select the record to edit which then opens the edit form... I keep getting a type mismatch error. The bound column is a text column and I'm wondering if that is the problem because this works perfectly everywhere else in the database. The primary key is the SKU of the product which is alphanumeric which is why I have it set to text...
View 14 Replies
View Related
Nov 24, 2014
I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
View 9 Replies
View Related
Sep 19, 2013
I am attempting to create an attendance database that needs to be able to be updated if someone calls in sick.
I have created a cascading combo box where you can select a date up to 15 days into the past with the following code in the rowsource for cboDate
Code:
SELECT DISTINCT tblAttendance.dataDate FROM tblAttendance WHERE (((tblAttendance.dataDate)>Date()-15)) ORDER BY tblAttendance.dataDate DESC;
an after update event has been coded to show only employees who have been scheduled for the day
Code:
Private Sub cboDate_AfterUpdate()
On Error Resume Next
Me.cboAgentName.RowSource = "Select tblAttendance.agentName, tblAttendance.attendance,
[Code]....
So basically what i need to do is be able to use the cascading combo boxes to select the date and employee to go to that specific record so i can update the other fields. Currently when i attempt to do this with my current form, it automatically goes to a new record when i select a date and name and doesn't update the existing record.
View 1 Replies
View Related
Jan 3, 2014
I have created a form (races) with a subform (yachts in a race) to edit data in joined tables. It seems to be working well but I am having difficulty selecting which record to edit in the main form. I can scroll through the records until i get to the correct race but this won't work well when there are a large number of races to choose from.
I can see that I can use linked forms to select a race from a multi form or datasheet and call up my desired form by a double click on the ID field. This should work but isn't really elegant with users scrolling down a long datasheet to select the race record they want to work with. Is there a better way?
Races are uniquely identified by 3 attributes: SeriesDivision, Date & RaceNumber.I have created an index in the Race table for these 3 fields which forces them to be unique.I would love to create an interactive routine which asks users to select from a list for each of these 3 attributes and then returns the form for editing with the appropriate race record selected or even cascading - users select a SeriesDivision can then choose the valid dates for the selected SeriesDivision and can then select the valid raceNumbers for the selected date
View 1 Replies
View Related
Jan 15, 2014
i have the database with records of blood donor names, addresses, phone numbers, blood group and that is main with date when blood was taken. I want to select that donors whose blood was taken 60 days ago or more. I want to select them somehow.
View 14 Replies
View Related
May 7, 2014
I have a form which uses a listbox to display a given record but when a user without edit rights opens the form in read only mode
Code:
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormreadOnly
The user is unable to use the listbox to select a record to view.
I have also tried opening the form in edit mode (acFormedit) and then setting the allowedits etc. to False
Code:
Me.Form.AllowAdditions = False
Me.Form.AllowDeletions = False
Me.Form.AllowEdits = False
But this didn't work either.
View 3 Replies
View Related
Jul 3, 2014
I have a form that allows the user to select a record in a combo box. When this selection is made I want a 2nd form to open and give the user a place to enter additional info about that item selected. I have the forms working correctly but now I would really like to add the 2nd form to the first form as a subform but when I try this I get the message"The expressing is typed incorrectly or is too complex to be evaluated..." There are no fields on the first form that link directly to the 2nd form - because the combo box in an unbound combo (the selection in the combo box is what the 2nd form opens base on) I tried to add this as a subform with no parent/child info and I get the message stated.
I was hoping to make the 2nd form not visible until the selection was made in the combo box then make it visible for the info to be entered. Would really like it to be on the same form not as a separate form opening.I believe I just found that the reason for the message. It seems not to be a subform problem but is because the selection in the combo box needs to be made BEFORE the form can open. So now the question is - how can I make this a subform and avoid this error when the main form first opens. I will test the visibility and see if not visible until the selection clears this up.
View 10 Replies
View Related
May 12, 2014
I have a list box bound to a query. If the list box retrieves two records, I am not able to select only one of them. If I click, it gets both records selected ( highlighted ). Is there any way I can select only one record?
View 8 Replies
View Related
Sep 19, 2014
I am looking to have a combobox on a unbound form to select a product code (this i can do). However I want a textbox to auto populate with the description as well - both in the same table tblproducts - product_id, pcode, pdesc....
View 4 Replies
View Related
May 22, 2015
I have a continuous subform linked in a data query, I added some check box to select for each record. However, everytime I check a checkbox, it also check the same checkbox on the next record.
My check box consists of Shipping options - FEDEX, COURIER and HAND DELIVERY. These are the options I need to check for each records in the subform.
Is there a way I can select different checkbox for each record?
View 7 Replies
View Related
May 15, 2013
I have a list of client stored in a table "Clients". I would like a form to present a user with the next client in the list at the click of a button, but there will be some exceptions:
Some clients will be given priority, and should be moved to the top of the list although they may not be the next logical entry in the table. Priority clients are indicated by a field "Priority" with a Yes/No option set before work begins.
Some clients will have requested a call at a certain time of the day - This time will be stored in a field named timeToCall. The next user to click the button after the time has passed should be given that clients details.
When the end of the list is reached any clients who could not be contacted will be tried again. My table currently has a field "Attempts" to track this, but that may not be needed. Clients who have been dealt with will be removed from the "Clients" table to a "Completed" table.
Whichever record is selected will be flagged as being dealt with so that 2 people don't get the same entry. I may add a new field for this, but right now I plan to use the "attempts" field to control this
I am unsure of the best approach to the above, I'm not very well-versed in Access, but if I were doing this in Excel (as originally requested by my boss) I would do it like this:
Use a form where the "Next" button will use VBA code to first check if there are any priority clients, then check if there are any timed client's ready to call and then move to the next entry that has had 0 contact attempts. Whichever record is the next one will be displayed in the form to the user.
View 1 Replies
View Related
Aug 9, 2014
I have a form which displays the records from a query (i have included a picture of this form) and allows me to filter them and create a report from the filter.
I'd like to be able to select a record with the record selectors (or preferably without) - and then go onto edit it in a new window and save the edit...
If possible also to select a record with or without record selectors and click a button to delete the record
searchdb.jpg
View 1 Replies
View Related
Jul 18, 2013
I have written the following code. I want to change the status of my order when reminaing products shipped is equal to zero. When I run this code I get error " Cannot update. Database or object is read only". I think that this error is because that I am using the front end of the database.
Code:
Private Sub Form_AfterUpdate()
Dim dbs As DAO.Database
Dim rstTest As DAO.Recordse
Dim strQuery As Strin
strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining
[code]....
View 12 Replies
View Related
Apr 19, 2005
My database tracks employee training.
I have tblClasses, tblEmployees, and tblRegistrations. I have a registration
form with EmpName and subformClasses, which allows me to register one
employee for several classes at a time.
My registration form has an OK button. I need a message to appear (on btnOK click) before adding a new record to the registrations table that indicates if the employee has already had training for the one or more of the classes selected in the subform. Then the option to "Yes" add the record anyway, or "No" cancel the record.
I have everything working except, I can’t figure out how to do the
comparison with the subform and generate my "Yes/No" option. I’m hoping that one of you wonderful access geniuses will have a relatively simple suggestion for me.
--
Thanks in advance!
Shel
View 4 Replies
View Related