Could Any One Explain This Code For Me ?

Aug 11, 2005

Hi expert. could any one explain this code for me. I do not understand some parts of it. I know the output but not some parts of the code.Thanks



Code:Private Sub processButton_Click() Dim fso As New Scripting.FileSystemObject <------ Dim io As Scripting.TextStream <------ Dim db As DAO.Database Dim rst As DAO.Recordset Dim fld As DAO.Field '''Declreaing our variables Dim strBase As String Dim strInsert As String Dim strFields As String Dim strValues As String Dim strTemp As String Dim strFile As String Dim strName As String Set db = CurrentDb() Set rst = db.OpenRecordset(Me![ComboBox]) strBase = "INSERT INTO " & Me![ComboBox] & "({%1}) VALUES ({%2})" <------ strName = "c:" & Me!ComboBox & " Data.sql" With rst <------ While Not .EOF strValues = "" <------ If Len(strFields) = 0 Then For Each fld In .Fields If Len(strFields) > 0 Then strFields = strFields & "," & fld.Name & "" Else '''strFields = "[" & fld.Name & "]" strFields = "" & fld.Name & "" End If Next fld strInsert = Replace(strBase, "{%1}", strFields) End If For Each fld In .Fields If Len(strValues) > 0 Then strValues = strValues & "," End If If IsNull(fld.Value) Then strValues = strValues & "null" Else v = fld.Value Select Case fld.Type Case dbMemo, dbText, dbChar strValues = strValues & "'" & v & "'" Case dbDate strValues = strValues & "#" & v & "#" Case Else strValues = strValues & v End Select End If Next fld strTemp = Replace(strInsert, "{%2}", strValues) strFile = strFile & strTemp & vbNewLine .MoveNext Wend rst.Close End With If Len(strFile) > 0 Then Set io = fso.CreateTextFile(strName) io.Write strFile io.Close End If End Sub

View Replies


ADVERTISEMENT

Help Explain Code

Apr 6, 2007

Can someone explain what the following code does.Thanks in advance


Set dbUser = DBEngine.Workspaces(0).Databases(0)
Set rsUser = dbUser.OpenRecordset("SELECT User.UserName FROM [User]WHERE (((User.UserName)='" + txtUserName + "'))")

View 1 Replies View Related

Please Explain This Code

Oct 11, 2006

I am trying to figure out how this code actually works. In the Field I see the following code:

IIf(Weekday([CollectionDate]-4)=1,[CollectionDate]-6,IIf(Weekday([CollectionDate]-4)=7,[CollectionDate]-5,[CollectionDate]-4))




In the Criteria I see: Like Date()
I know what the "LIke Date() does but what im trying to figure out what is the connection to the above....

Any Help would be greatly greatly appreciated!

View 1 Replies View Related

One Line Code Can Someone Explain Please?

Sep 5, 2006

I saw this code and was wondering how does it actually work?
I saw it in/connected to a textbox.



Private Sub savetime_BeforeUpdate(Cancel As Integer)

End Sub

View 1 Replies View Related

I'm Not Quite Sure How To Explain This

Aug 24, 2007

I am building a database that will tell the company what items to dispatch to the customer first. It’s a food company. So getting products out on time is important. I will try explain it. Say the food company makes a batch of 30 apple tarts. This collection of products has a set batch number with all the relevant information attached. For exampleBatch no 4457 - qty 30 - created 10/10/07 - use by 10/11/07 Batch no 4458 - qty 30 - created 11/10/07 - use by 11/11/07Now lets say customer (joe Bloggs) orders 35 apple tartsThe food company wants to take all the first batch (4457) and 5 from the second batch (4458) The will have an order sheet with the followingApple tart batch 4457 - 30Apple tart batch 4458 - 5 After the order is gathered it will be given an order number of say order 8897The company can now go back and look at order 8897 and see what was shipped in that order and what the batch numbers where for all products. Any help would be greatly appreciatedAlan

View 14 Replies View Related

Explain Query

Oct 17, 2006

Hi all,

I was recently asked to rewrite some stuff and i was wondering if the query below can be expressed with just ( SELECT, FROM, WHERE, GROUP BY ) Instead of JOIN.
Someone else wrote the query below and Joins always confuse me ...
Thanks in advance


SELECT Grpoffic.CODE, Evoter.precinct, Evoter.grp, IIf(evoter.style Is Not Null,evoter.style,evoter.[activator code]) AS ballot, Evoter.party, Evoter.[date voted] AS [voted date], Evoter.certnum AS [voter id], Evoter.name, Evoter.address, Evoter.[ev location] AS [Location voted], Vote_status.status, AB_CODE_reason.reason AS [absentee reason]
FROM Vote_status, AB_CODE_reason RIGHT JOIN (Evoter INNER JOIN Grpoffic ON (Evoter.precinct = Grpoffic.pct) AND (Evoter.grp = Grpoffic.grp)) ON AB_CODE_reason.code = Evoter.reason
ORDER BY Grpoffic.CODE, Evoter.precinct, Evoter.grp, Evoter.name;

View 1 Replies View Related

Can Some One Explain This Expression To Me Please?

Mar 6, 2008

Month Number: Mid([Date],4,2)

I also have the SQL that goes with the query:

SELECT Mid([Date],4,2) AS [Month Number], Count(tblOrders.Order_Number) AS CountOfOrders, Sum(tblBikes.Price) AS SumOfPrice, Format([Date],"mmmm") AS [Month Name]
FROM tblOrders INNER JOIN (tblBikes INNER JOIN [Order/Product] ON tblBikes.Model_Number = [Order/Product].Model_Number) ON tblOrders.Order_Number = [Order/Product].Order_Number
GROUP BY Mid([Date],4,2), Format([Date],"mmmm");

:confused:

View 6 Replies View Related

Help With A Query... Hard To Explain

Mar 29, 2008

Hi all!, i would really appriciate it if you could help me out. I have this complex query... in one table i have dates and costs associated that show when fuel was put into the system

such as:
1/1/2008 -- $200
1/20/2008 - $100
1/25/2008 - $150

I then have another table that has data when fuel was taken out of the system
such as:
1/2/2008 -- ($50)
1/3/2008 -- ($20)
1/4/2008 -- ($10)

what i need is a query that is able to show a running tab on the fuel tank and how much fuel is in it...

so for example if it could spit out a result such as
1/1/2008 -- $200
1/2/2008 -- $150
1/3/2008 -- $130
1/4/2008 -- $120

If this is convoluted i can upload a little database..

Thanks! (Here is the current query i am using but its not working right.

SELECT DatePart("yyyy",[purchase_Date]) AS AYear, DatePart("m",[Purchase_Date]) AS AMonth, DatePart("d",[Purchase_Date]) AS ADay, DSum("gallons_purchased","purchase_fuel_tbl","DatePart('d', [purchase_Date])<=" & [ADay] & " And DatePart('m',
[purchase_Date])<=" & [AMonth] & " And DatePart('yyyy',
[purchase_Date])<=" & [AYear] & "") AS RunTot, fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons, [RunTot]-[fuel_use_tbl].[gallons] AS cur_Value
FROM (tank_tbl RIGHT JOIN purchase_fuel_tbl ON tank_tbl.tank_ID = purchase_fuel_tbl.tank_ID) LEFT JOIN fuel_use_tbl ON tank_tbl.tank_ID = fuel_use_tbl.tank_ID
GROUP BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]), fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons
ORDER BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]);

View 14 Replies View Related

Difficult To Explain Yet Simple Problem...!

Dec 1, 2005

Hi all

I have a financial database that gets downloaded transactions off the internet from our accounts. The problem is that the transaction payer/payee is not always unique and needs to be classified. This is easy done using If statements but I really want to enter parts of the string into a table and have a query return a category for this payer/payee. Example:
I want this: AUTO ONE CLARKSON CLARKSON WA AU006495
to be recognised as: car parts

or with this tranaction:
CLARKSON MINI MART CLARKSON553908
look for "mini mart" and return supermarket

This step will make classifying transactions a lot simpler and user friendly.
thanks

View 3 Replies View Related

Can Someone Please Explain Basic Query Logic To Me?

May 11, 2006

I am not sure if I understand this...

I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????

Any help is greatly appreciated.

Tom

View 4 Replies View Related

Pulling My Hair Out! Hope I Can Explain

Jul 16, 2005

Let see if I can explain my goal. I've been fighting with this for some time with no resolve. In the included DB has a table that represents the fields that will be queried in the real DB. It shows records of one of many employees that were "tested" (shopped) on a date and whether it was a successfull shop or not. My goal is in three parts.

1)A query that goes through each employee and returns the records with the check box in SuccessYes checked (Yes) back to the last unchecked box, not including the unchecked box. In the example given the last (most recent) 5 records would be returned. If the shop of date 1/18/2005 was not checked, it would only return the last 2 records. Basically, I'm looking for a way to have a report that shows everyone's current successes in a row. It could be any number.

2)A query very similar but it returns only those employees that have the current successes in a row totalling 5 and where the field "Award5_Paid" is NOT checked (they have not been paid their reward). From there it should be easy to do one for the 10 in a row.

3)If I've got a form open (or...?) where I can place a command button, that when clicked, it appends the selected records with checks in the appropriate boxes for being paid. Example: If a set of employees come up in the query with 5 successfull shops in a row because the "Award5_Paid" field is unchecked, then I need to pay them. Click the button and the Award5_Paid field gets checked in THOSE RECORDS ONLY so that the next time I run the query, they won't show up and get paid twice. Issue? What's the coding or SQL or ??? that's run when clicked.


I hope that makes sense. I've been running around in circle trying to figure this one out. Maybe it can't be done, but if it can, I know some of you know how.
As always, thanks for the assistance.

-Jim

View 3 Replies View Related

I Think This Is A Real Noobie Question But Can Someoen Explain?

Sep 16, 2005

Hi, i know i have done this before but its been so long and i know its real simple so here goes:

Say i have a record in a table and i want to add records to that record in another table does simply creating a relationship between the two tables make sure the information is relevant to that record and how do i add more than 1 tables information to a form so i could show the record from the first table and say all of that persons orders from another table?

Thanks for your assistance
Alex

View 1 Replies View Related

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


End Sub

View 14 Replies View Related

Using Code To Unprotect And Protect Viewing Code

Jan 14, 2007

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.

Thanks

SHADOW

View 6 Replies View Related

Code Help Please!

Apr 27, 2005

I have a button that runs a macro to insert NOW() into a text box.
This is how it is coded;

Private Sub Start_transferred_job_button_Click()
On Error GoTo Err_Start_transferred_job_button_Click

Dim stDocName As String

stDocName = "Start transfered job"
DoCmd.RunMacro stDocName

Exit_Start_transferred_job_button_Click:
Exit Sub

Err_Start_transferred_job_button_Click:
MsgBox Err.Description
Resume Exit_Start_transferred_job_button_Click

End Sub


How do I write the code to populate the [start Time] text box with NOW() when the button is clicked without using a macro???

View 1 Replies View Related

Need Help With Code

Nov 22, 2005

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



Thanks

jon

View 1 Replies View Related

Need Help With Code

Nov 22, 2005

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



Thanks

jon

View 1 Replies View Related

Code Help?

May 19, 2006

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()

strsql = "SELECT SiteIssues_tbl.IssueID, SiteIssues_tbl.SITE_ID, SiteIssues_tbl.IssueID, SiteIssues_tbl.AdminDate, SiteIssues_tbl.Occurring, SiteIssues_tbl.Issue, SiteIssues_tbl.Administrative, SiteIssues_tbl.Technical, SiteIssues_tbl.IssueComments, SiteIssues_tbl.CandidatesAffected, SiteIssues_tbl.RecordCreated, SiteIssues_tbl.User, SiteIssues_tbl.DateModified " & _
"FROM SiteIssues_tbl"


strWhere = "WHERE"

strOrder = "ORDER BY SiteIssues_tbl.AdminDate;"


'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

'Pass the SQL to the RowSource of the listbox

Me.lstSearchResult.RowSource = strsql & " " & strWhere & "" & strOrder

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

View 1 Replies View Related

VBA Code

Jun 1, 2006

I have written the code below, which should work, however I am receiving an Error: Run Time Error '13' Type mistmatch on line:

Set cn = Application.CurrentProject.Connection.

Can anyone help?

Option Compare Database
Option Explicit

Private Sub cmdRun_Click()

Dim NICode As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Dim strSQL As String

Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
NICode = "txtLetter"

strSQL = "SELECT " & NICode & "1a, " & NICode & "1b, " & NICode & "1c, " & NICode & "1d & NICode & "1g & NICode & "1h FROM [WorkPlace NI Breakdown]"

rs.Open strSQL, cn

If Not (rs.EOF And rs.BOF) Then
MsgBox rs.Fields(NICode & "1a")
MsgBox rs.Fields(NICode & "1b")
MsgBox rs.Fields(NICode & "1c")
MsgBox rs.Fields(NICode & "1d")
MsgBox rs.Fields(NICode & "1g")
MsgBox rs.Fields(NICode & "1h")


End If
rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

End Sub

View 3 Replies View Related

A Code

Jan 3, 2007

Hi all,

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.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test Import Specification", "K:FIDCPGCCGPI1228", True, "Heffalump!"

THANKS,
B

View 1 Replies View Related

A Code

Jan 3, 2007

Hi All,

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..?


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test Import Specification", "K:FIDCPGPI1206", True, "Heffalump!"

View 1 Replies View Related

Any Help With My Code;

May 3, 2007

Hi,

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

M.Close

End Function

View 1 Replies View Related

Can Anyone Help With This Code Please

May 8, 2007

Can anyone help with this code please. I keep getting Error 13, type mismatch on the on click event of a Command button.

Private Sub Command6_Click ()
Dim tmpFilePath AS String
tmpFilePath = Me!Text1

DoCmd.DeleteObject acTable, "Expire"
DoCmd. TransferDatabase_ acImport,"MicrosoftAccess",acTable,"tmpFilePath","Expire","Expire",False

End Sub

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:

Thanks for your help

View 4 Replies View Related

To Code Or Not To Code.

Mar 21, 2007

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?

Thanks in advance!:D :confused: :confused:

View 5 Replies View Related

Need Help In VB Code

Aug 22, 2006

Good day,

Iam traying to get result of my record as below

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"

Example



idTimeCodePriceRes
110:00:00 AM1010905 0
46410:00:34 AM1010906 1
62610:00:56 AM10109061
152310:02:16 AM10109040
156810:02:21 AM10109040
156910:02:21 AM10109051



the code gave me correct result for some filed and other incorrect !

Could you please check the code and correct it for me.

I have attached the DB for any correction

Note: the command bottons on Form1 of Forms Object.

Iam very appreciated for assistant . :)

..

View 1 Replies View Related

Help With SQL Code

Dec 23, 2006

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.

Thanks in advance.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved