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
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
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.
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"
I need a littlle help adapting this piece of code to my purposes:
SELECT Clientes.[Codigo Cliente LAE], Clientes.Nome, Clientes.Endereço, Clientes.Cidade, Clientes.[Codigo Postal], Clientes.Telefone, Clientes.Fax, Clientes.[Numero Contribuinte] FROM Clientes GROUP BY Clientes.[Codigo Cliente LAE], Clientes.Nome, Clientes.Endereço, Clientes.Cidade, Clientes.[Codigo Postal], Clientes.Telefone, Clientes.Fax, Clientes.[Numero Contribuinte] HAVING (((Clientes.[Codigo Cliente LAE]) Like "*" & Forms![Pesquisa de Clientes]!PCTextPesq2 & "*"));
I need to replace "Codigo Cliente LAE" by "Forms![Pesquisa de Clientes]!PCComboPesq" (I need to keep the [] of the the original code, just replace "Codigo Cliente LAE"). I've tried several ways but still get a sintax error.
Hi, I'm analyzing a program made with MS Access. It has a whole bunch of tables and queries already made, and a form with lots of code behind it. I'm looking through and I'm not sure what the following means:
With CurrentDb .QueryDefs("qry_cur_pier").SQL = _ " SELECT DISTINCT RE.AIRPORT_RESOURCE_NAME, RE.AIRPORT_RESOURCE_KEY " & _ " FROM qry_cur_resource AS RE " & _ " WHERE RE.AIRPORT_RESOURCE_CODE = 'PIER';"
qry_cur_pier appears to be an already existing query. AIRPORT_RESOURCE_NAME and AIRPORT_RESOURCE_KEY are fields in that query. AIRPORT_RESOURCE_CODE is not in the query though. What does the RE. and the AS RE do here? Thanks in advance.
Is there a way of changing VB code so that ONLYif a statement is true then the command runs.
This is what I currently have:
If Me.chkDoorChoice = True And Me.cboFrontDoorType = "N/A" Or Me.cboFrontDoorColour = "N/A" Or Me.cboRearDoorType = "N/A" Or Me.cboRearDoorColour = "N/A" Or Me.cboAdditionalDoorType = "N/A" Or Me.cboAdditionalDoorColour = "N/A" Then DoCmd.RunMacro "mcrPleaseNote" End If
So the command should only run when chkDoorChoice is true. There are occasions when cbofrontdoortype = "N/A" and chkDoorChoice = False and the macro still runs. I dont want this to happen.
Hola. Im new to this forum and new to Access. At the mo im busy with computing coursework and as such , am working with Access XP. I want to create Amount Paid and Amount Due fields in which the values automatically change as I input new data. Ie, total =£45. Amount paid changes to £5 and the due changes automatically. Can anybody help me? Thanks. Michael
i am trying to put in a text box that counts the number of dates where the apptmade text is "attended". [Both fields from the same table]. I can set the control source of a text box to = Count(Date) which works but how can I extend that to a count of dates where the ApptMade = "attended"
I hope this is relatively simple. I have a command button that closes out a form, but was wondering if I could also write some simple code into the button to essentially "Alt-tab" out of access completely while the form closes. I'm still not very good in Access so any help is appreciated.
I am trying to write the code which will show a Message Box if another field in a table is "Not Null" . Here is what I am attempting:
Upon exit of [JobFieldname] Look at record in [Table].[Jobinfo]. where [JobFieldname] match If [Yes/Nofieldname] = "Yes" then Open Message Box "My message Here"
Can someone help me? I havn't found the answer looking through the threads.
I can't get this working. The code was posted by someone else. Anybody that can help, thanks so much. ........................ I have a table name main1. I have a form name main2. (input box and command button)
..........................I placed the code into my command button......
Private Sub Command2_Click() Set con = Application.CurrentProject.Connection Set rs = CreateObject("ADODB.Recordset") stSql = "SELECT * FROM main1 " 'I have the password stored in this table!
rs.Open stSql, con, 1 ' 1 = adOpenKeyset
If Not (rs.EOF) Then If rs![PASSWORD] = Form_main2.Text1.Value Then 'Text1 is my unbound box for the user to enter the password! DoCmd.OpenForm "intro" DoCmd.OpenForm "main2" DoCmd.Close Else MsgBox "You have entered the wrong Password!" End If End If End Sub
I'm in the US military. I would like to use the bar code on the back of the military IDs to populate an access database. Can you point me in the right direction as to how to get started?
