Modules & VBA :: Inserting From Excel Number Of Query Values And Destination Fields Not Same

Jan 3, 2014

I am just beginning to manipulate Access from Excel.

I am trying to insert a row into a table. I am adding values for every field (8) except an Autonumber.

I have 2 Insert strings which are identical except for the fact that the one that doesn't work, doesn't specify which fields I am entering data into, which I presume shouldn't be necessary in this case.

When I try to use the second version I receive the error message...

Number of Query Values and Destination Fields are not the same.

The string that works perfectly is:-

Code:

strSQL = "INSERT INTO Clubs (ClubNumber,ClubName,ClubGrade,ClubRegion,ClubPosition,ClubHasHistory,clubinleague,cluboriginalposition) VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"

The one that generates the error message, which is identical except that I have removed the list of fields is:-

Code:

strSQL = "INSERT INTO Clubs VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"

The execute command is:-

Code:

gcnConnection.Execute strSQL, , adCmdText + adExecuteNoRecords

View Replies


ADVERTISEMENT

Number Of Query Values And Destination Fields Are Not Same - Runtime Error 3346

Dec 30, 2013

I am receiving the error above when I try to execute the code below. I have checked the fields in the code as well as the table and I can't see what I am missing. The tblMeasure table which is the subform have the following fields:

MUserLoginID - NumberMainMeasureID - AutoNumber - Primary KeyMeasureName - TextMPositonName - TextMeasureScore - NumberMeasureWeight - NumberMeasureTotal - NumberMeasureDesc - MemoMStaffApraisedID - Number

The UserDeatils table which is the main form have the following fields:

StaffID - NumberStaffName - TextDepartmentName - TextStaffPosition - TextStaffGrade - TextStaffBDate - DateStaffEDate - DateStaffApraisedID - AutoNumber - Primary Key

The link between the two forms are StaffApraisedID to MStaffApraisedID then StaffID to MUserLoginID then StaffPosition to MPositonName. When I select a member and click the Duplicate button I received the error above. The area highlighted in red is where it errors out.

Code:
Private Sub cmdDuplicateData_Click()
On Error GoTo Err_cmdDuplicateData_Click
Dim OldStaffID As Integer, NewStaffID As Integer
OldStaffID = Me.StaffApraisedID
'Add new record to end of Recodset Object

[Code] .....

View 1 Replies View Related

Modules & VBA :: Inserting Values Into Table With SQL

Sep 12, 2014

I'm trying to insert a set of values into a table using SQL.

Here's my code:

DoCmd.RunSQL "INSERT INTO tblAuditTrail([DateTime], [UserName], [RecordID], [Action], [FieldName], [OldValue], [NewValue])VALUES (" & Now() & ", " & User & ", & Me.CSM & ", " & 'EDIT' & ", " & 'Location' & ", " & Me.txtTranFrom & ", " & Me.txtTranTo & ");"

However, this returns a compile error.

I reference User as Environ("USERNAME") earlier in the code. Everything else pulls from the current table ("tblInventory").

I'm lost, since I've never attempted to use SQL in VBA.

View 3 Replies View Related

Modules & VBA :: Loop Through Values In ComboBox / Set Query Criteria / Export To Excel

Jul 24, 2014

I am a relative newbie to VBA, and not very familiar with loops, but I need to add a loop to my function that exports a query with criteria contained in a bound ComboBox on a form. I've gotten my code to work fine without the loop, but I would like to export one file for each item "Team_ID" contained in the ComboBox without the user having to manually select and re-run the function each time. Here is what my code currently looks like:

Code:

Option Compare Database
Option Explicit
Public Function CreateQCChartsforReports() As Boolean
Dim qdf As DAO.QueryDef
Dim strSQLStatic As String
Dim BookName As String
Dim BookName2 As String
Dim intCounter As Integer
Dim cboCode As ComboBox

[code]....

View 1 Replies View Related

Modules & VBA :: Exporting Query With Combobox Text Values Into Excel File

Sep 17, 2013

I a trying to search some product from a search button and two combo boxes text values ,and on serch the vba code is :

Code:

Private Sub Command4_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varRecords As Variant

[code]...

I just dont know,the fuile is created on click of button but with headers only,dtaa is not coming but wheni debug ,in immediate window,data is oming but just not coming in excel file.

View 4 Replies View Related

Modules & VBA :: Get Number Of Unique Values Without Separating Values That Belong To Same Block?

Dec 5, 2014

I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?

So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.

If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.

For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.

So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.

There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.

A few notes.

1.Getting as close as possible to the requested number is a requirement.

2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).

my table (NR_PVO_120)
Row OtherID Fax
1 11098554 2063504752
2 56200936 2080906666
3 11098554 7182160901
4 25138850 7182160901
5 56148974 7182232046
6 56530104 7182234134

[code]....

A few sample outputs

one solution is taking rows 1-6 and 26.

OtherID
11098554
56200936
25138850
56148974
56530104
56148975

Another solution is taking rows 1-4 and 10-14.

OtherID
11098554
56200936
25138850
56024315
56115247
56148974

This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.

View 12 Replies View Related

Forms :: Unable To Multiply Values Of Two Number Fields Together

Aug 8, 2015

I'm trying to multiply the values of two number fields together, but it is not working. I used to be able to do this easily in earlier versions of Access. Here's the problem:

On the form, I need field A x field B to appear in field C. I've tried this as an expression in the build event function on the control, but it is not working. My code reads as follows: = (field A) * (field B).

View 3 Replies View Related

Modules & VBA :: Clearing Values From Excel Table

Nov 25, 2014

I'm trying to set up code to clear values from an excel sheet. I need to leave the header row in place, and clear data from the sheet. Here's what I have:

Code:

Option Compare Database
Option Explicit
Sub ClearSignUsedXL()
Dim wb As Object

[Code] ....

This has been alternately throwing me a 438 Object doesn't support this method error and a 1004 Application or Object defined error as I've tried variations on Range, Row, Delete, Select etc.

View 5 Replies View Related

Modules & VBA :: Error 2391 Field F1 Doesn't Exist In Destination Table - Access 2013

May 3, 2014

I'm getting error 2391 field 'f1' doesn't exist in destination table

Code:
Dim FileBrowse As Office.FileDialog
Dim varFile As Variant
Dim sFile As String
Set FileBrowse = Application.FileDialog(msoFileDialogFilePicker)

[Code] ....

View 1 Replies View Related

Inserting Values

Nov 16, 2006

HI,
I would like to insert values to differetn tables using a form, is that possible with just access? or does it need VB script or SQL, if so, is there any example to follow.
my form made of a combination of two tables attributes ad when I enter say:

name
tel number
address
car
car reg
booking time

then I would need that info to go to the table it belongs to, as there is three tables, one is the car, the other is customer and the last is booking.
thanks

View 1 Replies View Related

Modules & VBA :: Pick ArrayID From A Form And Send Corresponding X And Y Values To Excel

Jun 13, 2013

I have hit a roadblock trying to send data to excel. I have created a table of Arrays where each record has an ArrayID, a record ID, an X unit and a Y unit. I want to be able to pick an ArrayID from a form and send the corresponding X and Y values to excel as part of a Linest calculation.

View 3 Replies View Related

Modules & VBA :: Import Multiple Excel Files With Different Number Of Sheets Into Table

Jul 12, 2015

some code I've come up to in order to import multiple excel files each of them with a different number of worksheets into an access table. The procedure is called from an Access database. The problem I have with the code is that when it encounters a workbook with only one worksheets (e.g. Sheet 1) it gives the error that "Sheet 2$" is not a valid name. When geting to a workbook with 2 sheets it says that "Sheet 3$" is not a valid name and so on and so forth. Is there a way to "check" the number of sheets in the workbooks and when it has only one sheet to transfer it and go to the next file?

Below is the code:

Code:
Sub ImportExcelFiles()
Dim strFile As String 'Filename's
Dim strFileList() As String ' File Array
Dim intFile As Integer 'Number of files
Dim filename As String
Dim path As String
DoCmd.SetWarnings False
path = "D:Tranzactii"

[Code]...

View 3 Replies View Related

Modules & VBA :: Excel File When Formatted In Access Shows Wrong Number

Nov 7, 2013

I am opening an excel file from access and changing the formatting from "dd/mm/yyyy hh:mm" to number with 15 decimal places.Then I am linking the file to the database and subsequently doing a lookup on the date time on the access form against this table.The excel file when formatted from vba in access shows the wrong number

datetime = 03/05/2013 11:26
database number = 41397.4763888889
Excel file number = 41398.4763888889 (which would equal 04/05/13 11:26)

now if I do it manually then I get 41397.4763888889 or if the macro is in excel I still get 41397.4763888889.Also I have tested the code with manually editing the excel file and this works fine.

Code:
Private Sub Command288_Click()
Dim s As String
Dim t As Integer
Dim ws As Worksheet
s = LaunchCD(Me)
MsgBox (s)

[code]....

also this brings up a form and then I select the field required for the lookup and with the button is pressed the rest of the code runs as follows.

Code:
Private Sub LoadData_Click()
Dim w As String
w = Forms![frmList1]![Combo0]
w = "[" & w & "]"
Dim dtA As String

[code]....

View 7 Replies View Related

Queries :: Inserting A Record Number?

Apr 9, 2013

I have a query with records,

I want a field named RECORD NUMBER beside it I want a consecutive number increment EG

RecordNumber - RecordDecription - RecordTitle
------------------------------------------
1 - TITLE001 - TITLE001
2 - TITLE002 - TITLE002
3 - TITLE003 - TITLE003

is there a formula i can use in my query to create this?

View 5 Replies View Related

Modules & VBA :: Check If Values In Column A Of Excel Sheet Also Present In Access Table

Sep 2, 2014

In sheet 1, In column A all the Batch numbers are present. So I want to check if each of these is present in Access table named "tblmain". If its present then display its corresponding Policy number in Column B of attached workbook.

View 1 Replies View Related

Inserting Values Into Multiple Tables

Aug 22, 2005

I am using an unbound form to insert data into several tables, all related, at the same time. Please let me know if what I am trying to achieve is too ambitious!

I am developing a material sample library... any sample could be one material, two materials or three materials. Let us say that A, B, and C are three primary materials.
I have three Tables: tblX, tblY, tblZ.
tblX stores details for each individual material. Its structure is as follows:

tblX
XPK | ID | value1 | value2 |
01 | A | asdfas | asdfdf
02 | B | dfasdfa | sdfaf
03 | C | asdfffd | asdfg
(here, XPK is the primary key, ID is the name of the material and value1, vlaue2 are other descriptive fields...)

tblY stores materials that are combinations of two primary materials:
tblY
YPK | 1st | 2nd | ID | value1 |
101 | 01 | 03 | M | keiury |
102 | 02 | 03 | N | kjgeiih |
103 | 03 | 01 | P | djlkgoi |

(here, YPK is the unique ID for a sample, the '1st' and '2nd' fields are simply the primary keys from the tblX. ID is the name of this new material and value1 has descriptive values for samples M, N, etc.)

tblZ is the next level of material, made of combinations of materials from both tables tblX and tblY. it is like this:

tblZ
ZPK | 1st | 2nd | 3rd | 4th | ID | value1 | value2 |
1001 | 01 | 03 | -- | -- | R | asdprw | mnvd |
1002 | 01 | 102 | 02 | -- | S | adsfd | oirtyr |
1003 | 103 | 02 | -- | -- | T | werwq | pojfgr |
1004 | 02 | -- | -- | -- | U | alkfdp | uioite |
1005 | 01 | 02 | 103 | 102 | V | keqwei | oirewj |

(here, ZPK is the unique identifier, the primary key. as you can see, 1st, 2nd, 3rd, 4th are references to materials from either tblX or tblY or none, ID is the name of the material and so on.)

So, going back to the original primary materials, a sample could be a combination like A + (A+C) + B...

I hope I am clear enough till this point.

My unbound form (which is a very complicated one by now!) is where a user will start with such a sample and start putting in details for each of the primary materials. As he/she logs each individual material, I store that data in a temporary table (tbltemp). When all the primary materials are fed in, the user hits a 'Save' button, which is supposed to do the following:

1) save each primary material from tbltemp to tblX
2) extract their primary keys and then insert that into the next level table, either tblY or tblZ.

I have been working with ADO recordsets to 'addnew' data to tables and am adept with that. I have never simultaneously extracted data from one table and inserted into another. similarly, i have never extracted more than one record, and inserted them into a single record in another table, etc.

I will appreciate any guidance, both at conceptual level and at operational level, that I can get. If you would like to see my database, I could arrange to have it accessible; there is nothing confidential (only higly complicated, I think!).

If you are still reading, I already owe you a ton of thanks!!

View 7 Replies View Related

Modules & VBA :: Create Unique Reference Number Based On Field Values For Record

Jun 18, 2015

I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.

The first field is called Operation Number.

The second is Bag Number.

The reference number needs to be in this format: 19C.3.1

Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.

This is kind of like library catalog numbers. Not sure how to do this.

View 2 Replies View Related

Modules & VBA :: Export Visible Fields Only To Excel

May 15, 2014

I have a query tool that allows users to create their own custom queries. Basically, it's a form that allows the user to check boxes for the fields they want to see. The code behind it simply hides the fields in the query for which the user has not checked the box. That works very well.

My problem is I would like to have a command button that will export the query to excel. The OutputTo and TransferSpreadsheet commands will just export the whole query into excel regardless of whether or not the field is checked. I'm looking for a way to only export the columns the user has checked. Is this possible, and if not is there a workaround that would do something similar?

View 7 Replies View Related

Modules & VBA :: Specific Fields From Access To Excel

Apr 15, 2015

VBA for a click button that extracts specific fields in an Access DB in to an Excel spread sheet.

View 9 Replies View Related

General :: Inserting Values From Combobox In Table

Mar 6, 2013

I am a newbie to ACCESS. I am stuck with inserting values of combobox directly into a record in table.

I have five different tables(each has only one cloumn) and a complete full_details table(contains 5 cloumns).

I have a simple form with five comoboxes and a save button on it. The five comboboxes are populated from five different comboxes at the time of form load. Now, I want to add values selected from these comboxes in fulll_details table.

My form is unbound. I tried with VBA code, on the save button click, I have written ,

Private Sub save_Click()
CurrentDb.Execute "Insert into Full_Details(Col1,col2,col3,col4,col5) Values ('" & Combo0 & "','" & Combo1 & "','" & Combo2 & "','" & Combo3 & "','" & Combo4 & "');"
MsgBox "Record Saved !!!", vbInformation, "Success"
End Sub

It gives me some error : "You can't reference a property or method for a control unless the control has the focus property set"....

See, above query works fine , if I set focus of one combobox,but it adds only 1 combobox value in full_details table as only 1 column value.

View 6 Replies View Related

Queries :: Inserting Values From A Listbox Into A Table

Jun 28, 2013

inserting values from a listbox in to a table. I have a table with a field named: PrefReports. This field will hold data from values in a list box that the user selects.

Table: Profiles
Field Name: PrefReports Type: Memo

The user utilizes a form to input the values necessary for the query. I then have the query tied to a command button. When the command button is clicked, the query is executed.

The query is as follows:

INSERT INTO TblProfiles ( [Employee ID], FName, LName, Barcode, PrefReports )
VALUES ([forms]![FrmCreateNewProfile]![txtEmployeeID], [forms]![FrmCreateNewProfile]![txtEmployeeFName], [forms]![FrmCreateNewProfile]![txtEmployeeLName], [forms]![FrmCreateNewProfile]![txtBarcode], [forms]![FrmCreateNewProfile]!lstReports.Value);

How can I take what the user selects in the list box, and insert into my TblProfiles.PrefReports field? When I run the query, all other information makes it to the table except the list box values.

View 3 Replies View Related

Modules & VBA :: Check Policy Number In Access Table And Populate Related Data In Excel Sheet

Aug 14, 2014

See attached the Workbook. I need to check the policy Numbers in Column A of all the sheets in the attached workbook if its present in Access Table. If yes then write the corresponding ScanDate and BatchNo from Access table to columns I and J of all the sheets. I need to write VBA code to perform it.

In the attached workook, only Sheet1 contains the data but in actual there will be data in 5 sheets in the workbook.

View 1 Replies View Related

Modules & VBA :: Count Number Of Fields Have X As A Response

Nov 20, 2013

I have a form that records followup activities for protocols. The form allows the user to enter up to six f/u actions from a dropdown menu in six separate fields (called fu1_action, fu2_action, etc).

I am trying to write a query that retrieves only protocols that only have one f/u action selected as "telephone correspondence" and am having trouble. It seems I could write a function in VBA that would somehow loop through the 6 f/u action fields and give a value of 1 to those that have 'telephone correspondence' as a response, and then add them up to give a total of calls for that specific protocol, and then in a separate query only retrieve those that have a total number of calls = 1, but how to get started in writing this.

View 3 Replies View Related

Modules & VBA :: Sequence Number In Query Use A Starting Number

Sep 20, 2014

creating sequence number in a query using a starting number. For example i want to use the starting sequence number as 5

---SEQNUM---------COLOR-----------
-----5------------------blue--------------
-----6------------------red---------------
-----7------------------orange-----------
-----8------------------black-------------

View 2 Replies View Related

Modules & VBA :: Assigning New Values To Blank Fields

Dec 19, 2013

I got one months table containing a reporting_month ,timeperiod and an Index column ID with data type Autonumber.Basically I want to search through the table whenever the User types in a new reporting month or timeperiod over the dialogue.Now I want to realize the following options:

1. The user types in a new reporting month, when a record in the months table exists with a timeperiod and a blank reporting month field, it should be assigned there. For example the User types in reporting month = 032014 it should be assigned to the Time_Period Value = 042014-032015

2. Vice versa, the user types in a time period. This value shuold be assigned to the blank field beneath the existing reporting month.

View 14 Replies View Related

Queries :: If Function And Inserting Null Values In False Part

Oct 7, 2013

I have a calculated field (Bhours) with the following IIf function:

=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)

It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".

View 7 Replies View Related







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