I would like to use code to look at each field in my form (they are all textboxes) and if it is blank, fill it with "888". I have read enough to know I should use the NZ function, but can someone tell me how it should be written to look through every textbox on the form?
View RepliesHello everybody,
basically what i'm looking for is a method to search a field in a database that i'm not able to find on the net. what i'm trying to do is create a search funtion for my dvd collection.
i have text box and listbox(4 col).
i like to do a search as i type in the name of the dvd and list the results in the listbox as i type more and more characters the list box display them narrowing down the results.
example, as i type t, the function should list everything starting with t in the listbox.. if i type te, it should do the same display everything starting iwth te. and the same as i type temrminator....
database field name is dvd_title, to be search. and once it finds or as you type the informatoin it should displayed in the listbox col1= title =field1(DB), col2=year field2(DB), col3=actore(DB), col44(DB) subtitle..etc
is this possible and if so can someone help me implement this.. thanks
robin
Hi,
I'm having some trouble with a report I am responsible for. I'm novice to Access, so if you could please explain in detail...that would be great.
Well, I am supposed to create a report, that will pickup data from 1 table (Passport_details), and display them in a report. Within the passport Details table, there are 3 fields (Scrap, Resale and RTS). For each record, their will only be a number in one of these fields. For example:
Product status: **here is where Scrap, Resale or RTS goes"
PassportID Product Scrap Resale RTS
123456 Battery 9 0 0
987654 Pots 0 7 0
456789 Knives 0 0 3
What I want is a textbox or label at the top that will state "Scrap" if there's a value other than 0 in the Scrap Field....."Resale" if there's a value other than a 0 in the Resale Field, and "RTS" if there's a value other than a 0 in the RTS field. There isnt occurences where their may be a value for two of Scrap, Resale or RTS...its either one of them.
I'm sure this is simple for the experts within this forum, but I could sure use the help.
Thanks,
Infamous
I need a text box in a form to display a series of error messages depending on the result of a DateDiff result, but I'm having trouble creating a single formula that encapsulates all error messages, but I have been able to get this working easily enough using 3 text boxes (For 3 seperate error messages one for results between 7 and 13 days, another for between 14 and 27 days and a third for 28+ days) and the same basic formula, I just need help combining them into a single formula for a single text box. I'm using these:
=IIf([DateDiff]>=7<14 and [Bleh] is Null, "7 Day Warning", "")
=IIf([DateDiff]>=14<27 and [Bleh] is Null, "14 Day Warning", "")
=IIf([DateDiff]>=28 and [Bleh] is Null, "28 Day Warning", "")
How would one go about combining all those formula's? I tried for ages today but I didn't come up with much, experimenting with Or IIf etc... but coming up with -1's all the time. Any help would be appreciated
OK, this is my first post and unfortunatly it is for some help...
I have searched Google to my wits end without any luck.
Heres my problem (partially) and I am hoping some kind person can help me.
Suppose I have a form [form1] and a query [query1].
On form1 I have a 2 textboxes. [txtProduct] and [txtStockCount].
Into [txtProduct] I enter the name of a product. In reality I have already a usable listbox in my form, but I am using a textbox for simplicity here!
Now I create an [EventProcedure] using 'On Lost Focus' against [txtProduct].
The event prodedure would partially read:
"SELECT query1.StockTotal, query1.Product FROM query1 WHERE = Product = txtProduct;"
In my actual form I am using a listbox with a listbox.rowsource = (SQL Query)to display the stock count, but I don't think this is the best way... (It works, i'll give it that!).
What I would ideally like to do is take the results from that aformentioned query and display them in txtStockCount.
What I am aiming for is to get the a query to dump its single result into a variable; not just for display purposes (txtStockCount), but to later validate user input when they wish to reduce stock values from the database, IE so they cannot produce negative stock counts.
I have a feeling this might involve Record Sets which I know nothing about at present.
If someone could either provide some working sample code snippet or point me in the right direction I would appreciate it.
I am confident enough to write SQL statements. I am semi confident in using VBA. (I am not a complete newbie).
If I have not been clear enough, I can provide a lot more information using actual details from the database I am trying to develop.
The database is a stock control system which records:
* products in one table
* transactions in another table (goods in and goods out)
* a query to 'sum' up 'goods' in and 'goods out' with an expression to create total stock counts for each item
* a form into which a user can select an item from the product table which would then produce a single count of the stock running another SQL query against the one already created to pick out that single product (phew!)
and finally in the same form add to the transaction table.
Here's hoping!
Damo.
I currently have a if statement checking for values greater than 0
if(x>0) Then
Statement
Else
EndIf
Is there a way for me to set the property Bold to the text box in the case that x >0?
Hy
This is maybe a question that has been asked a million times.
Can anyone explain me how you can link to textboxes in a access form.
I'm making a form that input info. When a user selects a number in the first textbox I want that in het second text box the info of that number is showed automatically. Those two (number and info) are in the same table.
And is this possible without using visual basic for applications?
Thx
I have a table with names of people and the cars they own. I made a form with a combobox which everytime i choose the name of the person in my combobox, it will list the cars they owned. That works fine. Problem i have is that i have to show how many cars they own. So i made a query to count the number of cars for each person. Then I made a textbox on the form to show this count. However I can't seem to link this textbox to the query since my form has a record source to the table of ppl and cars. How do i link my textbox to my query of counts of cars?
I tried changing the control source pointing to the field of the query but it doesn't work. I have linked the combobox selection so that my query filters to wat i select on the form. Anyone can help :( , its a simple process but i keep trying and doesn't work, and also dont wanna use a subform.
Hello all,
Thanks to all the folks that have helped with my posts in the past.
I have 2 questions.
I have a form with textboxes.
When I tab from one textbox to the other, the tab goes to one of the buttons on the page. How do I tab from one textbox to another.
Also, I have a textbox for memo. I would like to press enter and go to the next line in that textbox.
Thank you very much.
hi everyone,
i have a query which returns n number of rows. my problem is that i need to display the values in text box which is created automatically according to the number of rows returned in the same page(form).
for example if my query returns 1 row then 1 text box must be automatically created and then the value must be displayed and if my query returns 3 rows then 3 text boxes must be created and then the corresponding three values must be displayed.(on the same page).
is this possible?
aravind.s
I have a couple textboxes (Standard) format with 2 decimal places. The textbox allows me to enter say 12.65
What I wan to do is enter 12.65 in one text box and 43.56 in another textbox and then when I click on the third it multiplys the two above and places it in the third mentioned textbox.
I have that working to a certian extent...I place a number in Textbox 1 and then another in Textbox 2, then when I click on the 3rd one (After update event fires) and multiplys then giving me (see below)
Textbox 1 = 12.65
Textbox 2 = 13
Textbox 3 = 169.00
It is rounding the values....I need this carrieed out to two decimal places....
I need it to return 164.45
How do I configure the properties to give me the correct number?
Thanks for all your help it is apprecitated....
hey all i was wondering, can a unbound text box be a bound textbox. I have just noticed that my unbound text box doesnt not save in the field in my table, that is because it is unboune right? but i need it to save to my table, and i guees the only way to do that is to save in in the save button right? thanks
View 2 Replies View Relatedhey all i was wondering, can a unbound text box be a bound textbox. I have just noticed that my unbound text box doesnt not save in the field in my table, that is because it is unboune right? but i need it to save to my table, and i guees the only way to do that is to save in in the save button right? thanks
View 9 Replies View RelatedI have a Form/subform that is used to allow editing of a user selected record from a table called personnel. When the form/subform is first opened, all text boxes are blank. The user selects the desired record from a combo box that uses a dropdown list to display all records of the table. When a record is selected, the contents of that record are displayed in the subform in which the user can then edit/modify. The user then clicks on a "save button" which then saves the record.
I want to use a msgbox that will notify the user that he has just updated a record and ask if he/she would like to update another record. If the answer is NO, then close form/subform. If answer is YES, I would like to clear the combo box and the subform text boxes. then the user can them start the process over by selecting the desired record from the combo box.
When I placed the msgbox coding into the "save" button's on click property, the NO portion worked fine. However, when YES is selected, the combo box would clear but the subform texboxes still show the previous record data (does not clear).
Can anyone provide some assistance?
Here is the code for the main form:
Sub SetFilter()
Dim LSQL As String
LSQL = "select * from personnel"
LSQL = LSQL & " where last = '" & cboSelected & "'"
Form_Editpersonnel_sub.RecordSource = LSQL
End Sub
Private Sub cboSelected_AfterUpdate()
'Call subroutine to set filter based on selected last name
SetFilter
End Sub
Private Sub Form_Open(Cancel As Integer)
'Call subroutine to set filter based on selected last name
SetFilter
End Sub
Here is the code for the subform "save" button:
Private Sub SAVE_Click()
On Error GoTo Err_SAVE_Click
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close
If MsgBox("You have updated information on a Detachment Member. Do you wish to update another member?", vbExclamation + vbYesNo + vbDefaultButton2, "WARNING") = vbNo Then
DoCmd.OpenForm "PERSONNEL MANAGEMENT"
Else
DoCmd.close
DoCmd.OpenForm "Edit personnel"
End If
Exit_SAVE_Click:
Exit Sub
Err_SAVE_Click:
MsgBox Err.Description
Resume Exit_SAVE_Click
End Sub
I'm creating an Access form and there are three sections to the form of which the user should only fill out one. So, they are controlled by a radio group and the desired action is as follows:
There is a default choice (by far, the most common) and it will be selected when the form is opened. When the user selects one of the radio options, the section controlled by that radio option will become enabled and the other two sections will become disabled/locked.
So I found that each box (textbox, combobox, etc) has the property enabled and locked. So I just set this accordingly depending on what is selected. The actual result is that the boxes will be grayed out and become disabled, but will never become enabled again.
Here's some abbreviated code:
Private Sub accountRequestType_AfterUpdate()
If Me.accountRequestType.Value = 1 Then
enableSection1
disableSection2
disableSection3
ElseIf Me.accountRequestType.Value = 2 Then
disableSection1
enableSection2
disableSection3
ElseIf Me.accountRequestType.Value = 3 Then
disableSection1
disableSection2
enableSection3
End If
End Sub
So an example of a disable function (which seems to work) is as follows:
Sub disableSection1()
Me.text123.Enabled = No
Me.text123.Locked = Yes
End Sub
And for an enable function (which doesn't seem to work):
Sub enableSection1()
Me.text123.Enabled = Yes
Me.text123.Locked = No
End Sub
So I'm a bit lost here, any ideas would be great. Are there different steps I need to take in order to enable them again? Am I being clear?
Thanks in advance for any ideas.
Is there a way to clear all the textboxes on my form without having to go through and assign "" to each text box value?
Thanks
Angelo
Hi:
I have a form with data entry textboxes on it linked to a table and a subform
Linked to a query that sorts the data ascending so the newest entered data is the top row of the subform. Also the newest data is still showing in the textboxes which is what I want.
This is all fine and good until you exit the form and re load it.
When you reload it the subform still shows the last data entered at the top
But the textboxes no longer show the newest data entered but are showing data from several rows down.
How can I make the textboxes show the data at the top when the form is reloaded?
Thanks
Charles
Does anyone know how to run a query in VBA that will update TextBoxes in a form?
View 3 Replies View RelatedI've tried manipulating my code in always but cant solve my current issue.My 3 tables are Clientdetails, Clientcontact and Clientdoctor.All the lookups below work fine. clientname is a textbox i use to search the Database and populate my form unbound. i want to populate other textboxes with data from Clientcontact table as well. I'm using a command button relevant to the clientname search.
D = DLookup("[ID]", "[Clientdetails]", "[Surname] ='" & [Forms]![unboundfrm]![clientname] & "'")
Forename = DLookup("[Forename]", "[Clientdetails]", "[Surname] ='" & [Forms]![unboundfrm]![clientname] & "'")
Surname = DLookup("[Surname]", "[Clientdetails]", "[Surname] ='" & [Forms]![unboundfrm]![clientname] & "'")
Address1 = DLookup("[Address1]", "[Clientdetails]", "[Surname] ='" & [Forms]![unboundfrm]![clientname] & "'")
Address2 = DLookup("[Address2]", "[Clientdetails]", "[Surname] ='" & [Forms]![unboundfrm]![clientname] & "'")
[code]...
I have a customers form, on which i have a textbox to allow the user to search and show a record by surname. I have got it working, but i do not know how to accomodate the possibility that there might be two members with the same surname, e.g. Smith.(the PK is member ID)here is the code:Private Sub cmdSearch_Click() Dim strMemberRef As String Dim strSearch As String 'Check txtSearch for Null value or Nill Entry first. If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me![txtSearch].SetFocus Exit SubEnd If 'Performs the search using value entered into txtSearch'and checks this against values in strStudentID DoCmd.ShowAllRecords DoCmd.GoToControl ("Surname") DoCmd.FindRecord Me!txtSearch Surname.SetFocus strMemberRef = Surname.Text txtSearch.SetFocus strSearch = txtSearch.Text 'If matching record found sets focus in strStudentID and shows msgbox'and clears search control If strMemberRef = strSearch Then MsgBox "Match Found For: " & strSearch, , "Record Found" Surname.SetFocus txtSearch = "" 'If value not found sets focus back to txtSearch and shows msgbox Else MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus End IfEnd SubCan anyone shed some light as to what I should do? Im looking to have a next button or something to show the rest of the records, or have the search button change to say next if there are more records.Thanks in advance
View 1 Replies View RelatedI have a subform with textboxes for data entry.
I want the last data entered to stay displayed in the textboxes after the form is closed.
When the form is re-opened the textboxes display a row af data several rows down.
How do you link the textboxes to the feilds of the subform the new data is always at the top row.
Example:
Form textboxes data Entry:
Apples Oranges Pears grapes
Click command button to save:
Subform displays
Apples Oranges Pears grapes
Grapes Kiwi Tangerines Corn
Beans Lettuce Okra Rice
Close form Text boxes still display Apples Oranges Pears Grapes
Open form back up
Text Boxes Display
Grapes Kiwi Tangerines Corn
Subform Displays:
Apples Oranges Pears grapes
Grapes Kiwi Tangerines Corn
Beans Lettuce Okra Rice
How Can I make this work Right?
If anyone knows how to do this please Help.
If it must be done in code please provide an example.
I need to get this worked out this weekend.
Thanks for all the help
You guys are great!:D
Charles
Im just starting to program in Access.. can somebody please help me with my problem?
Im trying to figure out how to search the table with the content of my textbox. (Ex. i input "A" in my textbox, it should display all the records of "A" like it's AGE, STATUS, etc.) it doesn't have to be case sensitive or exact because i have a primary key that doesn't accept duplicate.
Thanks to anyone who'll help
I been trying to figure out how to do this for some time now. But nobody seems to be able to know the method. I have searched the message board and tried the techniques that were suggested but none of them seem to work effective. Here is the problem:
All I want is a 2 textboxes on a form that where a user can put in the parameters for a query. The users clicks on the generate report button and the report pulls the textboxes's data as the parameters data. Report opens and its done. Every time I try this I get the query still asking for the parameters. Is there a sample database that someone can point me to that is using this method for the parameters. The Microsoft website has nothing pertaining to this matter. Only reason I am trying to use this is to stop people from putting in the wrong parameter information and it would look better then that generic parameter box that pops up.
Sorry to be a pain in the you know what!
I have a continuous form and having trouble with the layout. When i fill in the record and then tab to the new record it displays the textboxes on top of the last one instead creating a new line bellow?
View 2 Replies View RelatedI've got a form called "Main Menu" and i'm in the process of implementing a search form into it. So far I have three textboxes called TBFirstName, TBSurname and TBCentre these are unbound. I also have a search button.
This button runs the query which runs the search, the sql for this query is below.
Code:
SELECT Staff.[Employee ID] AS [Staff_Employee ID], Staff.Forename,
Staff.Surname, Staff.Sex, Staff.[Job Role], Staff.[Start Date], Staff.[Leave Date], Staff.Centre,
Staff.Hours, Qualifications.[Employee ID] AS [Qualifications_Employee ID],
Qualifications.[Level 2 Maths], Qualifications.[Level 2 English], Qualifications.[Level 3 Support Maths],
[Code] ....
Whenever I run this query it just returns all the records instead of the search criteria. Where am I going wrong?
When moving to a new a record in my form, i will click on a text box and the following happens:The black line will flash much faster than normal (This will happen on any text box i click on) No text will appear while this is happening When it stops al the text that was typed in that period will appear After this 5 to 10 second text delay the text box and all other textboxes will work fine, until i move to a new record and the process starts again
View 2 Replies View Related