Please Review This Code, (simple Code) New With Codes
Feb 16, 2006
Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load()
Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
If InputBox("What is the password?", "Password") = "2" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
End If
End If
hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.
Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.
Help is appreciated.
Here is the code that I was thinking of, I don't think its right but it might be a start.
Dim Count As Variant Dim CountImplementedTotal As Variant
CountCost = 0 CountImplementedTotal = 0
Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordSet("ECM Details") Do While Not rst.EOF If rst![AUDIT ID] = Forms![audit info]![AUDIT ID] Then If rst![Has Measure been Selected] = "YES" Then Count = Count + rst![Total Measure Cost] End If End If rst.MoveNext Loop
I'm having some problems with VBA code I'm writing that references a subform. I'm pretty new to VBA and am not sure what the problem could be. The parent form (TrackingForm) tracks dates different tasks were completed. The subform (Child2) tracks exceptions that also need to be marked completed. I need ALL dates to be marked as completed before the entire tracking record can be marked as completed.
I have the following code entered into the OnFocus event of the tracking records Me.Completed_Date field:
Private Sub Completed_Date_GotFocus() If IsNull(Closing_Date) Or IsNull(Package_Received) _ Or IsNull(Upload_By) Or IsNull(Initial_Review_Date) _ Or IsNull(Me.Child2.Form![Date Exception Completed]) Then Me.Completed_Date.Locked = True MsgBox "Completed Date cannot be entered - outstanding items.", vbOKOnly, "Warning!" Else: Me.Completed_Date.Locked = False End If End Sub
What ends up happening is that, if there is more than one exception record on Child2, the code only seems to care if one of the records (usually the first record) has something entered in the [Date Exception Completed] field. I can't seem to make it look at any successive records in Child2 to ensure they are also completed prior to unlocking the Me.Date_Completed field. I have googled this extensively over the last day and the only response that seems to recur involves using a Recordset function that I have never used and am unsure how to.
I am checking to see if a memo field and/or a text field has more than 1000 characters and if so then it calls a function that prints legal size paper vs. 8 X 11. It works fine except when the either field is empty. I have searched and searched the Forum and found many that are close but no cigar!
I know this involves a "IsNull" routine and I know I need to check both fields but can't quite come up with the right syntax. Right now if either field is empty I get an error msg = "Invalid use of Null"
Could someone please help?! Thanks in advance once again! This is the basic code after setting the focus,etc....
So far I have a query that picks up any collisions between booking dates with the following:
SELECT [Customer Details].[CustomerID], [Booking].[RoomNumber], [Booking].[DateFrom], [Booking].[DateTo] FROM [Room Details] INNER JOIN ([Customer Details] INNER JOIN Booking ON [Customer Details].[CustomerID]=[Booking].[CustomerID]) ON [Room Details].[RoomNo]=[Booking].[RoomNumber] WHERE DateFrom Or DateTo Between [Forms]![Bookings]![DateFrom] And [Forms]![Bookings]![DateTo] And ([BookingID]<>[Forms]![Bookings]![text38]) And (RoomNumber=[Forms]![Bookings]!RoomNumber) ORDER BY [DateFrom] And [DateTo];
But the problem is that it doesn't pick up dates inbetween DateTo and DateFrom. So:
Booking 1 12/12/06 20/12/06
Booking 2 13/12/06 19/12/06
On trying to book Booking 2 it will not notify me of 'no clashes' when there are. As it isn't passing by the 12th or 20th my SQL doesn't pick it up.
Can anybody help me? I'm new to code so I'm probably missing the obvious but all help will be greatly appreciated.
I have a database with 8 tables. One of these being staff
I have staffID as a Primary key in the table staff The format of this is "STA"0000 and input mask "STA"9999
I would like staffID to be automatically generated by taking the value of the previous staffID (eg STA0001) and creating a new staffID for the next member of staff that is to be added (eg, STA0002).
I have access experience but have never had to use SQL code and am struggling. If anyone could be kind enough to help me with this I would be very grateful.
Just arrived here, so I hope this question is in the correct forum. Here is my question
I have one field called ContractType, another field called Contract Description. What I like to do is if ContractType = CO and Contract Description, DO NOT HAVE TEXT IN THE Contract Description field, flash the Contract Description field. How do I identify if there is text fill out in the Const_Description field?
This is what I have so far:
Private Sub Form_Timer() If Me.ContractType = "CO" Then Me.Const_Description.Visible = True Else Me.Const_Description.Visible = Not Me.Const_Description.Visible End If End Sub
I hope I made this easy to understand. Very new at Code.
I got this to work easily on another form but on this particular form it doesnt work. I enter a command button and name it d1, then I enter this code for it to populate the current date in the field next to the box:
Private Sub d1_Click() Date_Entered.Value = Date End Sub
Can anyone see why it doesnt work :eek:
ERROR IS: Run-time error '2465':
Database cant find the field 'Date' referred to in your expression.
When I enter a date field it enders whats in that field (blank) into the field I told it (Date_Entered) on my form.
I have some simple validation that unit price is greater than 0. The code is this:
If Me.txtUnitPrice.Value <= 0 Then MsgBox "Please enter a value greater than zero", vbOKOnly, "Alert" Me.txtUnitPrice.Value = Null Me.txtUnitPrice.SetFocus End If
However, when I run it, the setfocus doesn't work. It jumps straight into the next field. I can make it run to any other field (productname, productID). But not back to UnitPrice.
I found this simple password code that I use to open some forms: Code:Dim x As Stringx = "password"Dim y As Stringy = InputBox("Enter password for form")If x <> y ThenMsgBox ("Invalid password")DoCmd.CancelEventEnd If I would like to use it in a switchboard button, but the following statement is OnClick already "=HandleButtonClick(4)". How can I incorporate this statement with the code?
(I guess I don't really understanding how the auto-switchboard works.)
quick easy question, as im a noob when it comes to coding/vb.
Within our company database we have the usual timesheet table. With the table I have create a query (called "dailytimeforkeith") that gives me the sum of time for an individual person for one day (in fact its the previous day). What I have done then is created a form (called "keithyesterdaytime") that shows this query entry (called "sumoftimespent").
Each user has a different database screen logon and forms that are present when they log in. So what I then done is on one of these forms the load up, i attached this:
Private Sub Employee_Enter()
Me.Employee = User.FirstName
If Me.Employee = "Keith" Then
Dim stdocname As String
stdocname = "keithyesterdaytime"
DoCmd.OpenForm stdocname, , , acAdd
............
then on "keithyesterdaytime" opening the following code runs ------------------------------------------------------------
I protect my code from people being able to read it by setting a password on the code from Tools > Properties, selecting the Protection tab and entering a password, and clicking "Lock Project"
Is there a way to write code that will remove that Lock Project check and check it back on?
I've looked through the Application.SetOption command and it doesn't seem to be one of the choices. It would be very helpful if someone knew how to do this.
I have witten a code in the close event of a "Primary" form that would update a Combo boxe on a "secondary" form only if the "secondary" form is open. ie:
Private Sub Form_Close() If Forms!frmEnquiry.Open Then Forms!frmEnquiry!CboCustomer.Requery Else DoCmd.Close End If End Sub
This code keeps giving me an error, is anybody has got any idea why? Thanks in advance.
Private Sub NextApplication_Click() On Error GoTo Err_NextApplication_Click DoCmd.GoToRecord , , acNext
Exit_NextApplication_Click: Exit Sub
Err_NextApplication_Click: If Err.Number = 2105 Then MsgBox "Cannot navigate to the next record. This is the last record." Else MsgBox Err.Description End If
Resume Exit_NextApplication_Click
End Sub
but even when this error occurs nothing is being properly handled the way i specified - any ideas ?
Need a little help here. I have this code on a command button that open a form that displays a chart. The chart work great as long as there is data to display. However, if there is no data then the chart is blank. So what I'm trying to do is add a dcount to catch the 0 and give an error. So the 1st query makes the table where the data for the chart come from. The Qry-Test for Zero query, queries that new table if dcount is 0 it should error. My problem is that I can't get this to work with the 0 if I make it 1 then I will get the message box if there are no records and if there is 1 record 2 and greater work fine. So my question is why won't the code catch the 0?
Dim stDocName As String stDocName = "Qry-Makes Table" DoCmd.OpenQuery stDocName, acNormal ', acEdit
If DCount(" * ", "Qry-Test for Zero") = 0 Then MsgBox " There is no data for this time frame to chart. Please re-enter your Date range" Exit Sub Else
Need a little help here. I have this code on a command button that open a form that displays a chart. The chart work great as long as there is data to display. However, if there is no data then the chart is blank. So what I'm trying to do is add a dcount to catch the 0 and give an error. So the 1st query makes the table where the data for the chart come from. The Qry-Test for Zero query, queries that new table if dcount is 0 it should error. My problem is that I can't get this to work with the 0 if I make it 1 then I will get the message box if there are no records and if there is 1 record 2 and greater work fine. So my question is why won't the code catch the 0?
Dim stDocName As String stDocName = "Qry-Makes Table" DoCmd.OpenQuery stDocName, acNormal ', acEdit
If DCount(" * ", "Qry-Test for Zero") = 0 Then MsgBox " There is no data for this time frame to chart. Please re-enter your Date range" Exit Sub Else
I use the code below on a search form. I would like for the results of the list box search to populate a report instead of the list box. Is it possible to take the the sql and move it to a report? Thanks..
Dim strsql As String, strOrder As String, strWhere As String Dim dbNm As Database Dim qryDef As QueryDef Set dbNm = CurrentDb()
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form If Not IsNull(Me.txtIssue) Then '<--If the textbox txtCenterName contains no data THEN do nothing strWhere = strWhere & " (SiteIssues_tbl.Issue) Like '*" & Me.txtIssue & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef End If
If Not IsNull(Me.txtAdminDate) Then strWhere = strWhere & " (SiteIssues_tbl.AdminDate) Like '*" & Me.txtAdminDate & "*' AND" End If
If Not IsNull(Me.txtSite) Then strWhere = strWhere & " (SiteIssues_tbl.SITE_ID) Like '*" & Me.txtSite & "*' AND" End If
With Me.lstSearchResult If .ListCount > 0 Then GetListCount = .ListCount - 1 'Headings count Else GetListCount = 0 'Headings don't count if no items listed!! End If Me.Text31 = .ListCount - 1
Dim ctl As Control End With If Me.Text31 = -1 Then Me.Text31.Value = 0 Me.lstSearchResult.RowSource = "" DoCmd.RepaintObject acForm, "SitesIssues_qry" Me.Requery
MsgBox "No Records Found."
For Each ctl In Me.Controls If ctl.ControlType = acTextBox Or ctl.ControlType = acCheckBox Then ctl.Value = Null DoCmd.RepaintObject acForm, "SitesIssues_qry" Me.lstSearchResult.RowSource = "" Me.Requery End If Next ctl End If
I have code bellow which is bringing a tab called Heffalump from the excel spreadsheet into table. Now, what I need is to create a text box where I will be typing PI1228131313 and it's going to be changed in the code bellow.
I have a code bellow which takes data from Excel spreadsheet and puts it into table in access. I want to see this code in txtbox and be able to change the path's.. Please advice. I tried typing.. Me.mytextbox.value = but for some reason doesn't work.. Should I use Ufter Update or something like that..?
I 've converted the code to ADO method. It was working fine in DAO but know I get the following error and i can't figure it out the soloution.
Run-time error ‘3265’
Item cannot be found in the collection corresponding to the requested name or ordinal
Function ChangeQuantUnit(Material, quant_a, unit_a, unit_b)
Static M As New ADODB.Recordset Static Mat_SQL As String
ChangeQuantUnit = 0
Mat_SQL = "SELECT SAP_Materials.Material, SAP_Materials.K, SAP_Materials.Base_K, SAP_Materials.KG, SAP_Materials.Base_KG, SAP_Materials.MTR, SAP_Materials.Base_MTR, SAP_Materials.ST, SAP_Materials.Base_ST FROM SAP_Materials WHERE (((SAP_Materials.Material)=""" + Material + """));" M.Open (Mat_SQL), CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If unit_a = "LB" Then ChangeQuantUnit = (quant_a / 2.2) Else If M(unit_a) * M("BASE_" + unit_b) > 0 Then 'this is the line that crashes ChangeQuantUnit = quant_a * M(unit_b) * M("BASE_" + unit_a) / (M(unit_a) * M("BASE_" + unit_b)) End If End If
What I am trying to do is delete a table and replace it with a table of the same name in another db. the path to the file is stored in Text1. The delete part works OK then the error message arrives, when I run it with a static file path it worked ok, but I want to pick up the path stored in Text1 on my form.:confused:
In the database I am creating a table that houses “subjects’ names” and their “unique IDs”, which are both indexed with no duplicates. The Unique ID is used as a FK to connect all the tables that contain information from the subject. When subjects are enrolled in one of our studies they are assigned a “Unique ID”, which is assigned sequentially. The IDs are alphanumeric, and consists of 4 characters with the first character being a letter:
A001, A002…A999, B001, B002…C001 etc.:
To help avoid data entry errors, I would like to automate the process of assigning these Unique IDs as much as possible. So my question is:
Is it better to manually insert all the possible Unique IDs into this table upfront and then use a code that allows the user to assign the next Unique ID to the subjects they are enrolling (ie search the subject names column for the first null field and enter value there)? Or would it be better to set up a code that looks through the IDs that are already in the list and then automatically generates and assigns the next sequential ID to the person they have entered.
Also, where might I find some pre-established code to get me heading in the right direction?
If next record is biger than previous show "1" If next record is same previous show "1" If next record is smallest show "0" if next record is same previous show "0"
