How To Add A Record In A Table Based On NotInList Option In A Form

Jun 23, 2005

I've 2 tables:

customers
-id
-customer_name
-city_id

cities
-id
-city_name

I've a form to create new customers accounts.
The form has a drop-down control listing the cities in the "cities" table.
This drop-down control has a hidden column (id) and a visible column (city_name).
The user can choose a citie from the list and the city id (1st column in the drop-down list)
will be stored in the "city_id" field in the "customers" table.

Problem:

I need to allow users to enter cities not listed in the drop-down control.
When they entrer a new city, I need to create a new record in "cities" table for the new city.

I tried opening a dialog using the NotInList event, and the dialog pops up as espected, but also pops up an ugly msgbox wich says that the typed text isn't a listed item.

Please help :-(

View Replies


ADVERTISEMENT

Forms :: Repeat Record Entry Based On Option Group Selection

Sep 10, 2014

Is there a way to duplicate, triplicate, a record based on an option group selection. i.e.

Form
Field: Option group
Value = 1
Value = 2
Value = 3
Field 2:
Field 3:
Field 4:
and so on.

Basically if value 1 is selected, then I fill out the rest of the fields hit enter and go to a new record. But if value 2 or 3 is selected, I fill out the the fields, hit enter. The information is recorded said number of times based on the selection in the option group. Ideally I would have2 or 3 of the same record in the db, with different Primary keys of course.

View 3 Replies View Related

Select Option Group Option Based On Checkbox Value

Aug 22, 2005

Hi

I've got an Option Group with 3 options; Yes, No and Future.

I also have a range of Check Boxes; 1 for every day of the week.

When one of the check boxes is selected I want the Option Group to change to Yes.

Currently, the Option Group defaults to Future and No will only be available by manually selecting it. This doesn't need to be changed.

Any help will be greatly appreciated!

Cheers

View 2 Replies View Related

Reports :: Sort Report Based On Option Group On Form

Oct 9, 2014

I have created a form with an option group with 4 options (date, line, description and observation). Each of these options are to be a sort order for a report that will open after the user selects an option and clicks a button. I am trying to code the button using a select case so that case 1 opens the report sorted by date, case 2 by line, etc. how to write the code for the sorting.

View 6 Replies View Related

Forms :: When Using Query With Grouped By Option / Can't Edit Record In Table

Aug 19, 2015

I have a table called "Workorders" that I have created a split form for and have customised it so that it had combo boxes etc to enable the user to change the record data.I then needed to add some existing queries to the form so that I could calculate some totals from records in a few other tables. This all works fine EXCEPT that I can no longer change any of the fields in the records.

I am sure that it is something to do with the fact that the 3 queries I added to the form use the "Grouped by" option and based upon my Googling, this is probably the issue.Here is one of the queries:

SELECT DISTINCTROW [Workorder Labor].WorkorderID, Sum([BillableHours]*[BillingRate]) AS [Labor Total]
FROM [Workorder Labor]
GROUP BY [Workorder Labor].WorkorderID;

I need to have the totals and I also need to have the ability to change the data..

View 8 Replies View Related

Using The NotInList Event And Updating Two Fields The Table

Nov 26, 2005

I have a data entry form where country(ies) (there can be more than one for
each project) is/are entered in a sub-form on a tab control. Countries not
listed are entered by the following:

Private Sub CountryName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl

strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to
add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tblCOUNTRY (CountryName) "
strSQL = strSQL & "VALUES('" & NewData & "');"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub

This works fine. However, if I restrict the country names by a region
selection on the main form (for example if region Africa is selected on the
the African countries are preselected) the NotIn List does not work. Any way to work around this?

Another question, I want the user to select a region (a combo box ) on the
main form and when a new country (using the NotInList event) is entered in the subform both values go into
the same record in tblCountry (which has two fields CountryName and Region).
For example, I enter AFRICA for region on the main form and on the sub-form I enter Malawi which is not listed. I have tried the following:

Private Sub CountryName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl

strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to
add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tblCOUNTRY (CountryName, Region) "
strSQL = strSQL & "VALUES('" & NewData & "', FORMS!frmAddPro!Region);"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub

But it does not work (Region is the control on the main form named frmAddPro).
Any suggestions would be welcome. Thanks.
Niels

View 4 Replies View Related

Modules & VBA :: Add Rows To Table Based On Record Count Of Another Table

Mar 10, 2015

I am trying to assign teams to players. I have an import table with all of the players information listed. What I want to do is determine the count of players in a given city. For every 9 players I want to add a new record to the Team table and assign the team number (auto incremented for each team created). Then I want to add the players to the Players table with the Team Number that was created.

View 8 Replies View Related

Tables :: Disable A Record From A Table Based On Attribute Of Another Table

Jan 12, 2013

I have a keys table and a keysctivity table. I need keys to not be available if they are currently signed out (return_date is null) or if they have been marked as lost (lost_key = true)

This is my activity table. URL....If for example signin_id 1 was not returned or was lost, key_id 1 should no longer be available. Is this something that can be done?This is the access file I am working on: URL....

create two new tables "lostKeys" "unreturned_keys" and have records moved to their respective tables based on whether they are indicated to be lost or currently not returned.

View 4 Replies View Related

Hide Selection Option On Form By Making Lines On A Table INACTIVE

Jan 15, 2014

I have several tables linking our employees to certain supervisors, etc.On the Form, there is a drop down with all employees listed that someone can select and then enter the stats required. When someone leaves the firm, I want to remove them from the form dropdown list, but not delete them from the corresponding table. I have seen this in other databases where they someone added an 'Inactive' column with a Yes/No response and when you selected the inactive - yes, that name would disappear from the form when people select names from a dropdown to enter stats.how to make the names disappear from the view in a form, but not in the table related to that form?

View 6 Replies View Related

Forms :: Form That Adds Data To A Table Depending On Which Option Was Clicked In Combo Box?

Sep 30, 2013

i have a standard database with tblProducts, order, employees, customers, suppliers etc. i also have a table called tblperson with personID which acts as a one, with the many in three different tables (customers, suppliers and employee) the person table holds data such as first name last name address city postcode and phone. now, ive created a form called frmPerson that acts as a adding data to my database. ive got a load of buttons a working add, delete and a save button, i also have the option to switch through records next, last, end and first. now basically in this form i want to be able to add data i.e. fill out the text boxes and then have a combo box or something similiar that when i add the data i can basically click an option the says 'employee' 'supplier' or 'customer' and then the data gets added to my person table however the difference is that it will also be associated to the record in the wanted table.

View 14 Replies View Related

Delete Records From One Table Based On Record From Another

Oct 10, 2014

I want to Delete all records from the Table Named "CustomerWiseRotaryTillerRates" (Given Below) if the State in the State Filed in found in the "Customer_Master" Table (attached Herewith). What will be the correct procedure ?

I followed the steps referred in page no. 67 & 68 in the Book "Microsoft Access 2007 Data Analysis" by Mr. Michel Alexender.

Customer Name
State
Product_Code
Product_Name
Price_perunit

TSI Engineering Indutries (P) Ltd.
Assam
P_05
1500_Rotary_Tiller_220_RPM
Rs. 63,722.00

Kishan Krishi Engineering Works
Jharkhand
P_05
1500_Rotary_Tiller_220_RPM
Rs. 62,997.00

TSI Engineering Indutries (P) Ltd.
Assam
P_06
1750_Rotary_Tiller_220_RPM
Rs. 66,663.00

Shri_Tata_Enterprises
Bihar
P_06
1750_Rotary_Tiller_220_RPM
Rs. 65,683.00

View 1 Replies View Related

Modules & VBA :: Retrieve / Validate Data On 2nd Form Based On Record Selected On 1st Form

Jul 29, 2013

Can I look up and verify data on a "second" form based on a selected record from first (still open) form.

I am trying to allow users to select a User Name from a combo box list and then open "Change Password" form when they select "Change Password" for that selected user name.

My problem is that I can't figure out how to associate and verify the data tied to the user name selected on the previous (Login) form ( I am trying to validate the old password tied to that selected record).

I have the first login form created, and it's working just fine. I also have the change password form created (and it's displaying the user name selected from the first form using:

Code:
Private Sub Form_Load()
With Forms![frmLogin]![cboUserName]
Me.txtPwdChgUserID = .Column(2, .ListIndex)
End With
EndSub

I also have the code written to validate and confirm old password, new password and validate new password (when the save button is clicked). I have yet to update the password with the new password (still trying to figure that out).

Attached zip file has screen shots of the two forms.

View 3 Replies View Related

Visible Option On 2nd Record Linked To First Record

Jun 16, 2006

my form works fine on the first record of the database but now i have added a 2nd record some of the functionallity form the first record is applied to the second. At the moment in my table for each record i have a status field which speifies whether certain data is visible to the user or not. When i select visible = false on record one this is also appled to record 2. I dont want this and want each record to have there own specific status.

For example at the moment i have:

if me.combobox.value = 1

Me.mytxtbox.visible=true

else

Me.mytxtbox.visible=false

endif

This seems to apply the rule to all records not just the current one i am in. If explaination isnt clear i will gladly expand further

View 2 Replies View Related

IIf Statement Used For Sorting Data Based On Option Group

Jan 10, 2006

Hi all,

My problem is as follows:

I am trying to generate a report, which is based on a query, which references a form that has an option group containing four options.

I want to sort the data according which option group radio button has been selected (they the values of 1,2,3,4).

Originally there were only 2 options and the query said (in SQL view):

ORDER BY IIf(Forms!frm_08_reportsort!opt_sort_by=1,TBL_Acco untManager.Name,TBL_SiteContactDetails.SchoolName) ,

This works fine for 2 options, but I can't figure out what command i should use for more than 2 options :confused:

Any help would be much appreciated :)

View 3 Replies View Related

Queries :: Option Group - Query Based On Classifications

May 25, 2015

I used an option group to classify a record in my database. It works great for this purpose, but I am at the point now of creating a query, and I would like to query based on these classifications (or options). Is this possible to do?

Also, for the option buttons, the value is numeric. When I create the Query (or a report off of that query) I would like the actual text values to display, not the data value Access is using.

View 2 Replies View Related

Discrepant Record Numbers Between Query And Table It Is Based On.

May 25, 2007

I have a temporary table with all text fields for importing data to a normalized table. In order to import I need to prep the data by pulling the numeric values of "lookup" type fields (nb. not actually using MS Access lookups)

My prep query is producing 9 more records than there is rows in the import table, even though every join is a LEFT join, which should just pull all import rows and match up lookup values where they are available.

eg. of import table (all fields are text)

Location;
Group;
Name;
Wfm type;
Account Number;
WFM Status;
Date Created;
Date Held To;
processdate

Beachville Operating Centre;
Service Orders;
DAVID JONES;
;
Complete Engineering Investigation;
Completed;
4/20/2007;
;
4/27/2007

here is my prep query (the iif is to replace userid's that can't be found with UNKNOWN)

SELECT lookup_location.val AS F1location, lookup_group.val AS F2group, IIf([lookup_user.val] Is Not Null,[lookup_user.val],"UNKNOWN") AS F3user, wfm.wfmcode AS F4wfmcode, CURPW020_import.[Account Number] AS F5account, lookup_status.val AS F6status, CURPW020_import.[Date Created] AS F7datecreated, CURPW020_import.[Date Held To] AS F8dateheldto, CURPW020_import.processdate AS F9processdate
FROM ((((CURPW020_import LEFT JOIN lookup_location ON CURPW020_import.Location = lookup_location.txt) LEFT JOIN lookup_group ON CURPW020_import.Group = lookup_group.txt) LEFT JOIN lookup_user ON CURPW020_import.Name = lookup_user.txt) LEFT JOIN wfm ON CURPW020_import.[Wfm type] = wfm.wfmname) LEFT JOIN lookup_status ON CURPW020_import.[WFM Status] = lookup_status.txt;

any assistance would be much appreciated. any other info needed let me know.

View 3 Replies View Related

Getting Data From One Table Based On Current Viewing Record

Jul 30, 2007

Hey all,

I am working on converting someones Paradox Database to Access and making some modifications.

I have a table with people in it and they are linked to a number of interests. But i am struggling to create a query so that on each page of the form for the person i can call the interests and display them on the form as a list...

If anyone could point me in the right direction that woul dbe appreciated!

Give me mysql and a bit of php anyday!! hehe

I am using Access 2007 but i have used access before so i should be able to work my way through instructions for 2003.

thanks

RF

View 4 Replies View Related

Forms :: Lock / Unlock Textboxes Based On Option Button?

May 11, 2014

I have a form with 2 option buttons on it one with year 12 on it and one with year 13. I have a simple form with drop down menus and text boxes allocated for year 12s and 13s. I want all of the year 13 text boxes and drop down lists to be locked if year 12 is selected and I want all of the year 12 text boxes and drop down lists to be locked if year 13 is selected. I do not want to over complicate the form by hiding parts of it or having a new form open depending on the option button selected.

View 9 Replies View Related

Forms :: Control Option Buttons Based On Selected Date

Jun 28, 2013

I'm building a workCube reservation system and I've been tasked to have it work kind of like an airplane seat reservation system. I've laid out my form with option buttons representing the location of each available space. (space1, space2, space3...space16)

My desired outcome is to be able to select a date from a calendar popup and have the options buttons react to that date if they have been reserved. (change color and indicate "reserved").I've tried to create the form based on a query which represents the "booked" table.

Tables:

Employee
(k)empID
emplyeeName

Space
(k)spaceID
space

Booked
(k)spaceID
(k)empID
(k)bookdate

Some rules a space can be booked by any ONE employee on any day.How can I get any and all of the option buttons to react if there is a reservation in place on the day indicated by the calendar?

View 4 Replies View Related

General :: Meter Reading Table - Calculation Based On Previous Record

Aug 2, 2013

I have a table full of meter readings. I want to have a field called consumption which looks at the reading just entered for that month and then subtracts the previous months reading for that meter which leaves the consumption.

Can I get a formula that can work this out automatically?

View 3 Replies View Related

Modules & VBA :: New Record Button With Prefilled Number Based On Selection On Another Table

Jan 6, 2015

I'm making a database that so I can log calibration information about equipment every year.I have 2 tables:

The Equipment details table
The Calibration record table

ID Number is shared between the 2 tables so a calibration record can be linked to its' details.

For Example (simplified sample data):

Equipment Table
ID Item Unit Type
104 Thermometer DegC PT100

Calibration Table
ID Cal Point 1 Test Equipment Unit Under Test Date
104 20 21 22 06/01/15

What I want is a button on a form that creates a new blank record in my Calibration record table with the ID number already entered based on what record I selected in a combo box linked to my equipment table. I really don't know where to start.

View 2 Replies View Related

Opening A Form Based On Individual Record

Oct 6, 2004

All my records are displayed on a form. Upon clicking on their respective 'View' button, I would like to open that particular record. How can I do that? I have attached a screenshot for better clarity. Thanks in advance for the help!

Regards,
Swee

View 3 Replies View Related

Making Form Open With Data From A Table Based On Date And Time In Table?

Sep 12, 2014

How to make a form open with data from a table based on a date and time in the/a table?

View 7 Replies View Related

Print Single Record Based Upon Current Form

Apr 6, 2006

I need to Print a single record from a form but using a report layout.

I have created the form and created a report. A button on the Form kciks off the print but I get all records rather than just the one shown on the Form. Whats a really simple way of just selecting the Current record.

Where do I put an instruction saying print only this record:confused:

View 9 Replies View Related

How To Find Record On Form Based On Multiple Queries

Jun 23, 2006

I currently have a form to manage inventory. On the left side of the form, I have a list box that shows all of the items in the database and finds the record on the form based on the selection.

I would like to have toggle boxes or something underneath the list box that when clicked will sort the box by vendor and item and another that when clicked will sort the list by part number and then select the record on the form based on the selection at that time.

I have no idea how to accomplish this...can anyone help?

View 1 Replies View Related

Create A New Record, Opening A Form Based On The Key Field

Jan 28, 2005

I seem to need some help!
I have a table with customer information in it ie: name, vehicle, (Key=Id number)...
it is the master link to the repair table ie: mileage, (Key=ROnumber), and repair data..
these two work together beautifull
now I need to add another table, a check sheet for checking over a vehicle.
I create the table and the form however I cannot open a new form with the ROnumber from the second already open form into the newly open form.
(the second form is based on a Query, and I have tried changing it to a SQL statement both with no luck)
is there an example of what I need to be doing to link the ROnumber to the ROnumber in the new form, or create the ROnumber so it will add the data in the linked table?
I need this to open using a button on the open repair form
David

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved