General :: Code To Create Several Rows In Table Not Working
Jun 4, 2014
I have an issue with a code that I have in a form which adds rows in a table as many times as categories chosen from a list. However, the code is not working correctly: it adds the information and creates a row with a category in blank, in addition to the rest of the rows with one of the chosen categories. I would like for this not to happen, to add only as many rows as the categories chosen.
My code is the following:
Private Sub cmdUpdate_Click()
Dim valSelect As Variant, MyDB As DAO.Database, MyRS As DAO.Recordset
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Tasks", dbOpenDynaset)
MyRS.MoveFirst
I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.
I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.
I am just starting out learning how to connect Access to Excel. From an Access form, I need to connect to an Excel spreadsheet, and loop through every row/column to look for data that matches a database field data, then populates the associated cell data. I don't want to import the spreadsheet since the user who created it is using it manually, and I am attempting to automate the task. This is probably the most elementary code, but I would like to start out with the best way to do this. What I am finding is code that appears to be much more complicated than I need, for more complicated situations.
I have a database with customers/orders etc. I need to create a form or something which will show if a duplicate customer is entered. I know about the duplicate records query wizard, but I also need an easy way to consolidate and delete records. When a duplicate customer is found, move the customers order/s [OrderNumber] to the existing customer and delete the new/latest customer record.
Customers are in tblCustomers, their orders are in tblCustomerOrders, [CustomerNumber] is the joining field.
I was thinking of just a date/time field for [DateCustomerEntered] with default value Now to keep track of the later customers?
a ER programm gives the following output to an sql code, which it pretends it should be for Access 97. Till now I haven't found an idea of how to create a table using the SQL statement CREATE. As a matter of fact it returns me an error of Create statement or something, even if I tried any available query form.
CREATE TABLE Algorithm_Property( Algorithm_Property_ID Integer NOT NULL, Layout_Property_Name_ID Integer NOT NULL, Data_Type_ID Integer NOT NULL, Submodel_Algorithm_ID Integer NOT NULL, AlgPropValue Text(255) NOT NULL, Global_User_ID Integer NOT NULL, Row_Time_Stamp Integer NOT NULL ) ; CREATE TABLE AppGenAttribute( AppGenAttribute_ID Integer NOT NULL, Attribute_ID Integer NOT NULL, DiagramId Integer NOT NULL, ModelId Integer NOT NULL, EntityId Integer NOT NULL, AttributeId Integer NOT NULL, InitialValueType Text(1) NOT NULL, ClientDefaultValue Text(254), CounterColumn Text(1), Global_User_ID Integer, Row_Time_Stamp Integer ) ;
I have an existing database being used for for order processing (normalized, working). The order table houses the general order info and a separate detail table holds 1 or more orderdetail records for each order in the order table, related by orderID.
A user has asked that I find a way to import her data into the system from an excel spreadsheet. She has individual columns for each type of order detail record, so for each "order" row, there may be 1 or more columns of "orderdetail"s that I will need to parse into the correct tables.
I imported the raw excel into a table, but I need to append that data into the order and orderdetail table rows (i.e. I have to create the order and orderdetail records that match every other record in the system).
It looks like I need to somehow perform a looped INSERT INTO [ORDER] (field1, field2....) VALUE (val1, val2) but there are those related detail records to contend with (the orderdetail table entries) which may be more than one insert....
I'm doing a subquery to select the top 5 of products for each supplier. The selection needs to be done randomly on the products for each supplier. For this I have made the following query (based on Allen Browne's example):
SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN FROM tblProducts_temp GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN FROM tblProducts_temp AS Dupe WHERE Dupe.SupplierID = tblProducts_temp.SupplierID ORDER BY RND(Dupe.GTIN) DESC))) ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;
The query returns a random number of products, but not a top 5. So for supplier X one time 3 products and the next time for supplier X 7 products.The query without the RND function, so just the top 5 works fine:
SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN FROM tblProducts_temp GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN FROM tblProducts_temp AS Dupe WHERE Dupe.SupplierID = tblProducts_temp.SupplierID ORDER BY Dupe.GTIN DESC))) ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;
MP No (Primary key), Sales, Date Entry, Specs, Email
The second Table is as follows:
MP No, Quantity, Description, DWG, Price
There is a "one-to-many" relationship between the two tables through the MP No field so whenever I go to the first table there is a sub-table for each MP No. Correct?can I create an extra sub table under the sub table already created?
We use a Database which has been constantly developed over 10 years using earlier versions of Access2003.We have Upgraded to Access 2013 and we are experiencing many conversion issues which I need to resolve.One key problem is that many of the earlier tables were developed with spaces in their names (Hindsight is a wonderful thing?). There are over 200 Tables that need changing. I would like to update the Table names replacing every "space" with an underscore"_". AT the same time I would also need to Update all of the QUERIES that use these particular Tables to be updated to reflect these changes made and still work as normal. I can do this manually but it will be very time consuming and perhaps someone has already had to do this when recently upgrading to Access 2013?
I programming a eBay upholder, and having an issue with the description, as there cant be any line breaks in the column. I have little knowledge of Vba programming. I think I can use the replace command to change the line breaks to spaces.
I have an old ERP system. The upload function is one line per value. So if I want to upload 7 models I need 7 lines, not one line with Qty of 7 however my source system sends the data as one line with Qty of 7
so in this example line 1 is fine. But line two needs 7 lines, and line 3 needs 7 lines and line 4 147 lines etc etc etc.so this is how the first three lines would look in the query.
I split my db to front-end and back-end. 20 users are using the database. But now I have the problem: In my admin front-end db I created a new table and some reports to the table, but this new table I have only in my front-end db and it doesnot appeares in back-end db - so that other users has no data in hte report for the new table. How I can create new table to be in back-and table too?
access where in on clicking a button on an access form, I can insert Field A, B & C (all containing email ID's) in to a new mail message in outlook. Similarly I have a subject field which needs to go into the new message as subject.
I enter data into the table using the form where the button needs to exist.
Hello, all! Can you please look at the following code and tell me why it throws a compile error of user type not defined. I'm trying to use this code to automatically pull in the previous room # and associated data into the next record, so that I don't have to enter the data every time, as sometimes there are fifty entries with the same room # and associated data. Can you tell me what it's wrong with it? Thank you!!
VB Code: Original - VB Code Private Sub Form_Current()Dim s As StringDim db As New ADODB.ConnectionDim rs As New ADODB.RecordsetSet db = CurrentProject.Connections = "SELECT Last(Room #) AS Room # FROM [tblFF&EBudget] WHERE Budget Line=(select max(Budget line) from [tblFF&EBudget])"Set rs = New ADODB.Recordsetrs.Open s, db, adOpenDynamic, adLockOptimistic'add to the tableWith rsMe![Room #] = .Fields("Room #")End WithSet rs = NothingSet db = NothingEnd Sub Private Sub Form_Current() Dim s As StringDim db As New ADODB.ConnectionDim rs As New ADODB.RecordsetSet db = CurrentProject.Connection s = "SELECT Last(Room #) AS Room # FROM [tblFF&EBudget] WHERE Budget Line=(select max(Budget line) from [tblFF&EBudget])" Set rs = New ADODB.Recordsetrs.Open s, db, adOpenDynamic, adLockOptimistic'add to the table With rsMe![Room #] = .Fields("Room #")End WithSet rs = NothingSet db = Nothing End Sub Thanks!
I have a 'tblStock' with fields 'ProductID', 'InitialStock', 'Buy', 'Sell' and 'UpdatedStock'. I also have a form 'StockUpdate' add values and also add new records to 'tblStock' .
If I have value [100] for IntialStock quantity, Buy [0] and sell [10], UpdatedStock will be [90] (that's done and fine!).
The problem is, I would like to make the UpdatedStock value [90] to be the NEW InitialStock, so that any BUY or SELL will keep updating the UpdatedStock and making it the NEW InitialStock for the next transactions and so on....
I am creating a table in access and I would like to color code some of the fields if it is possible. There would be multiple green fields to represent it has usable data, Yellow to represent the data is in process and Red to represent bad data. I do not want to color code fields using queries, forms or reports I want to color code the fields in the tables only.
I was just wondering if I could use a query and table to create pivot chart? and If so how I could do this? I need information from both in order to create the chart I want.
1) Create a table with a single text field 2) Enter some text values, including one which begins either "aa", "AA" or "Aa" (but not "aA"!) 3) Sort the field, and note where it puts the string(s) created in (2)
For me, they appear at the wrong end of the sorted list! I have tested this in Access 2007 and 2010 and it only happens in 2010. I'm using 32 bit Access on 64 bit Win 7 Pro.
Maybe this should be posted under VBA.... not sure. Havent seen answer that works yet. Its a form with an option group "PickWO" When you pick an option, different fieilds become visible-non visible. I created a field "PickWOvalue" to store the option value "1" or "2". That part works fine.(visible) The problem is when you reopen the form to view the records the option box value does not show or change the fields visible properties. And the on open cuauses an error. Anyone see the problem?
Private Sub Form_Open(Cancel As Integer) Me.PickWOvalue.Value = Me.PickWO.Value End Sub ````````````````````````````````````` Private Sub PickWO_AfterUpdate()
Call NotVisible
Select Case Me.PickWO
Case Is = 1 Me.ReInspectionDate.Visible = True Me.Price.Visible = False Me.WOPreview.Visible = True Me.PrtWO.Visible = True Me.WBMInvoice_.Visible = False
Case Is = 2 Me.ReInspectionDate.Visible = False Me.Price.Visible = True Me.cmdPreTag.Visible = True Me.cmdPrtTag.Visible = True Me.WBMInvoice_.Visible = True
I have an access database that links to a SQL server. My problem is when I made a copy of the Access database, made changes to one sub report and mapped to test tables and now I have VB code that runs the overall report where the SQL gets jammed on the join, (which I noticed through debugging) but when I take the same SQL and run it in the Query view - the SQL works.
1 table(1): record of people & contact details 1 table(2): list of events with check box's with the names of people from the other table 1 report: listing how many events people have attended.
When I add a new person to table 1 I want a field to be added to table 2 in the form of a checkbox, also when I delete this person I want this field to be deleted in table 2.how to make this an automated process.
I am trying to create a duplicate of a certain record in a table so that the user can then edit the new record - or old record even as it doesn't matter - as long as one of them is original. The idea is that any revisions / changes are stored the user may of made.
I have Header form and subform subform in it with following fields
Subform fields are :
1. MQE_No : (Text field as it will be like MG-120, MG230), Duplicate OK) 2. RPO_No : (Number field – Double, Duplicate OK ) The are project no. 3. ForemanNo (Number field – Long Int – Duplicate OK)
Header form fields are ;
1.ForemanNo – Long Int – No Duplicate 2.Other fields…
The subform field have ForemanNo One-To-many relationship with Header form.
Following project MG-411 need to enter (assign) to foreman no. 641
Actual data to store in subform
MG-411 (MQE_NO) 5421654 (RPO_NO) 641 (FOREMANNO)
I started to enter data into subform selecting the ForemanNo at header so records to enter in specific foremans account. Everything is fine here.
What I need is:
I would like to select / add another Foreman into the header and start entering same above data for another Foreman. Bcz sometimes we need to do like this in case previous Foreman may go on to a week vacation. We can not stop project for a week. So need to assign project to other foreman.
When selected the Project (MG-411) thru a combo to assign it with other foreman, it should give a short msg to user that this project has already been assigned to “XYZ” forman. Need to assign again “ (Y/N box )
If Y then data entered otherwise cancelled.
To achieve this, I did the following but only 50% success. (Used Cmbo CboRPO2 select MQE_NO). Tried to satisfy both condition but no use.
Private Sub CboRPO_AfterUpdate() If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO = " & ENO) Then MsgBox "RPO ALREADY ASSIGNED TO SOMEONE / FOREMAN", vbOKCancel, "WARNING!!!" Me.Undo Exit Sub Else
Dim Msg, STYLE, TITLE, HELP, CTXT, Response, MYSTRING If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO <> " & ENO) Then Msg = "RPO ALREADY EXIST WITH OTHERFOREMAN, ASSIGN AGAIN TO ANOTHER ?" STYLE = vbYesNo + vbInformation + vbDefaultButton2 TITLE = "!! ATTENTION !!" HELP = "TEST FILE" CTXT = 1000 Response = MsgBox(Msg, STYLE, TITLE, HELP, CTXT)
Else Me.MQE_NO = Me.CboRPO.Column(0) Me.RPO_No = Me.CboRPO.Column(1) Me.WORKSHEET_NO = Me.CboRPO.Column(2) Me.WORKORDER_NO = Me.CboRPO.Column(3) Me.WORK_DESC = Me.CboRPO.Column(4) Me.PL = Me.CboRPO.Column(5) Me.PipeLineKM = Me.CboRPO.Column(6) Me.DiaMeter = Me.CboRPO.Column(7) Me.PipeLength = Me.CboRPO.Column(8) Me.PipeLineArea = Me.CboRPO.Column(9) Me.P = Me.CboRPO.Column(10) Me.RPO_AMOUNT = Me.CboRPO.Column(12) Me.INV_AMOUNT = Me.CboRPO.Column(13) Me.Status = "WIP" Me.StatusID = 2 Me.CboStatus.SetFocus End If End If End Sub
I tried with following code also
'Dim cdn As String 'cdn = "[MQE_NO] = '" & Nz(CboRPO, "") & "'" 'cdn = cdn & " And ENO = " & ENO
'If DCount("*", "T_RPO_Footer", cdn) > 0 Then 'MsgBox "THIS RPO ALREADY ASSIGNED TO THIS FOREMAN", vbOKOnly, "WARNING!!!" 'Me.Undo 'Exit Sub 'Else ‘do something ‘End if
just wondering why randomly certain modules in the form code stop working..
when you accidentally press return.. or do something in the wrong order.. or apply one rule to one control.. and then another control rule stops working..
so you have to delete the code.. right click on the control's event.. and re-insert the code into the event section.. and then it works..
