Populating A Field Automatically OR Allowing Manual Entering Of Data
Sep 26, 2005
I have a simple table with two fields in that table called:
1. BankName
2. BankNumber
Each bank has a bank number. For example Bank XYZ and all of its branches have the same Bank Number 123. There are 5 banks I have listed in a combo list under the BankName field. I can also type in a different bank in that same field if it is not listed in the combobox list.
Now, I want the BankNumber to automatically populate based on what I choose under BankName. If the BankName is manually entered (for banks that are not in the combobox), or if the BankName field is blank, I want the BankNumber field to be able to enter a number manually.
For example, if I go to the BankName field and under the combobox I select Bank XYZ, i want the BankNumber field to automatically populate as 123. If the BankName has a bank name that was manually entered, i want BankNumber field to allow me to manually enter a number.
Thanks for ur help. I couldn't figure this simple request out.
I have two tables, "Membership" and "Class." The Class table has a class ID, class name, and cost of the class.
In the Membership table, I have a field to accept a class ID that is entered by a user. I also have a column called Tuition. When the user enters a classID in the Membership table, I would like the dollar amount associated with that classID to automatically appear in the field called Tuition in the Membership table.
I tried the LOOKUP data type and calculated fields, but no luck. I know that DLOOKUP can be used on forms, but I don't see how it can be used directly with tables.
uh.. I guess the title pretty much sums it up... Is there a way to enter data in a text box or something once, and have it applied to all the filtered records?
I have it set up so that we can sort by project number, and it displays only the invoices that havent been assigned to a bill (we recover the expenses form our parent company). I dont want to have to enter the same bill number to each of the filtered records individually.
I was assigned by my manager to design an Access database system that is able to import all data from excel file monthly and creating charts & tables to analysis how each sales people and industry perform.
We originally have a big excel master sheet that has more than 10 sheets. I tried to import the current excel into access, but then i realized that this is not gonna work. because for next month, there will be new data and I can't do the whole import process over and over. Plus, after this system is designed, the users will be someone who has no knowledge in access, so i need to create a user-friendly system for them to use.
My questions is:since the data is always cumulative number, if I imported current excel file into access, when the next month comes, how to update the new data into excel. p.s. EXP. Mike's sale volume is different each month, and with the access system, for that column, it will be a cumulative number, like the total from the month of November to this month. how do i achieve this kind of update/import goal?I tried to link the excel to access, but by doing that, I will not be able to set relationship or change the attributes of any data type in access.
I am creating a log in form, it checks the user name which is unique. if the user has entered a password, it shows only one password text box, they enter the password, if it is correct they enter the database, otherwise they return to the text box.
But if they have not entered a password before the form opens with two text boxes, one for the password and one to confirm the password is typed correct, if the are different a message box shows telling them that they are different, now is where i having problems, when they have typed the two passwords and they are correct i want them to save this password in the same record "Password" as the selected "username" record, I can find the "username" record by doing a Dlookup, easy, but i am stuck how to then save the password from the text box where the selected username record is.
My table "staff" has fields of "IDStaff", "FirstName", "Surname", "Password", "Username".
Can anyone tell me what code I can use to manually enter data from a textbox. I don't want a bound box, but want to write some code that will take the text from that text box and enter it into a field in a table when i wish it to.
im having difficulty adding data into a table which i created, i want to use two or more of the same Student_ID's into one field, while adding different data into another field Subject.
I have a "Currency" field in a table and it holds large numbers (on which I will need to do basic arithmetic operations at some point, so I must store them as a numbers). I have set the "Standard" format on the TextBox used to display it on a form because I want to have thousand separators; I've also set "0" for decimals as I don't need to display them.
So everything displays as I want it even with the largest number that can be stored all the decimals are showing in the text field, but when I click on the field to edit the value instead of having the complete value it is displayed as scientific notation. I would like to display the complete value when editing it instead of the scientific notation, how can I achieve this, if it is possible?
A numeric example: If a user enters 1234567891012, the value displayed is indeed 1234567891012 but if they click on the field again the displayed value while editing is 1.23456789012E+11 (and it switches back to normal notation when the focus changes to another field). I would like to show 123456789012 all the time. I know that Access is capable of it most likely because if I set the Format to "Fixed" the values are always displayed completely (no scientific notation), but unfortunately I would like the thousands separators to show and it is not possible with the "Fixed" format.
Two last details, the scientific notation while editing does not kick in unless there is more than 11 digits in the number and the field width (and/or TextBox width) are sufficient to display up to 20 digits.
I'm new to this. I'm trying to enter data (it's actually Latitude and Longitude co-ordinates) from an existing Excel source into an Access database which has input masks of 00°00'00.00"L;0;0 (Latitude) and 000°00'00.00"L;0;0 (Longitude) in the respective fields. However I cannot get the information to import or display correctly. I did an "export data" of the respective table (hence fields) to Excel to try and get the correct entry format. An example of the Lat exported was 24°49'41.81"N and Long was 067°01'44.02"E (but with a very small ' in front but only visible in the data entry line in Excel, not in the actual spreadsheet table???)
However when I try to enter the data (even using the exact same little degree symbol, apostrophe, and quotation marks) it does not enter the access fields correctly. On closer scrutiny of the exported Excel format I note a small ' at the very beginning of the 24°49'41.81"N or 067°01'44.02"E string. But as I said previously only visible in the data entry line next to the formula button. Not on the spreadsheet cell.
However even when I "Paste Special" "values only" my new co-ordinates into the same entry location as one exported, it will still not import, or display correctly. If I go into the Access database directly there is a form where if I need to enter the new co-ordinates (using lat example above) I only have to enter 24 49 41 81 N (spaces between) and it will show correctly as 24°49'41.81"N
I'm getting desparate as I don't want to have to change all the details manually. Anyone know what my correct format from an Excel spreadsheet should be?
Apologies for lengthy story! Difficult to describe problem with degree symbols etc
I have Two tables one table has a Master list of products (ProductMaster) with a Unique product code (ProductMaster.UQ_ID). The second Table has Order data. The Second table (OrderTable) is where I will enter the product code (UQ_ID) which is the unique Identifier which is also in the ProductMaster. As soon as I enter the Prodt code in the OrderTable I should have all the related fields filled in from the Product Master.
Columns in The ProductMaster = Product_ID, Product_Manucaturer, Product_Weight, Product_Color, Product_Size
Columns in The OrderTable = Product_ID, Product_Manucaturer, Product_Weight, Product_Color, Product_Size, Order_Date, Ordered_By
The columns marked in red are the ones that will have to be automatically fetched from ProductMaster
The data in the second table should be populated from the ProductMaster as soon as I enter the Product_Code. The rest of the details Can be filled manually filled in.
Is it possible to do this with Access. I have tried different expressions in the Fields without much use. The Documentation from Microsoft is very weak. If someone knows a solution or where the documentation is, please help.
The situation is that I have a table named patient and a form named [patient1] where it holds a textboxs named "MFC_Seq_no" and another to hold [date], now, I manged to do a manual auto numbering for "MFC_Seq_no" using the following VB :
Private Sub Form_Current()
If Me.NewRecord Then On Error Resume Next Me!MFC_Seq_no.DefaultValue = Nz(DMax("[MFC_Seq_no]", "Patient"), 0) + 1 End If
Whenever user create new record, the system will automatically store the next number in the new record.
Now, I've be requested to code that system to start again from 1 as value for "MFC_Seq_no" whenever there are changes in date field (i.e. next day in the date field)
Is this doable and how?
Q2. how to duplicate an entire record with a subform, from another table, in it?
I am having trouble with our receiving database. This database consists of two tables. One for vendors, which basically contains their vendor ID as well as vendor name and phone # etc. The other table is our receiving data table. When our receiving person receives product in, they log this in the receiving table including info such as date, autonumber for record, vendor etc. The problem is, when the person selects the vendor id, which is set up as a lookup field, we would like to have the vendor name pop up atuomatically within that record. I cannot for the life of me figure out why this isn't happening. The person is using a form to enter all of this data and runs a summary report at the end of the day. Our accounting dept. is requesting this info be added, but I cannot seem to figure it out. Thanks so much for any assistance! Amy (monet1369);)
I have a form (Access 2010) that we use to keep track of client data. When I enter a number equal to 6 or above in one field (Client Rating and this field is actually formatted as text because it could be 6 or 6a, b, c or 7 or 7a, b, c, etc), I want anything =>6 to automatically check a box in another field which is the Watch Indicator box (a yes/no box), how would I do this?
I tried to create an expression like the below on the Watch Indicator check box and it does not work - not sure why - it did not say it was incorrect, I think I may have it in the wrong place?
Some days ago I made it by using "query", but now I forget it how I make the relation on this situation. Here is my problem.I have 3 Table on mdb file, named Table: A, B, Status.Table Status have One Filed with 1 Data: Dishonor
Table A have three fields
Sl Number: (Auto Number) Status: Lookup wizard-data of (Table-Status)-Default Value is "Honor" Amount:Number
Table B have two Fileds Sl Number: Number Status: Lookup wizard-data of (Table-Status)
After Entering Some data on Table A it's Look like as:
Asl numberstatusamount 1Honor5222 2Honor855 3Honor988 4Honor7777 5Honor777 6Honor9999
[code]...
Now I want to change the Status of SL Number Honor to Dishonor so I fillup data on Table B is as like following
sl numberstatus 5Dishonor
Now how I can get the result as following by using query:sl numberstatusamount 1Honor5222 2Honor855 3Honor988 4Honor7777 5Dishonor777 6Honor9999 7Honor6666 8Honor7777 9Honor666
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 table with 715K records. Each record is an inventory product, and the sixth field of each record is a short description. The tenth field is a single-letter category designation, and is currently blank. What I want to do is search through the description field of every line, and where the word "Paint" is contained, enter a letter "P" in the category field.
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
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?
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 have one field AccountName in customer table and another field AccountID.
In my form I would like to select from the combo box AccountName during data entry and then have the AccountID automatically update in the Account ID field.
