Modules & VBA :: Move Parsed Fields Into A Table
Apr 23, 2014
I'm trying to build a command button to parse each line into a new record of my table [TBLRECORDS].I am able to parse a single line using this code but don't know how to insert the data into the table and loop through for all lines in my [memFile] control
On a form I have a text control [memFile].I populate it by pasting text from the body of an email into it and it is stored in a tables memo field [TBLFILES].[TEXTFILE].Each email can have countless rows of data in this format
H E74 Warranty end not prior to Serv start for XRC Status: M RecordId=897*7756*33*1D1PMB*20140418
H E74 Warranty end not prior to Serv start for XRC Status: M RecordId=897*7916*68*A1C558*20140418
Each line of data will always begin with "H " and each line of text will always have the same character count..For each line in me.memFile I want to create a new table record and populate this way
[TBLRECORDS][TXTTYPE] = strType
[TBLRECORDS][TXTCODE] = strCode
[code]....
View Replies
ADVERTISEMENT
Mar 30, 2015
My boss has assigned a task for me to move the details under a field name (eg:[Name]) in table 1 to table2 under another field name (eg [Insured Name]).
Previously, the software I planned to use is Excel, but my boss says Excel cannot handle large data as the computer will be hanging.So,he wants me to use Access to do the programming.I have some programming backgrounds (like C++) but it seems like I hardly understand how Access works! So can any Access expert here teach me how to do so that i won't get fired.
Firstly, We will have the data like this.
Table1
Code:
Name | PolicyNo | Age | Benefit
--------+----------------+----------+-----------
Chris | 123 | 21 | Death
Jane | 456 | 34 | TPD
Adam | 789 | 55 | DD
Secondly, we let the other staffs to key in which column to be mapped with the other. Manually Key in (both columns).Mapping Table:
Code:
Table1 | Table2
--------------+-------------------
Name | InsuredName
Policy No | Pol_No
Benefit | InsuredBenefit
Age | InsuredAge
Thirdly, the staff will run the VBA and result would be like this.
Table2
Code:
InsureName | Pol_No | InsuredBenefit | InsuredAge
--------------+--------------+----------------------+----------------
Chris | 123 | Death | 21
Jane | 456 | TPD | 34
Adam | 789 | DD | 55
As you can see the new table(table2) , field number 3 is the same with the field number 4 in table1. There is something like copy and paste job, but user can decide how the arrangement for the new table is.
View 14 Replies
View Related
Aug 14, 2014
I have the following code:
techid = Forms!Main!txtCurrentUserID
Task = 2
note = "Work Instructions"
strSQL = "SELECT *"
strSQL = strSQL & "FROM [tblTracking] "
strSQL = strSQL & "WHERE ([EmpID]=" & techid & ");"
Set rs = CurrentDb.OpenRecordset(strSQL)
[code]....
For whatever reason, this code will move to the last record that was entered in YESTERDAY, but I want it to move to the last record that was entered in TODAY. If I change the code to only records that are dated today, it claims there are no records (EOF), when there clearly are. why the code will not move to the last record of the table, it is only moving to the last record that was entered yesterday.
View 2 Replies
View Related
Aug 27, 2013
I have a database that will register the emails coming in and what time, also the time, date out and person.
I have a form with the fields to be filled in and a submit button. There are some fields that are automatically filled in and others need manually fill in.
below that part is a sheet (subform in the form of sheet) that should be filled in with the above data. Once it is there, it should empty the fields so they are ready to be filled in again. If I fill new data and press the submit button, it should go to the next available row.
View 2 Replies
View Related
Jun 6, 2007
Hello,
I have a big table like this:
ID Name Race1 Race2 Race3
-- ---- ----- ----- -----
1 a Asian russian
How do I create a query to be like this (either in query or report)?
I like to combine three fields (race1, race2, race3) in one field (Race), and break any value of races to the 2nd line, 3rd line, if there is a value...
ID Name Race
-- --- ----
1 a Asian
Russian
In Oracle and SQL Server, we can combine a few rows to be columns-like, but in Access, is there any way to do that? and how?
Thanks so much!
Jenny.
View 1 Replies
View Related
Oct 15, 2014
I've just created a form and I'm trying to move and size the fields but they seem to be grouped together and whatever I do to one happens to all of them. I select one field and just that field has the orange outline but when I move it they all move.
View 5 Replies
View Related
Aug 5, 2015
[URL] .....
In the Lesson tutorial shown above I am clicking on tools and adding existing fields. I get a lot of output label and text box pairs for each field that I add.
However, they are all on right below the other. That is no problem just move them. No way.
Whenever, I move it a label and test box for say Order Status, I get ... nothing. It stays where it is.
They (label and text box) do not move together. So how do I get them to move together.
View 4 Replies
View Related
Nov 24, 2006
Ok my problem is this......
I have a form with a field "Property Status" on it. It has 4 possible values -
"C - SHELTERED (with warden charge)";"H - SHELTERED (No warden charge)";"J - WHEELCHAIR SHELTERED (With warden charge)";"M - WHEELCHAIR SHELTERED (No warden charge)";"X - DISCONNECTED"
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
View 1 Replies
View Related
Oct 16, 2013
I have a database where on the bottom I have kept the record navigation buttons. When I move to the next record is fine, but when it reaches the end and I press once more, it will create a new one. How can I avoid this?
I made a button special for that purpose to creat new record). I guess the best option will be not to show the record navigation and create buttons to go back and forward and avoid that the next press of the button will create new record.
View 3 Replies
View Related
Dec 12, 2014
I have a form which has a field called quote directory, this contains the path of the quote documents and sub folders, I also have a field called project directory.
When a quote is confirmed I have it all working so a new folder is created with specfic details like project number etc.What I want to do is move the folder from the quote directory and put it in the newly created folder in the project directory.
View 2 Replies
View Related
Aug 11, 2014
i have a form, where a user inputs a account number inputting the account number, then populates all the relevant table fields, into the form for the user to edit.however if there is more than one account, with that account number, how do i get it so they can cycle through on the form?
View 2 Replies
View Related
Jun 27, 2014
Right I have the code in place but it will only work first time out look is opened, unless I have a msgbox on the first line and, I cant work out how to make it work with out it, obviously the msgbox isn't code doing any thing but it must be setting focus or some thing, here is the code
Dim strID As String
Dim olNS As Outlook.NameSpace
Dim olMail As Outlook.MailItem
Dim objAtt As Outlook.Attachment
Dim objDestFolder As Object
[code]...
View 3 Replies
View Related
Oct 10, 2014
When I build the statement I want to email and view it in a MsgBox the lines space correctly with the code vbCrLf.However when I send that text out as the body of the email, the spacing is no longer there and its just one big pile of nonsenses. In the MsgBox I see it correctly as:
Tool Box 5
Drawer 3 Tag Number 4
Drawer 2 Tag Number 6
Tool Box 7
Drawer 8 Tag Number 2
But in the Email it comes out as:
Tool Box 5 Drawer 3 Tag Number 4 Drawer 2 Tag Number 6 Tool Box 7 Drawer 8 Tag Number 2
How do I make Outlook correctly space?
View 3 Replies
View Related
Aug 27, 2014
So I have a table "zztblArticles." Some fields should go to "tblArticles" and values in Tag_ID should go to a lookup table "tblTag" and a junction table "tblArticles_Tags." I'll explain the code I've written below:
Code:
Private Sub cmdSubmit_Click()
Dim db As Database
Dim strINSERT As String
Dim strVALUES As String
Dim rszztblArticles As DAO.Recordset
[Code] ....
The first loop through works fine, I get the records uploaded to all tables. The second loop through fails at
Code:
intArticleID = DLookup("ID", "tblArticles", "Sourcing_Date = " & !Sourcing_Date)
Because it can't decide which ID value to use. This is because the value has been duplicated in tblArticles after the code acts on the same record again. It has completely failed to move to the next record in the recordset, despite the .MoveNext before the Loop!
View 12 Replies
View Related
Nov 29, 2013
I'm using a form with next record and previous record buttons that works fine but as the size of the database has grown it's clear I need a faster way to move through records.
My solution was to have an advance 10 records at a time button but I'm having problems when reaching the far sides of the records.
For example: If I'm on record 9 and I press go back 10 button then access tries to go to record 0 (which doesn't exist) and brings up and error. The same happens if I try to advance more than total number of records.
I found a solution to this which was to disable the button when the recordcount was less than 10. This works except now if I'm on say record 14 and want to go back to 4, i press the button, i goes back to record 10, the button is disabled so it can't go back further and i get an error.
View 3 Replies
View Related
Sep 11, 2014
If I have a field in a tabbed form (the form has pages), can this field be moved from a tab to another programatically?
The form is called "ProjectMain". The field is called "Lenght" and the page (form tab) it is on is "specs1". Sometimes I need to have this field in the page (form tab) "specs2"
View 3 Replies
View Related
Jul 29, 2013
I have a table with 3 fields. The fields are down1, down2 and down3. . I would like to use this table to create a new table (downtime). What I need too do is loop through each record in the table and place the three fields independently in my new table. For example, I would like to go to the first record in my original table, than place down1 as my first record in my new table, down2 as my second record and down3 as my third. Than I will go to the second record in my original table and place down1 as my fourth record, down2 as my fifth record, down3 and my sixth record and so on. I want to make a toggle button that will do this.
View 7 Replies
View Related
Sep 20, 2006
Well I have two tables, one is the workorder register and the other is the closed work orders. This is for maintenance. I have a form for creating and veiwing workorders. I need to link a command button to the form that will delete the record being viewed in the WO REG table and paste it in the Closed WO table. I think I need to use two append queries but have no idea what I am doing. Here is what I have so far.
INSERT INTO [CLOSED WO] ( WO, MMCN, TECH, NOMIN, FUALTS, TYPE, [SECTION], CLOSEDATE, OPENDATE )
SELECT [WO REG].[WO], [WO REG].[MMCN], [WO REG].[TECH], [WO REG].[NOMIN], [WO REG].[FUALTS], [WO REG].[TYPE], [WO REG].[SECTION], [WO REG].[CLOSEDATE], [WO REG].[OPENDATE]
FROM [CLOSED WO], [EQUIP REG] INNER JOIN [WO REG] ON [EQUIP REG].[MMCN]=[WO REG].[MMCN]
WHERE ((([WO REG].[WO])=[WHAT WO TO CLOSE?]));
Like I said I have no idea what I am doing, this is out of the SQL view for my "move to closed WO" query. the fields I need to move are WO, MMCN, TECH, NOMIN, FUALTS, TYPE, SECTION, CLOSEDATE, and OPENDATE.
Is this even remotly right? I know nothing of VBA an all these symbols mean nothing to me.
View 4 Replies
View Related
Sep 8, 2006
ok ive read some responses to people having trouble or need to know how to move a record from one table to another...
i have two tables. One with member and another with thier siblings.
i have two forms. one with the member details which contain a command box to open the other form for their siblings. the siblings form is a tabular form so when i double clikc the siblings form i can all the children but when i open it from a specific member only their children are shown.
each member has a memberID (primary key). but the siblings have the same ID as their father(member)
i have put a commmand box in the siblings form next to every sibling. what i want is that when i click this button the record moves to the members column.
i only know the very basics of VB...so a step by step explination would be helpful...
this is very specific and if more information is required please ask here or ask for my msn address.
Thank you in advance
View 4 Replies
View Related
Mar 26, 2008
Say i have two tables
1 = computers (it holds info about the computers)
2 = Repair (this table holds only the PCs that are under repair)
I want to be able to move one computer record to Repairs table as soon as i select that "this pc is under repair" on a form. So it does not show up on my report of computers in hand and shows up on computers in repair.
and when i get the pc back from repair, i want to be able to select "back in stock" and it'll move the record back to Computers tables & removes tht record from repairs table
is there a way i can do this automatically (without manually move a record)?
Any suggestions - web links or examples will be much appreciated.
Thank you
View 2 Replies
View Related
Apr 28, 2005
I've got a database that pulls from a table. One of the options on the table is pending or complete. Right now, there are over 1000 records because they are all stored in the same record. I'd like to move the completed items to a different table.
Can this be done? If so, what steps do I need to take to do this?
Thanks!
View 3 Replies
View Related
Aug 25, 2005
Hi all,
My database is a simple membership database that adds members and by clicking of a button it tells me the memebers birthdays for the month and if i chose to select the send card option then it remembers who has been send the card.
All i want now is a query that will take a member who's memebrship has been expired (since membership is year long only) and move them to another table by looking at the membership dates (like 2004 and 2003).
And may be later on if those members wants to join the membership i can then add them again from that table back to the main table.
I hope i made my self clear enough.
Thanks alot
View 4 Replies
View Related
Sep 26, 2006
Ok here is what I've got.
I have a table called (EQUIP REG)
It contains all the information to the equipment in the hospitol.
next i have a table called (WO REG)
It contains all the information for the workorders for the equipment.
In the EQUIP REG table I have field titled (PM) this is the date that the equipment is due for PM's. Now I could just run a query for all the equipment on a given date called "MONTHLYPM" and then "open" the workorders by hand....but would be an all day job becuase there are nearly a thousand PM's a month. What i need is a wuery to run off of the above mention query that will copy the some data from the EQUIP REG table to the WO REG table. I only need the fields called "MMCN" "SERIAL" "MODEL" and "LOCATION"
I know very little about access and even less about VBA, so who ever is brave enough to help me must be able to talk on a very low level.
thanks for any help in advance. :)
View 2 Replies
View Related
Jun 27, 2005
Hello. I have a question that seems simple but I can't get it to work. Any help would be appresiated.
I need a form that will pull the oldest record from one table, allow the user to add a few fields to the record, then paste the record to a different table. when it is done the original record has to be removed from the original table.
I have tried using a append Query, but it moves all of the records at once and doesn't allow data entry on each record. I'm not sure if this would be done by a Query, Form or Macro. I have laid out the nessacary data flow below incase there is an easier way to do this.
Persons A,B, and C enters data into table 1.
later person D pulls the oldest record from table 1, reads the data then adds fields to the record and records it to table 2. The information is then removed from table 1. Person D then goes onto the next record from table 1. When person D is done, there will be no data left in table 1.
Any Ideas? or can someone point me in the right direction for help?
View 3 Replies
View Related
Aug 26, 2004
I am working on a calibration database which keeps track of measurement equipment and their scheduled calibration date. There are several gages that become "In-Active" because they are old, lost, or whatever. I have two tables that have Active and In-Active gage listings containing all of the pertinent information regarding that gage. I would like to put a button in the Active gage form which in the event the gage becomes "In-Active", pressing the button moves all of the information in the "Active" page to a new page in the "In-Active" table. So essentially one can look at Active gages and In-Active gages depending on the switchboard button that is clicked.
I know that is probably a crazy question but any thoughts or insight would be greatly appreciated.
View 1 Replies
View Related
Nov 21, 2006
Hello. My question is how do I go about moving data from one field to another within a table? I want to move the data in ReportDateClo to ReportDatCom when both ReportDateClo and SupAct are true by record which are identified by the field LogNum. It is probably very simple but I am having a brain cramp.
ReportDateClo and ReportDatCom are "short date" date fields. SupAct is a Yes/No field. LogNum is an autonumber.
View 2 Replies
View Related
