General :: Find Specific Row And Copy Its Fields To Array

Feb 16, 2014

I'm new to Access but comfortable with Excel and VBA.I'm trying to transform a VBA-heavy form from Excel into an Access database, as multiple accessing is required, as well as data analysis. The situation is I have a form where users enter data (name, account number, date, etc). The account number is unique, and I have a table of approximately 14000 account numbers which contains the company name, address, and other details for each account number. I've set the account number as the primary ID on the accounts table.

What I would dearly love to be able to do is: when the account number is entered on the form, it is searched for (e.g. by the user clicking a button) from the accounts table. If it is located, the 7 fields in the relevant row are displayed in 7 textboxes on the form. I do not want to record the address, just display it on the form so the user can choose whether or not to use it.

So far, I have found GetRows, which seems to be able to convert the fields into a 2D array... this could be usable. But how can I do the first bit - find the correct row in the accounts table based on the account number entered into the textbox on the form? Is it possible to do this behind the scenes? In other words, Access finds the right row and displays each field in its textbox on the form, without producing a report form first.

View Replies


ADVERTISEMENT

Forms :: Copy Specific Fields From Selected Record To Specific Fields In Subform?

Jul 9, 2015

I am new to access i have a problem which is i have made a form which contains a subform and a read only subreport, what i want is the ability to select a record in read only subreport as in the picture attached and make a button that when i press on it, it should copy the values of the itemsID field, Packing field, ContainerNo field and origin field from the selected record and then paste them in the subform below.

Also i want to add more then one item, so the when i press on another record it should paste the values below the first record.

View 12 Replies View Related

General :: Cannot Find Specific Field On Control Source List

Sep 17, 2014

I am relatively new to Access and i am wondering why i cannot find a specific field from a table on the control source when i am and attached it to a check box.

I have created the field on the table, and now i want to attached it to the check box on a form however when i go down the list of control sources i cannot see it.

View 3 Replies View Related

Modules & VBA :: Find Duplicates Of Values In Array

Oct 4, 2014

I have to deal with string arrays that store text. I need info on copying, comparing, appending arrays. Also on passing arrays as parameters to subs or functions. Where I can get to this info quickly without having to browse through many screens.

In addition to this I have some questions:

I have to find the duplicates of values in an array. Here is the code that I use.

Code:
Sub FindDuplicates()
Dim I As Integer, J As Integer, IEND As Integer, text() As String
ReDim text(IEND)
For I = 1 To IEND - 1
For J = I + 1 To IEND
If text(I) = text(J) Then text(J) = ""
Next J
Next I
End Sub

It works but is not performing well. Are there more efficient ways of doing this?

View 14 Replies View Related

Modules & VBA :: Search CSV To Find Specific Statement / Text On Specific Line?

Jul 8, 2015

how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.

If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).

The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).

View 3 Replies View Related

Copy Button In A Form To Copy Fields To Other Fields In The Same Record

Sep 23, 2005

Hi there,

Can someone help me on this issue?

I want to copy Postal Address Information to Visit Address Information wthin the same record, in my Contact Database that I am building at the moment.

I want to copy 4 fields to 4 other fields within the same Record.

This the current code I use for the Copy-button:

---

Private Sub cmdCopyFields_Click()

Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant


v1 = Me!Field_a.Value
v2 = Me!Field_b.Value
v3 = Me!Field_c.Value
v4 = Me!Field_d.Value


RunCommand acCmdRecordsGoToNew

Me!Field_e = v1
Me!Field_f = v2
Me!Field_g = v3
Me!Field_h = v4


End Sub


---

The underlined part (acCmdRecordsGoToNew) is false part of this code, because it copies the Adress Info to a new record, instead of copying it in the same.

-Is there someone that can help me with this problem, to make the copy-button in the Form run properly?

-Are there other options to achieve te same?

I have put in a attachment, to give you guys a visual example of what I mean.



Thanks in advance,

Quinten

View 3 Replies View Related

Modules & VBA :: Define And Setup Array - Find Respective Numbers For A Text String

Apr 21, 2015

I need to find the respective numbers for a textstring when for

abcdefghijkl stand the numbers
79 81 82 83 84 85 86 87 88 89 91 92

The textstring to "decode" is for example is 'adgjk'

The result (79 83 86 89 91) should be added into a table by Looping.

rs.Addnew
rs("Letter")= myarray??
rs("corNumber")= myarray?
rs.update
rs.movenext

Something like this.

But I cannot define and Setup the Array, which should be the best way for doing this.

The Array does not change its Content nor its Dimension.

Both, letters and numbers are strings.

View 14 Replies View Related

General :: Find Records That Match Criteria Of Multiple Fields

Mar 12, 2013

Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.

My query shows:

field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b

I would like my query to now display a third field and group field :

field1 field3
apple both
banana a
carrot both
dog b
elephant b

View 1 Replies View Related

General :: Find Average With Blank Fields In Access 2010

Nov 29, 2012

I am trying to find an average of four fields in either a form or query. Basically I have figures for [Grade 1], [Grade 2] but [Grade 3] is an empty field. I need to include all three field because there are sometimes 3 grades, but a majority are 2 grades. How to do so?

View 2 Replies View Related

General :: Specific Text Display In Field Separated By Commas When Fields Checked

Aug 11, 2012

I have a few bit fields that, when checked, specific text need to be displayed in a field separated by commas.

Example:
Child 1 = checked
Child 2 = not checked
Child 3 = checked
Child 4 = not checked

Output:
Child 1, Child 3

I did find this sample expression that I am using in another area and it is working perfect and I understand it works based off of string length.

Mid(IIf(Len(Expr1), ", " & Expr1, "") & IIf(Len(Expr2), ", " & Expr2, "") & IIf(Len(Expr3), ", " & Expr3, ""),Len(", ") + 1)

What is the best way to accomplish this?

View 4 Replies View Related

Making Specific Fields In A Current Record Copy Themselves When Making New Record

Mar 28, 2005

;) Hey everybody,

I am working on a database used in recording device characteristics/test information. The main table of information has dozens of columns for test/part detailed information. When inputing the data for each specific test, many of the info. details are repeated when testing say 20 devices of the same part all at once. Rather than retype every piece of detailed information in every field, everytime, is there an easier way? Does anyone know of a way to make specific fields copy/paste the previous record's information in the fields automatically when a new record is created? Please, if anyone could help or has ANY ideas, let me know...

Thanks

gunwax

View 9 Replies View Related

Table With Multiple Like Fields That Need To Display Into Array-like Format

Jan 8, 2013

I have a table that contains the following;Company name, PositionID, Firstname1, Lastname1, title1, gender1, Firstname2, Lastname2, title2, gender2, Firstname3, Lastname3, title3, gender3 [...up to 50 names]

The data is uploaded to my table from a 3rd party database source via .csv file.I can capture up to 50 names, titles and gender per record. I would like to display this data in a table/array like format. I'm not too good at VBA but I can use expressions for unbound fields.I have attached a file that shows the data in a format I would like to see in Access displayed in a form.

View 2 Replies View Related

Copy Files Into Specific Folder

Apr 16, 2012

I have created a database that saves a link of technical data. Now i have forms where you can add the hyperlink of the data in the database. You can also type information about the data into the form (like location, what kind of part etcc.)But I was asked to save the data in a specific folder that you can select.

So what I have to do is:

Browse for a file in a form.Save the hyperlink of the file in the database.Don't save the file in the database, but copy the file that's selected when browsing to specified folder

View 12 Replies View Related

Modules & VBA :: Find And Copy Values Between Sheets

Aug 20, 2013

I have this Sheet1 which is a manual input, I need to copy the values from Sheet 1 to Sheet 3, then I have Sheet 2 where there is data auto generated, How can I vlookup values copied from Sheet 1 and find it in Sheet2 ??? Is it possible using the vba code?

I should find the values copied from Sheet 1 in Sheet 2 and copy the values seen to sheet 3?

View 2 Replies View Related

General :: Reading Multi-line Column Values Into Array?

Aug 19, 2013

I am working on setting up a Document Control System and have a table, called List_of_SOP where I have, amongst others, the following fields:

- Document Title
- Document Number (unique identifier)
- Responsible
- DL (where this is a multiline lookup column where one ticks the names of the people to whom this document needs to be distributed)

I want to be able to fill an array with the checked values of the DL field, given a particular Document Number. So far, the code is somewhat on these lines:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim db As DAO.Database
Dim strSQL1 As String
Dim strSQL2 As String

[code]....

View 9 Replies View Related

Modules & VBA :: Copy Query Info To Specific Excel Cells

Mar 7, 2014

I have spent the day using Access 2010 and attempting to move information from a parameterized query into specific cells in an excel template. It runs smoothly until I attempt to reference the query at which point I run into th error "Too few parameters. Expected 1." Currently my reference code looks like this:

Dim T As Recordset
Set T = CurrentDb.OpenRecordset("SELECT [8D Data].ID, [8D Data].[Customer Closed], [8D Data].[Days Open], " & _
"[8D Data].[Open Date] , [8D Data].[QN #], [8D Data].[Last Report Date], " & _
"Leaders.[Leader Name] , Leaders.[Leader Title], Leaders.[Leader Phone #], " & _
"Leaders.[Leader Email], [8D Data].[Part Description], [8D Data].[Customer P/N], " & _
"[8D Data].Customer , [8D Data].[Vehicle Year], [8D Data].[Problem Description]" & _
"FROM [8D Data] INNER JOIN Leaders ON ([8D Data].Lead = Leaders.ID)" & _
"WHERE ((([8D Data].ID)=[Enter QCR #]));")

View 4 Replies View Related

Forms :: Copy Record From Previous Field If No Specific Combobox Value

Oct 30, 2013

I have an access form that needs filling in daily by various people.

It's to document changes to a website and I currently have a combobox box set up for the various sections to state whether they are AMENDS, REVERTS or NO CHANGE.

I have set conditional formatting to then highlight these sections but am also trying to get it to work so that if the user chooses "NO CHANGE" then the data for that field copies over from the previous record.

I have set this up in the AfterUpdate code for the combobox, but nothing is happening, not even an error....

Code:
Private Sub COMBOBOX1_AfterUpdate()
If Me.COMBOBOX1 = 3 Then
Me.[FIELD_TO_CHANGE] = DLookup("[FIELD_TO_CHANGE]", "tb_TABLE", "[ID]=Forms![form_FORM]![ID]-1")
End If
End Sub

(Where 3 is the value of NO CHANGE in the combobox, and FIELD_TO_CHANGE, tb_TABLE and form_FORM being the names of the various elements)...

View 1 Replies View Related

How To Find Specific Database Object

May 3, 2005

I am wondering if there is a quick way to find a database object (table, query, form, report) other than having to scroll through them in the database window.

E.g. I have many many queries and when I need to find one to make changes to it, it often takes me a while to find it when scrolling thru the queries in the database window.

Has anyone ever programmed anything whereby you can type in the object type and name and then it will be highlighted in the database window?

This would be a great time saver.

Thanks,
BJS

View 8 Replies View Related

Using 'Group By' To Find A Specific Record...

Oct 10, 2005

Hi,
I have a table with various customer items, a current price and the date the price changed.

EXAMPLE DATA

CustID/ItemCode/DateChanged/Value
AAA 21 01/01/2004 £3.00
AAA 21 01/06/2005 £5.00
AAA 21 01/03/2005 £4.00
AAA 22 01/01/2005 £6.00
AAA 22 01/06/2005 £7.00
BBB 25 01/01/2005 £8.00
BBB 25 03/07/2005 £9.00
BBB 26 01/06/2005 £1.00
BBB 26 01/09/2005 £2.00

Note: Dates are in "dd/mm/yyyy" format. Also, not all data is in order in the table. It’s not how I would have designed it but that’s what I need to work with!

I would like to run a query that groups by custID & itemcode and also the most recent date for any price change. I would then like the query to display the relating price for the record it pulls out. (EG for custID 'AAA' and item 21, it would display the price of £5 as that is the value for the most recent price as of 01/06/2005)
I then want to link the custID & itemcode to another table that has sales in, to check that we are using the most recent price.

Is there a way I can do this via a query or do I need to put some code together?

Cheers
Red [CODE]

View 2 Replies View Related

Find A Specific Record To Return A Value

Aug 15, 2007

Having problems with the following function to goto a specific record and return a value from a field in that record.

Code:'Public Function intFieldZConv(strICAO As String) As IntegerPrivate Function intFieldZConv() As Integer 'TEST PURPOSE ONLYDim strICAO As String 'TEST PURPOSE ONLYstrICAO = "KTCM" 'TEST PURPOSE ONLYDim cnCurrent As ADODB.ConnectionDim rsFieldInfo As ADODB.RecordsetSet cnCurrent = CurrentProject.ConnectionSet rsFieldInfo = New ADODB.RecordsetrsFieldInfo.Open "tblFieldInfo", cnCurrent, , , adCmdTable'rsFieldInfo.Index = "FieldICAO"'rsFieldInfo.Seek "=", strICAOintFieldZConv = rsFieldInfo!FieldZConvSTMsgBox intFieldZConv 'TEST PURPOSE ONLYrsFieldInfo.ClosecnCurrent.CloseSet rsFieldInfo = NothingSet cnCurrent = NothingEnd Function

strICAO is received from another function which is to be used to determine the specific record in the table "tblFieldInfo". Once that record is referenced, the value from the field "FieldZConVST" is returned.

My problem area is highlighed (index and seek) which I receive an error stating "method or data member not found". Any help would be appreciated on how to finding a specific record.

Thanks

Jeff

View 6 Replies View Related

Find Info For Specific Month

Aug 26, 2007

I have many queries for separate types of income that when criteria is entered on the switchboard, a report is created for either a specific month or range of dates. This works fine. Now I need to create one report that shows all 7 types of income in one spot, but once again based on a specific month or range of dates. I have a query that produces the entire income for all dates, but can't get it narrowed down to the specific criteria.

View 8 Replies View Related

Database With Many Tables; Find A Specific Column

Mar 30, 2006

Hi well as the title says i have a database with a LOT of tables, and i need to find the tables that contain a certain heading, eg reference 6, is there an easy way of doing this?

View 1 Replies View Related

Find Unique Products To Specific Build ID's

Dec 14, 2006

I have a table with 5 fields:

Build ID Process Product Product_De Operation_

What I am trying to accomplish is as follows:

Each Product can be in the table mulitiple times depending on how many Build ID's it has. So if Product 123456 has Build ID's G004, E818, N005, F813, D024, C879 it will show up one time each for each Build ID. What I want to query is unique Products that are for Build ID's D024 & C879 only and not for G004, E818, N005, & F813.

How can I accomplish this with a query. I am sure I am making this harder than it is but I sure need help.

View 1 Replies View Related

Modules & VBA :: Find And Delete Specific Record

Oct 10, 2013

Table Name: Admin

Field Name: userid, admin (Y/N), Password, ConPw, PasswordReset (Y/n), Createdby

Trying to run a vba to find and delete records that was "createdby" the current user. Enviorn("username")

View 2 Replies View Related

Modules & VBA :: Looping Through Columns To Find A Specific Value

Nov 20, 2013

This query returns a running asset balance for 60 months. Each month is 1 column.

1) I need to create a query based on those results that can cycle through each column and determine if it is NEGATIVE. Then if it is negative, I need to perform some math on it to determine how to get it back up to being positive.

2) I need to loop through X columns based on the assets lead time. Each lead time varies and I have a master table that contains it. So if the assets lead time is 3 months, I need it to move over 3 columns and return that value. If it is 6 months then I need it to return the value in the 6th column.

View 9 Replies View Related

Queries :: Find Specific Skills Of Employees

May 5, 2013

I am working on a school project called employee skills. I have a table of skills, each has a check box, which if checked is true. I am trying to write a query that will find who has a specific skill.

View 6 Replies View Related







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