Modules & VBA :: Populating Rows With Set Values?
Sep 11, 2013
I have a Machine Running Conditions database that records running conditions for 2 machines. The table is set up like this
Code:
LotNumber Parameter Condition
130506m11 Screwspeed 100
130506m11 StockTemp 50
130506m11 KniveSpeed 75
In reality there are many more machine parameters (about 30). We have two machines that usually are set with 2 different set combinations of parameters. The combinations vary at times but are usually one or the other
Currently the user is going in and selecting the parameters from a combobox manually for each lot number.
To save him time and work, I want those fields auto populated with the parameters for a machine. So the user will hit a button for machine 1 or 2 and the parameter rows will fill with that machine's correct parameters.
View Replies
ADVERTISEMENT
Jul 24, 2012
I have an infopath form, which people in my company can fill out to order parts. The form is linked to an Access database. The primary keys for the database are the Order Number and the Line Number (where the specific part info is in the system). The form also takes info about who entered the order, when they entered it, etc.
My problem is that while the form will be used for only one order, it may be used to order multiple parts. So the infopath form has a repeating section with a table where they can enter multiple line numbers and part names, etc.If I use the form to enter just one part, everything works great. But when I use the repeating section to add multiple parts, the information specific to the repeating section is added to the database but the rest of the information (who entered, when the entered, etc) isn't.
My intial idea was that since the first part is entered correctly and the subsequent parts are missing some information which is already in another line in the database. Maybe if there was a way to pull the information from another line into the lines with missing info within access would solve my problem. So basically automatically populating the rows with matching Order Numbers with the information that is missing.
View 7 Replies
View Related
Feb 4, 2008
Hi,
Hopefully this is a really simple requiring a simple answer.... hopefully
On one of my forms I have a combo box thats values are generated by a parameter query. On my computer and a couple of others i tested it (on the same network) this combo box populates fine, but i put it on a couple of other computers today and it doesn't show any values. I checked and the query itself runs fine, plus a combo box that runs off a table populates fine.
not too sure if its relative but the database is split with the tables sitting on the network share and the database sitting on the main computer. or perhaps this could have something to do with blocking unsafe expressions.. i am not sure
any help on this would be greatly greatly appreciated
View 8 Replies
View Related
Mar 2, 2014
I have a table having fields start date and end date. I need to calculate difference in the dates and store the values in a new column in the same table. I am able to write the query for this but am clueless as to how to put in these values in a new column in the table.
View 2 Replies
View Related
Jan 16, 2014
I use Access 2003. I am trying to populate a list box with the names of files found in specific folder in my computer. The problem is that if the file name has a comma (,) in it, so the name is being separated in two parts - first what is before a comma and second what is after. I need them exactly as they appear in directory in order to open the files from this list box.
View 2 Replies
View Related
Aug 25, 2006
I am trying to devise a way to display query results in a row instead of a column. The table on which the query is built has this structure:
Column1: Index (pkf)
Column2: Group (int)
Column3: Position (int)
Column3: ItemCode (txt)
When I do a select query, the data comes back like this:
Group Position ItemCode
11A
12B
13C
14D
15E
16F
Etc.
I want to arrange the data this way:
1ABCDE (Etc)
I keep thinking that a cross-tab query is the answer, but all I seem to be able to do is to get counts of Position 1, 2, 3 Etc.
I’m baffled. Does anyone have a suggestion?
Thanks,
Rick
View 4 Replies
View Related
Feb 6, 2013
I need to compare the sum of a values in a column of rows with the same id. For example:
Code:
ID Value1 Value2
122222 20 40 (these are okay, the sum of Value1 in rows with same ID equal value 2)
122222 20 40
333333 100 100 (ok)
111111 50 100
111111 50 100 (not ok, the values with same id add to 150, so the first row containing this id should be deleted so the the total of Value1 equals Value2)
111111 50 100
View 1 Replies
View Related
Nov 5, 2013
I have a report being generated that pulls in a bunch of data. The processed column is a boolean value.
I want to be able to set the text of a label to be equal to the "material cost" for all the rows where is processed is set to false.
Picture of the report: [URL] ....
View 3 Replies
View Related
Jun 17, 2013
I am working with an Excel file of raw data aggregated from an annual customer relationship survey that has been sent out since 2010. The file has approximately 20,000 rows, meaning the survey has been taken around 20,000 times. Unfortunately, each time a customer takes the survey, it is included in the raw data as a separate entry. Therefore the file has numerous duplicate email addresses, corresponding with unique data for each time the survey was taken. Another issue regarding the data, is that in the first year the survey was sent out, the distribution mechanism "broke" and the survey was sent out multiple times (and completed multiple times) in the same year by the same customer, so the surveys are not necessarily uniformly distributed, if that makes sense.
I have been interested in isolating the common respondents (those who have taken the survey across multiple years, albeit not necessarily consecutive years). Up to this point, these respondents have been isolated manually using a pivot table, however I am now looking to enter the raw data into SPSS (a statistical analysis package), in order to view the drivers of these common respondents.
Therefore, I would like to be able to isolate these common respondents and the data corresponding with their surveys from the raw data in a separate worksheet or file. I have tried various formulas to do so in Excel to no avail. Is there anyway to accomplish this in Access or would a more complex database be needed?
Ideally the final product would have the common respondents' information from multiple surveys in one row and would be able to be filtered by feedback date (found in the raw data), so that hypothetically one could select a month and be able to tell how many customers considered to be common respondents completed a survey within that month.
View 8 Replies
View Related
Oct 16, 2006
HELP! I’m working for a charity in a small town Tanzania and there is not a soul for miles who knows anything about access.
I have created a database to track requisitions of Office Supplies. I have table full of the requisitions which contains multiple rows for each item we have in the store and the quantity requisition on each occasion.
What I want is a query that will calculate the total quantity of each item requisitioned in a month. Is there an easy way to do this??
View 1 Replies
View Related
Jul 10, 2013
I have a table [VL] with four fields, [vl_id], [product], [vl_date], [valeur_liquidative].
The idea of the table is that you can input and update the value of each product on a given day.
I need to find the difference in days between successive dates (vl_date) each time that a product value (valeur_liquidative) is updated. Values aren't updated everyday as it is not updated during the weekends. I have had a go at this but have struggled..
A picture of what the table looks like is attached for reference.
View 9 Replies
View Related
Oct 18, 2014
I am trying to populate a combo box (cboVenderID) with one of the two queries below.
But "Set rs = db.OpenRecordset(strSQL)" causes a Run-time Error '3016': To few parameters. Expected 1.
I am quite new to VBA and SQL.
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intVenNum As Integer
intVenNum = Me.cboVenderTypeID
[code]....
View 4 Replies
View Related
Apr 1, 2014
I am populating Access table using a stored procedure from SQL Server. Here is the code that does the populating.
The problem area is the rst!id (highlighted in red). This is the Identity Column from SQL Server. When Access gets to 32768 it results in a blank in the Access Table. Ive Included the picture.
When I step through the loop and get to that 'id' I can see that the next value is there, by hovering over 'rst!id'. So I know that the value is not NULL but it does not record it in the table.
This continues for the remainder of the load, which is few more thousand rows.
Code:
Do Until rst.EOF
strSQL = "INSERT INTO tblStationPatronageEstimate (pax,transactions,time_band,day_type,entrance, " & _
" from_date,to_date,id,station_entrance_id,number_of_days,average_pax_per_day,
average_tran_per_day,vr_used_name,vr_used,userid, " & _
" time_stamp, completed,comment) " & _
[Code] .....
The results
View 3 Replies
View Related
Mar 5, 2007
Hi all,
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
How do I do this?
Kind Regards,
Matthew
View 1 Replies
View Related
May 29, 2014
I have an insert query that has been working just fine. I added three new columns (Paid, Balance, Invoiced) to both tables that I am using in my query. The query will insert the new records that don't exist in the one table just fine but it is not populating the new columns.
' Append records from aud_ck_reg (local table) to dbo_aud_ck_reg (table on server)
DoCmd.RunSQL "INSERT INTO dbo_aud_ck_reg ( audType, audDate, audUser, transid, customer_number_parent, transdate, create_1131, check_number, check_date, PW_voucher_date, " & _
"voucher_date, voucher_number, customer_number, credit, debit, fiscal_yr, month_number, calendar_month, calendar_year, calculated_cost, notes, CIT_number, Quarter, Hyperlink, Paid, Balance, Invoiced)" & _
"SELECT aud_ck_reg.audType, aud_ck_reg.audDate, aud_ck_reg.audUser, aud_ck_reg.transid,
[Code] .....
View 4 Replies
View Related
Jun 11, 2013
I'm writing to an excel file from two separate Access tables, for which I'm using two separate DAO.Recordsets in VBA.The first table has 190 columns, the second table has more than 66 columns, so more than 256 columns will be written to the excel file.So I'm not expecting any error due to any maximum of 256 columns, but still I'm getting an error while trying to populate the 257th excel-column:
Run-time error '1004': Application-defined or object-defined error.(The same VBA works fine when I use another table with less columns.)Even more strange: on another computer I did not get the error and I was able to populate more than 256 excel-columns.
Code:
Option Compare Database
Option Explicit
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim FileNameOut As String
Dim RowNr As Long
Dim ColNr As Long
[code]....
View 5 Replies
View Related
Aug 7, 2014
I want to know a way of populating unbound textboxes with data from a table without using the easy assigning data in the property of the box. I want it to be all done with code, which to me isnt a huge deal but I've yet to find answers on the web pertaining to this issue.
for example:
Say i was searching a primary key in one box and based on what ever is typed in i'd like it to populate the rest of the textboxes.
View 1 Replies
View Related
May 9, 2014
I'm trying to create a temp table, which is populated by a string, (taken from a recordset).
My problem is incorporating the String into the SQL statment, and making it work,
What I'm trying to do is to create a temp table, and populate it with the first record of the recordset, (which is an e-mail address).
(The recordset and the strings work fine). It's the SQL statement which doesn't work.
Here's the code I have :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~
Set DBS = CurrentDb
Set RST = DBS.OpenRecordset("SELECT Contact FROM Contacts_to_be_Mailed")
Dim My_Count As Long
Dim ContactString As String
RST.MoveFirst
ContactString = RST(0)
Dim strTable As String
strTable = "TempContact"
DoCmd.RunSQL "INSERT * INTO " & strTable & " FROM ContactString "
View 1 Replies
View Related
May 27, 2014
I have a form with a Listbox which lists Customers and a variety of customer information
I also have a table on the form that lists the donations that customers have made.
How can I populate the Table based on the customer that is selected in the Listbox?
There is an ID field that links Customers with Donations
I'm guessing I need some code in the ListBox_CLick() event that triggers the table to query or to Load with a query based on the ListBox.Column(0) which contains the ID?
View 2 Replies
View Related
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
Nov 28, 2013
I'm trying to populate a listbox from a recordset. I will explain what I'm trying to achieve.
I have a (continuous) form with some data from a table. On the form header I have a listbox, showing all (distinct) customers from that form.
The user is able to do some filtering. That all works great. But I want to populate the listbox with ONLY the customers on the form AFTER filtering.
One way I thought might me the solution was using RecordClone. But I'm stuck there. It must be something simple for an expert. I'm not entirely a newbie, at least not to VBA. Is there an easy way to refer the listbox' recordset to the actual recordset on the form?
View 1 Replies
View Related
Feb 23, 2015
I have a lookup value in a table, and I would like to remove all values of the lookup value from vba. How can i do it via vba? i've been trying to do it in a query but i have always some errors and i can not remove them.
View 2 Replies
View Related
Apr 9, 2014
Im working on a DB with multiple tables. My DB consists in a lot of tables, each one for a different element and his specifications (e.g.: transmitter, sensor, relay). The user can create loops selecting different elements (All the elements of the same loop have the same Loop ID).
I want that when the loop is finished the user can select one of the loops he has create and see all the elements and specifications he has selected for this loop. The problem is that when I want to show in a form or report all the elements the user has selected before I cant select each different element from his table and show in a form or report.Until now I have this code in a form to select an element from a report and show it, but it doesnt work very well.
-) List3 is a list box in my form where is the list of elements to select.
-) Boton1 is the button in my form to open the report when the element is selected
-) Transmitter Specifications is the report with the list of elements and his specifications.
Option Compare Database
Option Explici
Private Sub boton1_Click()
On Error GoTo Err_boton1_Click
[code]....
View 1 Replies
View Related
Nov 10, 2014
I need to write a code that checks if every 6 lines in a table contains a string. If it contains the string, then carry on looping, but if it does not, it will delete the current line and the previous 6 lines before it. The program should then continue looping through the entire table.
For example,
Code:
Apple
a
b
c
d
e
Apple
f
g
h
i
j
Apple
l
m
n
o
p
q
Apple
.
.
.
I need to find the string "apple" in every 6 lines. So, that means that the first 2 Apples are fine, but for the third one needs to be deleted as it contains 7 lines instead of 6.
View 14 Replies
View Related
Nov 16, 2013
I have a table:
"tb_skybox_Types", with the fields, [Policy_URN], [Box_Type]
Sample:
Policy_URN Box_Type
001 Standard
001 Sky HD
002 Sky +
002 Sky + HD
I'm mail merging this, so naturally it's giving me a row per box type against which I need to be one. Desired output:
Policy_URN Box_Type
001 Standard | Sky HD
002 Sky + | Sky + HD
I'm then going to make this into a temp table, then match the URN back to the customer table to get a single row per customer.
View 3 Replies
View Related
Nov 11, 2013
I have a form where we fill in information for supply of PPE to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the number of items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)
IssueIDDateProvidedItem_Name AmountIssued
0001 01/11/2013 Gloves (Orange) 10
0001 01/11/2013 Hard Hats (Black) 2
0002 02/11/2013 Hi Viz (Large) 5
The report I aim to generate from this should look as attached ...
The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.
View 4 Replies
View Related
