Can you update a text box on a form based on input of a date control? Here is what I have. I have several date controls on my form, and I want to update a status textbox based on which of these date boxes is filled out, but I need the status to be filled into the table as well. Do I update the field on the table, and then requery the text box? Is there some other method for accomplishing this?
View RepliesI have a form that our operators use to do their hourly quality audits on. This is getting to be a huge burden on them because right now, they enter the date, the week ending date, the month every time they have to do an entry, and for me it is a nightmare because they can still enter the wrong information. So, what I was wondering is if there is a way that I can have them select the date from a combo box (easy enough), but from that, have the week ending date and the month fields automatically update as well. Any advice? I would really appreciate it! Thanks so much!!!!
View 3 Replies View RelatedI'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?
I have a form in my front end database that is supposed to allow a user to search for a record based on account number and then make changes to the that record and for it to save in the back end database. I have the search function working where it populates different input boxes on the form with what is stored in the back end database but I cannot get the update function to work. I have tried to assign each input box with a variable and then run an Update SQL function to update each of the fields but the updates are not storing. Any example of a successful update statement that uses VBA variables in it or a way to update a specific record via a recordset type function?
View 8 Replies View RelatedI'm attempting to build an import module so that my users can take data from different walks of life and import it into my tables. I'm doing this by setting up a module and allowing the user to specify which column data will come from when importing data. So lets say I have a field that is CustomerID in my table and user one pulls data from 1 place and in his excel or csv file, customerid is in field 1. Another user does the same thing and its in field 2.
How can i make some form of a loop that when I'm attempting to update data, i pull the correct column?
strSQL = "SELECT * FROM tblImportTable"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
With rst
Do While .EOF = False
If IsNull(DLookup("DefaultValue", "dbo_tblImportTemplateDetails", "Template_ID=" & Forms!frmImport!TemplateName & " AND FieldName='CustomerID'")) Then
[code]....
I am new to VBA and I'm trying to write a query that will update a table with dates based on user input. For example a user will run data each Monday and that date will be day01. The date table has 28 days total and I need each day row to update with the next date i.e. day01 is 12/30, day02 is 12/31, day03 is 1/01 etc..I am having issues just running the update the query. I get a too few parameters error message on the strsql statement. There are only two columns in the table, order_day(date column) and date_value(text). I want to update order_day. I also need creating a loop so it knows to go back and add days to the other values.
Here is what I have:
Code:
Sub Update_Dates()
Dim rs As Recordset
Dim db As Database
Dim lmsg As String
Dim transactiondate As Date
Dim strsql As String
[Code] ....
I have a continuous form (2003) with 6 text boxes (StartTime, StopTime, Comments etc...). I would like all the text boxes to have a gray background if the StartTime for that row is less than Today().
View 5 Replies View RelatedI have a database with the following information:
FTA # Date Attended Last name First name assigned person......
Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.
Is there an easy way to type in the date that I want the query to use as the criteria?
Thanks
:o
I'm trying to pull a date from a table into a text box on a form tab control using DLookup and I just can't figure out what is wrong with my DLookup expression:
=DLookUp("DateOrdered","tDateOrdered","PrNumber=" & [PrNumber]) ----- (DateOrdered is short date, PrNumber is text, db is split Access 2013)
Whats missing in this expression? I've tried every criteria variant I could find but to no avail.
I have query in MS access which does date filter based on value given by user.
SELECT *
FROM Table1
WHERE ((DateValue([Table1].[Date_col])
Between "#"&[Forms]![Frm1]![Date1]&"#"
and "#"&[Forms]![Frm1]![Date2]&"#"))
But this gives empty output
I am trying to export records to excel that are in a given month based on an input month. The user would select the month with the records desired and click the button to export them to excel. The attached zip file has an image of the database and the code behind the button is:
Code:
Private Sub Command33_Click()
DoCmd.OpenReport "HLA_TAT", , , "Len(Exception & '') > 0 AND Receive_Date > #" & Forms!HLA_TAT.Date & "#"
End Sub
[CODE] can not access referenced form HLA_TAT [?CODE]
Good day... beginner Access user (converting from Lotus Approach) and need some help please. Please excuse my novice... I'm on a deadline at work and need help:
-- Data source: MS Excel Table
-- Database form: MS Access 2003
My form is using the excel table as its data source. There are two fields: TELEPHONE AND FAX
When I try to use the input mask in Access to view the numbers in TELEPHONE FORMAT, it keeps telling me the input mask works only on text or date fields. Do I have to first modify the field format in excel? What does this error mean?
Making a small database, Got 1 Table.
1. ContactDetailTable
Got 3 forms.
1. ContactIDForm
2. ContactInfoForm
3. NewContactFrom
In ContactIDForm it contain 1 textbox name 'TextBox' with Button Name 'Btn'
In ContactIDForm there is only 1 Text Box ContactIDTextBox and 1 Button. User Enters ID in TextBox and On Button Click Event it should check data from TextBox in Table name (ContactDetailTable) in field ContactID and if there is record matching, ContactInfoForm should Open else NewContactForm should open with ContactIDTextBox value in it.
I would like to change the text formatting (color, italics, bold etc) of the contents of a control based on a boolean value in the underlying datasource of the report.
For instance, I have a report that generates a "Proforma Invoice" i would like to ability italicize the prices of certain items based based on a boolean value (EstimatedPrice) in the underlying datasource.
I have an unbound control in data input form requiring to input a 6-digit number. I have put a validation rule restricting more than 6 digits. Most users prefer to enter, say 123 and the system can enter the 3 leading zero for them.
View 6 Replies View RelatedI'm creating a database for work, and one of my forms currently has 8 subforms on it. For each individual account there can be one, none, or many of any combination of these subforms that will have information on them. I need the form to be able to show all of them, as someone else will be inputting all the information when the database is complete, but to make it easier I have set it up with checkboxes so that when a checkbox is checked, a subform shows, if not checked it doesn't show. I have very limited skills with VBA, so that in itself was an accomplishment for me.
My questions is: is there anyway in access to do it so that if a subform doesn't show, all the ones beneath it that do show will get bumped up so that it is easier to read/input data?
I have attached my DB and the appropriate form is "ICinfoT."
I'm using I have a secondary control being populated by an entry from another control. I was able to find the code I needed to do this using a combo box here in this forum.
me.txtDependent=me.cboPrimary.column(#)
(specifics changed to protect my company's paranoid view of privacy)
the code itself was showing up in the secondary control instead of any value. I found though that moving the code from the On Change event for the primary control to the On Current event for the form itself everything is working beautifully.
However, I have a different task now that I need to be able to accomplish with the same two tables but in the opposite direction. I suspect that I'm going to need to create a second set of controls and maybe even a second relationship or table to do this. To use a set of specifics that I think will get this point across, if I had a master table which includes a field for "City" and a secondary table, CityState, which contains both "City" and "State", and two controls, a combo box cboCityState to look up the specific City and a text box, txtState to display the State, putting the following code in the City combo box's On Change event populates a text box with the State when the specific City is selected, or putting it in the form's On Current event will be sure that State shows up in all the copies of a form when the form is used in a search and the City is not changed :
me.txtState=me.cboCityState.column(2)
What I need to do now is to set up a control which allows me to type in the State and have the results show all records that include that State.
I'm using this in a Filter by Form environment. I know the objections to this method and the benefits to creating a dedicated search form, but this method appears to be working well in all other ways for us for now. I have several situations within this form where I have one control showing up at the initial load of the form and at the beginning of the filtering process, allowing selection of multiple values from a long list of values in a list box, for instance, (using the On Filter event) and other controls showing up as a result of the filtering process, to display the selected criteria only, for instance (using the On Apply Filter event), so I'm aware of how to make this work if that's required. I'm thinking I may need to create a second control, something like txtStateInput, to accept the State search criteria and another, something like txtCityResults, to show the cities that are part of the resultant records.
I want numeric only input for a certain textbox. I can use a ISNUMERIC function to test this, but prefer to use the Keypress event. So far got the following:
Code:
Private Sub txt_Position_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case 8 ' backspace
Case Asc("1") To Asc("9")
Case Asc("-")
If InStr(1, Me.txt_Position.Text, "-") > 0 Or Me.txt_Position.SelStart > 0 Then
[code]...
It is working alright, except I want to avoid input that has multiple leading zeros, such as 00000 or -00000 (i.e. minus sign with mulitple leading zeros).
I have a TABLE with the following data:
- EMPID
- LASTNAME
I have a form with the folliwing data:
- text box for user to enter EMPID, txtEMPID
- LASTNAME
I would like to create a button to initiate a query to do the following:
- once the user enters a EMPID in the form, it will search in the TABLE under the EMPID field...
- if the user enters an EMPID that is in the TABLE...display "Y"
- if the user enters an EMPID that is NOT in the table...display "N"
- a error message box needs to pop up if "Y" to alert the user that "the EMPID already exists and that duplicate entries are not allowed"
One of my main questions is how do I run a query based on the users input and search a table? would the following work in the query?
I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).
I cannot seem to define a form based on these relationships to input the data.
I'm trying to apply a custom input mask to a text box control. The text box must contain a 43 character string, broken into sections as follows :
AAAA-AAAAA-AAAAA-AAAAAA-AAAAAAAAA-AAAAAAAAAAA-AAA
(The format is quite strict and always comprises alphanumeric characters , hence I figured an input mask would, if nothing else, support the user to key the string accurately from its constituent parts...)
However, when I try to apply the input mask to the text box, I seem to run out of characters - I get as far as the below but the input mask wizard won't let me extend the mask to the final 8 characters
AAAA-AAAAA-AAAAA-AAAAAA-AAAAAAAAA-AAAAAAAAAAA-AAA
Are input masks restricted to 35 characters in length? And if so, how else could I make this, admittedly complex, text input more manageable for end users?
I have created a table for installer invoicing, and have a field for invoice amount. I have created a user input form that allows a user to fill in certain pay rates for different aspects of installation, and would like to know how to make the invoice amount a calculated control that will auto update the field InvoiceAmt in my table. I can't figure out how to do this in the property sheet.
View 3 Replies View RelatedI want to be able to have two text boxes on my form that firstly concatenate the values inputted and then saves this string to a field.
It's a "new record" form that generates a new record in table "tblUsers".
txtB1 & txtB2 and the text boxes would represent First Name and Last Name.
But it can't be allowed to be saved as the acctual names. - Insead I'm looking for a way to take the first two or three letters of the name and concatenate these into one string of text and save that string to the field tblUsers.ConcatenatedName.
I.E.txtB1:"Carl"
txtB2:"Chapelle"
would generate "CarCha" and save that from the form to the field ConcatenatedName in tblUsers.
Btw, the values form txtB1 and txtB2 (Carl & Chapelle) cant be saved anywhere either!
Found the answer myself;
create a txtB3
And set that source to Left([txtB1];3) & " " & Left([txtB2];3)
Is there a way to setup the format on fields to only allow text inputs, no numbers wanted. I know I can do this at the table level but is some cases numbers are ok. So I want to code it so I can control when numbers are ok and when there not.
View 1 Replies View RelatedI have a report that selects and shows records where a specified date field is within the range of 2 dates that the user enters.I created 2 unbound textboxes on the report with a Shortdate format and InputMask 0000-00-00;0;_.When user enter correct dates, then everything works fine: selection is properly done, the right records show up.But I have 2 problems:
1-the input mask is not working: the user can input anything!
2-the 2 unbound textboxes do not show the dates entered by the user.
It seems the value entered bu user does not go straight into the unbound textbox. How do I either intercept the value entered by the user directly into the unbound textbox or via a variable?
I have a date field on my form and used
Input Mask L<LL-0000;;* and
validation rule <=Date$()
I want the user input to look like 'Aug-2010' for example.but I get error: The value you entered isn't valid for this field