Using Two Fields In A Form To Generate A Third Field
Oct 9, 2015
I've got a form where I want to add an employee and I have three fields (EmpFirstName), (EmpLastName), and (EmpCode).They all have their own control source from a table but what I want is EmpCode to automatically fill in after they type a first name and last name and not be editable. Smith, John and the empcode will fill in with smijo (3 of last and 2 of first). I understand this is probably completed using Expression Builder (not sure on the command) and how do I make it so Empcode still receives the data entered??
Ok so I want to generate a subreport for my deacons that show what families have been assigned to them.
Using the wizard I created a subreport that pulls the name & phone number(s) for each member with the same family ID, and Deacon ID (which pulls the name, number and FID from the member table, then checks the family table and gets the deacon ID). My question is, how do I tell the report to check if the current member I'm looking at is a deacon and if so use their deacon number as the reference number for "Deacon ID"?
I have a table for my deacons that links the member through their EnvNum (if they're a Deacon). Not sure if I'll need that table for this, but thought I'd mention I do have one.
I have a database that will take lots of data entered by an employee and calculate some additional data and generate quotations. Within each quote there is a possibility for 15 different metals (5 Precious Metals, and 10 Base Metals). There is also 5 fields that need to be filled out about each Metal (What the metal is, the market being used, weight, whether it is included in a different price, and the price). I currently have 75 fields to address each Metal and their 5 fields respectively. Is there a better way to Normalize this data, and accomplish what I need accomplished? I want at the form level the employee to tell the database whether they want to add a Precious Metal, or Base Metal, or Move on to other data entry.
Here is a Screenshot of the design view of one of my tables with too many fields : table screenshot1.PNG
I have an ID field that I want a custom ID generated based on Name, Phone Number & Record Number.
Here is the code I'm currently using....
'User moved out of supplier name field Public Sub SupplierName_LostFocus() If Me.NewRecord = True Then 'If user adidng new supplier SupplierNameTemp = UCase$(Left$(SupplierName, 3)) 'Store 3 characters from supplier name End If nd Sub
'User moved out of suppliers phone number field Public Sub SupplierPhoneNo_LostFocus() If Me.NewRecord Then 'If adding a new supplier supplierPhoneNoTemp = UCase$(Right$(SupplierPhoneNo, 4)) 'Get 4 right characters of phone # End If End Sub
I then have a procedure which combines the two strings such as....
Private Sub GenerateSupplierId() SupplierID=SupplierNameTemp+SupplierPhoneNoTemp End Sub
The problem is when I check the SupplierPhoneNoTemp & the SupplierNameTemp within the GenerateSupplierID procedure. The contain no information meaning the SupplierID will have nothing as well.
However, when I check SupplierNameTemp within the Public Sub SupplierName_LostFocus() & SupplierPhoneNoTemp within Public Sub SupplierPhoneNo_LostFocus(). They both contain the proper info. It seems they loose this information when moving outside their procedures.
Does anyone know why this would happen & how I could fix this?
First off, I want to see if this is possible. It seeems like it should be but sometimes I dream bigger then things allow.
OK... I have a form made in Access. There is a text field that I would like to be able to do the following:
If the field is null, a number will be randomly generated betwen teh values of 1 and 1500. If the field is not null (we entered a number between 1 and 1500), then that field would be set to that number. The field CANNOT have the same number given (we will only have 1500 records and want them randomly generated a number unless otherwise specified).
Can this be done? I made my attempt but it keeps failing no matter how I try and fix and such. I guess this is what happend when you haven't used Visual Basic in about 4 years.
Okay I have a report (rptHorneOstbergQuestionnaire) that is based on a query (qryrptHorneOstbergQuestionnaire). In the report I have a total (HOTotal) which is the result of an expression created in the qry. Based on this result I would like to generate text in a text field (HOType) that is found in the same report.
Basically if the field HOTOtal shows any vaue between: 16 and 30 then I want the unbound text field to show the text Definitely evening typw and so on ...(see case statement below)
I thought I could do a case statement on report open but I am getting an error that my expression contains no value.
Here is the case statement
Private Sub Report_Open(Cancel As Integer)
Select Case Me.HOTotal
Case 16 To 30 Me.HOType.Value = "Definitely evening type" Case 31 To 41 Me.HOType.Value = "Moderately evening type" Case 42 To 58 Me.HOType.Value = "Neither type" Case 59 To 69 Me.HOType.Value = "Moderately morning type" Case Else Me.HOType.Value = "Definitely morning type"
I have Table called "Products" Field 1= "Product ID" which is a text field (PK) but numbers are used (ie 1 -20) Field 2= Products -showing our list of 20 products
When I enter a new product, currently I have to look in the table to find the last ID used then use the next one available. I have created a form to be used for data entry to enter new products
What I am trying to do is :- 1, have the form open at data entry level but still able to scroll and see all records and 2, Have the form auto generate the next number available. For example, I have 20 products entered so when the form opens to enter a new product, the ID is automatically at number 21. I know absol nothing about coding. I have looked at other suggestions on the forum incl the DMAX +1 and having tried and not worked, I wondered if its because the field is a text field or I am trying to insert the code in the wrong place.
I Have a table with 15000 entries. This table lists components we use. This table is used to generate queries/forms. I have been asked to add a hyperlink to this table to link to a drawing of the component. All the drawings are in PDF and in the same folder. I am looking for a way to automatically update the hyperlink fields all at once. The Hyperlink will be in the format of servershareddrawings12345.pdfwhere 12345 is the component name from the table.
But there are a few small problems with the component names. 1. If the component begins with a B- . The drawing name will be all the characters except when there is a second dash in the component. So if the component is B-12345-678 the drawing name will only be B-12345. 2. If The component begins with AB, The drawing will be the first 5 characters regardless of what follows, e.g. AB123. 3. Finally for all other components the full component name will be the drawing name.
Putting the naming to one side, how to go about auto generating the hyperlink. I have posted this in the table section, but maybe this should be in another section like queries or VBA.
Table called "Products" Field 1= "Product ID" which is a text field (PK) but numbers are used (ie 1 -20) Field 2= Products -showing our list of 20 products
When I enter a new product, currently I have to look in the table to find the last ID used then use the next one available. I have created a form to be used for data entry to enter new products
What I am trying to do is :- 1, have the form open at data entry level but still able to scroll and see all records and 2, Have the form auto generate the next number available. For example, I have 20 products entered so when the form opens to enter a new product, the ID is automatically at number 21.
I wondered if its because the field is a text field or I am trying to insert the code in the wrong place.
Autofill text field using combo box selectionHello,
I'm trying to autofill a field on a form using MS-Access 2010 Web version sharepoint using macros without coding.
The form has an existing Combo Box field called Segment, using the row sources "REW", "WTT", "DBM".
A user begins filling out the form and eventually selects the Combo Box for Segment and selects any one of the segment no Multiple selection
There is another text field, which is actually alpha-numeric, called Job_No. I want this to autofill based on their Segment selection, using this format:
REW-YYMMDD-TTTT, or REW-131210-20001 for example, for today with the time displayed as military time and last Job No.(20001) for REW and Also for WTT (30001) as starting number.
Fields will include: TransactionDate ProductID ProductName UnitPrice Quantity SubTotal Total AmoundPaid Balance
The subtotal= unitPrice*Quantity Total = Addition of all subtotal AmountPaid = How much each customer paid Balance = AmountPaid - Total
fields the TABLE should contain? (Remember customers will purchase more than one product & there will be more than one reciept)..How I can generate a REPORT using a FORM ? The REPORT should contain the calculations. How do I make access do the caluclations?
I am trying to set a form that when you add a new report it will auto poulate with a number like this S44/13/001. I have not had formal training in Access.
I am tring to generate a report or form, its generated by a combo box, which list all the categories of the products, when the category is clicked on it will generate the report or form showing all the products for that particular category. Can anyone point me in the right direction? Any help would be appreciated.
I am trying to see if the following is possible. I have created a form in my database that tracks how many thank you letters I have sent out with a "check for yes" box.
On records where there is no check I want to create a button beside that says "Print Thank You Letter". It would then generate a form based on that certain customer's information.
Is there a way for me to do this? Also, will the report automatically generate that person's information found in the record?
these are combo selection and the report is working fine with these 3 filters. filtering by a query. all 3 feilds on the same table.what i am unable to do is make these filters as option to select with a check box. like if i dont want the third filter product but to generate report with the other two filters sales_person and client_name.
edt: uploading my DB Form report_generator on medical_request_query and table medical_requeset
Using a table with employees, I created another with equipment that uses a lookup to assign each piece of equipment to an employee (more than 1 piece of equipment can be assigned to each employee)
I want to be able to select records using a form, either by checkbox or listbox of which employees to include in the report that shows what equipment each is assigned. The problem I am having is creating the form/code to create the and/or query to generate the report.
I have been creating an inventory control system for my small business. I am looking for a code that will generate to another form. So for example if I take out 4 aprons from the first row by hitting the '-1' button 4 times leaving 46 in total how do I generate the information from one form to another by hitting that submit button.
My other form shows Product ID, Date Taken, Product Description, Employee who has taken it, Amount Taken, and Total left in inventory. I need the correct information to show accordingly in the Inventory control form.
Please be kind, i have little VB Knowledge, and wish to expand my learning on this topic.
I have a form that updates information on one table, and has a subform displaying info from another table.
the subform is filtered, and only shows data from what is specified from the filter of the main form.
If I update information on the main form for instance, field 1, (the data on the subform has the same data so there is the relation), how do i update the subform by only updating the mainform? can this be done through some VB or something?
if you folks out there can give a Smidget of info on which VB codes i can use i can pretty much figure it out.
I have a calculated field that shows the sum of two other fields on the same form. I've got as far as making the expression work but it doesn't quite do what I want. I need the calculated field to update when any one of the reference fields are updated (i.e. currently it only updates once all the fields in the expression are updated).
How to I change the form so that the calculated field updates based on current values (including any fields that contain no value)?
I'm trying to track daily production at a manufacturing company running many different processes at different locations each running multiple parallel "lines." The tricky part is that the number of lines running and the shift schedules e.g. 2shifts 10hrs/day 4days/week or 3shifts 8hrs/day 5days/week change frequently for each process.
Right now I have a form for process data that specifies the schedule and number of lines running each day. Then I'm running an append query to a "production" table that generates blank production records associated with each item made in each process for the correct shift/line combinations.
Up to now I've been manually changing the date on the append query each time I run it. Then I have a seperate query for each process that pulls out the production records for each day. My problem is that the preferred interface for production data entry is a spreadsheet with the following layout:
--------DAYS LINE 1 DAYS LINE 2 SWING LINE 1 SWING LINE 2 ITEM 1 100 ITEM 2 2250 ITEM 3
which changes each time the shift/line schedule changes for each process. The only way to achieve this layout I know of is a crosstab query which isn't updateable. Ideally, each day the manager will specify the shift schedules and forms will be automatically generated with the correct structure and sent to the process supervisors. I'm open to different form layouts and even redesigning the database completely.
I'm trying to lock certain fields on my form based on the value in a particular field. It seems to work the first time but applies itself to all other records in the database from then on!
The starting point is a command button that sends an email and makes Me.DPLLock = 1. The corresponding table entry is formatted as a number.
The code is...
Private Sub Form_Current() If Me.DPLLock = 1 Then Me.OR_Name.Locked = True Me.OR_Sales_Order.Locked = True Me.OR_WO_No.Locked = True Me.OR_Qty.Locked = True
I want to calculate the average of 16 anodes from text box into the "average drop" box and simultaneously want the data to be saved in the table too as one of the fields. Also, How to load form view while the database loads?
I have a form called PAF_Assignment and on this form there are many textboxes for editing. After all fields are entered, the user clicks on a button and this button populates the PAF_Issued_Date field with the actual date.
Then I want to disable (enable=False) all fields on this form once there is a date on PAF_Issued_Date as I don't want any user to make any changes after submitting it, although the user would still be able to read the information submitted.
I have tried the following...
PAF_Assignment Form AfterUpdate Event If Me.PAF_Issued_Date=Not null Then Me.FieldName1.Enabled=False Me.FieldName2.Enabled=False Me.FieldName3.Enabled=False
[Code] ......
However this is not working, there is no error message or anything but the fields remain enabled with the date...
I am trying to auto-fill address info from a combo field. I'm using a select query on a table for the information. It appears to work for the first field (Firm Address1), but then stops working on all the other fields.
