Hi,
i'm not sure if this should be posted under queries or forms...
i have a series of combo boxes in a from based on a query.
each combo box needs to display options for characteristics of each section of a motor (ie. combo box for 'Corroded' = 'slightly', 'very', 'not at all', combo box for 'dents' = 'none', 'top', etc)
my table structure works like this: i have a parent defintion tbl that defines the list of combo boxes (i.e. 'corroded', 'dent', 'rub marks') and assigns then and ParentID number that links to the tbl:InfoObjectDefinitions that defines ALL the possible characteristics ('slightly', 'very', 'not at all','none', 'top') and assigns them an ObectID number.
So in a nutshell, i can pull the possible numeric options (OID) and text values('slightly') for 'corroded' from the table InfoObjectDefinitions according to 'corroded's PID.
I then store the OID in the column for 'corroded' in tbl:Repairs.
so i need to create a form that lets people add/edit entries in the repair tbl using the text values and not the id numbers.
i can create a query for the form, that joins the repairs table and the InfoObjectDefinitions table, and substitutes the numeric values from the repairs table with the text values in InfoObjectDefinitions but then the comboboxes arent editable???? (the combobox expands, and i can see all the text values but cant select a new value)
The SQL that created my query 'repairFormTest' is:
SELECT Repairs.FENumber, Repairs.Corroded, (select otext from InfoObjectDefinitions where pid = 17 and oid = Int(Corroded)) AS CorrodedTxt
FROM Repairs;
and the form places the field CorrodedTxt as the Control Source of the combo box with 'select otext,oid from infoobjectdefinitions where pid = 17' as the Row Source.
And as i mentioned, the box lokks right, and expands correctly but i can select another value.
Any help appreciated, i really stuck.....
Thanks,
Vauneen
I have have 2 feilds in my database, there can be the same value in one feild but not both.....for example say NAME and ADDRESS i can have the same NAME for more then one entery and i can have the same ADDRESS for more then one entery but i cant have the same NAME And the same ADDRESS....anyone know how to do this? :confused:
I have a form based on a select query (query does calculations as well). The query pulls from 2 linked tables, and it's not allowing me to add new records. On the form properties, I have "allow additions, allow deletions, allow edits, and data entry" set to yes. I've tried adding code to go to a new record when the form opens and this doesn't work, either.
I'm new to Access and am trying to work out the best way to achieve the following.
I need a way for users to be able to filter the data which is displayed by forms or output in reports. For example, suppose that a user wants a list of all customers in either of two specific towns with a specific date of birth.
Obviously this could be achieved using a query and setting the criteria of the towns column to be:
"Anytown 1" or "Anytown 2"
and setting the criteria of the Date of Birth column to be the specific date required, e.g.
"08/08/2006".
However, so far everything about my database has been very user friendly with forms for data entry and presentation, so I don't really want to force my users to start creating their own queries.
The alternative I'm thinking of is as follows. Provide a query to pull together the required information (from various tables) but without any specific criteria. Then create a form with a field for each of the columns in the query, and allow the user to type their critieria into these. Then, place If statements as the criteria of the query to check if there is anything typed into each box on the form and, where there is, set it as the critieria for the query, then run the query.
With the above in mind, I have two questions.
1) Is there a better, more "standard" way to achieve a system of allowing users to create customised filtered data without requiring them to write queries?
2) In some cases, e.g. like with my specification of a town above, it would be good if the user could select the town they want to filter for from a drop-down combo box, rather than typing it in and risking entering details incorrectly. However, this would obviously allow them to select only one town, and not two as was required in my example. Is there an equivalent to a combo box which would allow multiple selections?
Thanks in advance if anyone can help me - and please remember that I am new to Access so whilst I want the best possible solution, I need a plain English explanation!
I have a very simple database used by 3 users at a time. It is split into BE and FE. One user accesses the DB with queries only so I have created a DB with the master file linked - and literaly nothing else, She will then create and save all of her queries here. But - I want to keep her from accidently updating the file from here. I can't seem to find a simple solution to simply not allowing her to update. Any ideas? Thanks - Dika
How can i have multiple people entering data into a database, also there is an excel file that imports the info every few min, if i have that open i cannot enter data into the database. theres going to about 4-5 users plus the excel file.
I have a table called tblSchemeCodes with the following field names:
Field Name Data Type Field Size Required Indexed
strCompany Text 1 Yes No strSchemeCode Text 3 Yes Yes(No Duplicates) strSchemeName Text 100 Yes No strOrigNo Text 9 Yes No strCategory Text 9 Yes No dtmDateAdded Date/Time No No
Here is an example of some of the scheme codes I have:
HG HGA HGB HGD HGF HGG HGJ HGP etc etc
however when I try to add HGE, it won't allow it, even though it doesn't exist within the table, it keeps changing it from HGE to HE, and HE already exists, so a can't add messgage pops up, which is should, because duplicates are not permiited.
I've tried turning off all of the required aspects and changed the "Yes (no Duplicates)" to "No", and the problem persists!!
Has anyone any idea why access would behave in this way and how I might resolve it.
I have a database that allows multiple entrys of the same information. I also have a report based on a query that pulls out said data.
I just need my query to pull out every record regardless if it is indentical to a previous one. I found under queries > design > properties there are two options unique values and unique records. According to a source online, these are what control allowing duplicates through, but I have swapped them around with no luck. Currently they are both set to "no".
Is there some other setting I have to switch up in order to allow the duplicates to come through?
I have 2 related tables. The form to enter new data into the tables is set up with a subform for the second table. I want to allow the user to query for a specific record, have it display in a form that looks exactly like the data entry form, and be able to update the tables from that. I first tried an exact copy of the data entry form, using a subform for the second table. It allows me to update the 2nd table from the query results displayed, but won't allow updates to the main form. So then I tried just doing one form with all the results displayed, no subform, but then it won't allow me to update ANYTHING.
I have a filter (via textbox) on a form that only allows 1 character at a time to be typed (!!?) ie: after typing in "a" it highlights it, which means the next letter I type in, replaces the "a".When I use the mouse to re-click after the letter entered, it does allow further characters.What am I missing here?The code for the filter:Private Sub txtClientSearch_Change() On Error GoTo ErrorHandler Forms!frmClient.Filter = "Name Like " & Chr$(34) & "*" & Forms!frmClient!txtClientSearch.Text & "*" & Chr$(34) Forms!frmClient.FilterOn = TrueExitProcedure: Exit Sub ErrorHandler: DisplayError "txtClientSearch_Change", Me.Name Resume ExitProcedureEnd SubMany thanks!..Is it anything to do with Focus?
The first relates to the fact that in the crosstab is a field that is set to Yes/No. It appears as a textbox with a value (0 or 1) in it. I want to replace this with a tickbox but when run neither the textbox nor the tickbox will allow any changes. Is it possible to set this to be editable? I cannot see how this would relate back to the actual data table but if it is possible then this should become clearer.
The second issue relates to the fact that the crosstab obviously creates the number of columns in relationship to the maximum number of values of the relevant field. So, I have a field that can have values from 1 to 15 but in most cases the actual value runs no further than 9 and 10. So for those records where the maximum number is not being used I want the unused tickboxes NOT to appear. Again, is this possible.
In connection with the second, if I have (as I do) this group of 15, which is in fact made up of 3 groups ranging from 1-8, 1-4 and 1-3. So number 8 may not be required in the first group and numbers 3 and 4 in the second. I would prefer there not to be a strange gap between the datasets when run. Is it possible to set up a continuous form so that all the potential boxes are in place, that I run code on load that puts the values in from the query, hiding the end tickboxes and so collecting the groups together - then allowing the changes to be detected through code and re-assigned to the table?
I just created a form using the form wizard. I have added records from both tables and queries and now the form will not allow me to add any new records. Is this because I've used both tables & queries?
I'm currently working on a database that includes several tables with information about current and former employees. One of these tables has the training history for each employee, where the columns are the type of training (currently 37 types), and the data entered for each is either null or the date they completed the training.
I have a form bound to this table that allows the user to edit and add records, but additional types of training will surely be added in the future. I would like for the user to be able to click a button on the form that allows them to add a new training field to the form and underlying table. I understand that users should typically not be allowed to add new fields, but I can't think of a better way to account for the fact that new training will surely will be added in the future.
I found this sample (http://home.earthlink.net/~mgf00/SelectDemo.zip) which allows you to move your selections from one listbox to another with four buttons (<<, <, > and >>) which is just perfect.
Well, almost, actually.
This is already set up to protect against duplicates and that's a good thing. However in my case, my uppers want me to allow duplicates. The code is beyond my ability and my attempts only caused more problems.
The code requires a unique index be generated when the selection is moved between the listbox. Duplicating the index will cause error, and my attempt to circumvent that has either failed totally or is inconsistent (e.g. works if it comes up with a unique value, but will fail if it happens to duplicate a key).
Can anyone show me how I can modify the code to allow for duplicates? The selections then will be looped into a many side table.
I have a problem that should have an easy solution. But I can't find it.
I need to have a user add a new record. (Created a form with the fields on it, No problem , so far.)
The problem is when the user is adding a new record if they hit the PAGE DOWN Key or the mouse scroll, they then go to a new NEW record. (And if they are not paying attention they now have two new records)
I'm using Access 2003.I'm trying to create a command for a form that will allow editing or lock all fields, so that records are protected from accidental edits.AllowEdits in the form is set to False by default.I've used the below code to allow a user to unlock the form:
Private Sub cmdEditRecord_Click() If Me.AllowEdits = False Then Me.AllowEdits = True Else Me.AllowEdits = True End If End Sub
And this works without issue.However, I want to create a second button to lock the form again.
Private Sub cmdLockRecord_Click() If Me.AllowEdits = True Then Me.AllowEdits = False Else Me.AllowEdits = False End If End Sub
Seems logical that I should just be able to reverse the true/false but this does nothing!I've also tried the below code:
Private Sub cmdEditRecord_Click() Me.AllowEdits = Not (Me.AllowEdits) End Sub
To toggle between editing/locked, with the same results - ie, can switch to editing, but can't then turn it off.
I am using the following code in a textbox (CountryID) to show a country name (CoName) from a table (tblCountry). If the comboBox (cmbCoName) in current form (frmCountry) has no entry then CountryID remains blank, great. If I use the form to add a new entry then CountryID flashes because it does not recognize the entry. Can I use "or" to add another condition in the IIF statement so that it allows the "not in the combobox list" entry?
When I build the statement I want to email and view it in a MsgBox the lines space correctly with the code vbCrLf.However when I send that text out as the body of the email, the spacing is no longer there and its just one big pile of nonsenses. In the MsgBox I see it correctly as:
Tool Box 5 Drawer 3 Tag Number 4 Drawer 2 Tag Number 6 Tool Box 7 Drawer 8 Tag Number 2
But in the Email it comes out as: Tool Box 5 Drawer 3 Tag Number 4 Drawer 2 Tag Number 6 Tool Box 7 Drawer 8 Tag Number 2
SELECT tblClientDetails.FirstName, tblClientDetails.Surname, tblClientDetails.MobileTelephoneNumber, tblClientDetails.SentTextMarketing FROM (tblCategories INNER JOIN tblItems ON tblCategories.CategoriesID = tblItems.CategoriesID) INNER JOIN ((tblClientDetails INNER JOIN tblOrders
[Code] .....
this does not allow edits on a continuous form. the edit i am trying to do is the check box. it will allow edits if the grouping is not on but if it is not on then i will get many duplicates of client details.
so how do i set it up to allow edits but still only get one instance of each client detail.
I am trying to use a combobox called Manufacturer to select which table the combobox called Model gets it's rowsource from using the code below.
Code:
Private Sub Manufacturer_AfterUpdate() If (Me.Manufacturer.Value = "Siemens") Then Me.Model.RowSourceType = "Table/Query" Me.Model.Recordset = "SeimensTable" Me.Model.RowSource = "SELECT Model FROM SeimensTable" Else If (Me.Manufacturer.Value = "Samsung") Then Me.Model.RowSourceType = "Table/Query" Me.Model.Recordset = "SamsungTable" Me.Model.RowSource = "SELECT Model FROM SamsungTable" End If End If End Sub
But when I run the form and select Manufacturer. Combobox Model remains empty. tell me what I'm doing wrong?
I need to know if I can store data in more than one field, without allowing Duplicate entries.
Example: We have to send out letters with a claim number on it, sometimes we need multiple claim numbers on a letter, up to about 10 claim #'s is the most I've seen thus far.
We mail merge into Word to create the letters.
However, these claim numbers cannot be duplicated within the database and in order for this to work with the mail merge, I need several fields to store the claim numbers.
To be more specific. I probably have a bad data structure as I was kind of rushed and a little inexperience with putting the database together. It's for work and sometimes management just doesn't want to wait, they are so negative.
In brief, most everything is in 1 table and I know from reading this forum that I shouldn't do that, but that's another story.
Further, a claim# is tied together by the same claimant, the relationship code is what separates the claimant with the family, the insured id# is used for all claimants within the family.
You can also tie the claim# to a provider id#, some providers such as clinics and large hospitals have multiple providers that use the same provider id#, so a suffix code is used to separate the providers within that same #.
Please feel free to ask additional questions and I will give you the additional information you need to help me. Thanks again.
P.S. - Last year I created the database and this year I am improving it, so expect more posts from me in the future. I really appreciate all the help I get from everyone here!!!!
I need to have a query-based form open and allow users to check off (or otherwise indicate) which records they want to update.
I tried having a check box on a continuous form, but when you check one, you check them all. I did a search on this, and saw that if you make the check-box bound, then that will work. But I don't know what to bind it to. I don't want to add another field to an already large and complex table, and I don't know where to put it if not in main table.