Forms :: VBA Code To Pull Data From Different Table?
Mar 26, 2015
I have a form I use for data entry, it needs to generate an id called RO Number and i need it be generated by access starting at number RO129036 and then keep adding sequentially, so RO129037 etc etc.
as i already have data in my DB that i need i cannot just reset any fields
having a table with just one field - the numeric part of the RO number so first one would be 129036 - so i need the form to pull this field, add 1, and then add "RO" at the beginning of it? i have really been struggling with this database
I am trying to run some diagnostics on my database. I thought a good place to start would be to look at all of the tables, the number of records each table holds, and each table's size. I have been able to find plenty of code on the web that loops through each table and provides the aforementioned data points, but the code only looks at local tables. SInce the database is split, I have a ton of tables linked to the back end that I am unable to get stats on. Any query or any code that can pull back end table stats?
Code BrandName Lead Free Nickel Free 001 AAAA Yes Yes 002 AAAA Yes No
On a form the user selects the code field and in this table the field is called BRAND. On the same form, I need to display the value in the Nickel Free field so if selects 001, the field on the form needs to be "Yes", if the user selects "002", the value needs to be "No"
I am having trouble with our receiving database. This database consists of two tables. One for vendors, which basically contains their vendor ID as well as vendor name and phone # etc. The other table is our receiving data table. When our receiving person receives product in, they log this in the receiving table including info such as date, autonumber for record, vendor etc. The problem is, when the person selects the vendor id, which is set up as a lookup field, we would like to have the vendor name pop up atuomatically within that record. I cannot for the life of me figure out why this isn't happening. The person is using a form to enter all of this data and runs a summary report at the end of the day. Our accounting dept. is requesting this info be added, but I cannot seem to figure it out. Thanks so much for any assistance! Amy (monet1369);)
v sorry for the basic question, but ive been banging away at access and i cant my head around this..
i need to run a simple query. the query/ search will ask users to enter in the number of a document. i want, when this code is entered, for 2 controls on the form be updated with codes that are stored in a table based on the code they searched for originally.
heres my table structure... Table A Doc ID (PK) Doc No (manually input and is the search item that users enter)
Table B Unique ID (PK) Doc No (FK) Info (to populate field 1) Info (to populate field 2)
how do i perform this in the query section? do i need to manually code the SQL required, or is this query basic enough that i can just select the fields required in the design view of the query?
I am trying to add an attendance records to my database but cannot figure out how best to do it..I already have a 'children' table were all the kids info is stored and have created a 'roll' table.
i want to be able to open a form and search first and/or last name from the 'Children' table, then be able to save both first and last names and the date into the 'Roll' table. (then open reports etc later based on dates)how to pull data from one table and store it in another.
I have a table that has four columns. Column 1 had people's names, column 2 has their email, 3 has a category, and 4 has their office.
I have a form with radio buttons, When you check a radio button and press OK it will display the e-mails for the people selected. Problem comes when it's by category, since categories are not unique to one person. I want to click the category radio button and have it display every person under that category, concatenated with a comma.
Problem is I can't make a listbox because it will show the same categories several times instead of just once, so I want the radio button with the category name on its label, and then in the code I need to tell Access to go to the table and search for the e-mails from a specified criteria, the category.
Right now I have a subform with a combobox that pulls it's data from a table. I want the user to either select an existing item or type in a new item and have a macro create the new table row. What I have right now works in the sense that it prompts the user if they want to creat a new item and the new item is created (and I can see it in the combobox list), but I'm still getting an error saying that the item does not exist in the table forcing the user to manually select the newly created list item they just typed in.
Code: Private Sub MaterialCostCode_NotInList(NewData As String, Response As Integer) Dim rst As DAO.Recordset 'Update value list with user input. On Error GoTo ErrHandler Dim bytUpdate As Byte
[Code] ....
It appears that the new item doesn't always show up automatically and requires the form to be refreshed, so now I need to figure out how to get it to consistently appear right away without a refresh.
I have a module that is in Excel that is connecting to a back end database. I am trying to pull data from a table that meets a number of conditions. With the following statement, it is not returning any records,even though I know there are records that meet all of the conditions. I suspect the problem is with the last condition. In that condition I am trying to say that pull in records where it has been at least 14 days since the last review.
Code: strSQL = "SELECT tblsojrol_oc.* FROM tblsojrol_oc WHERE tblsojrol_oc.[Status] = 'Pending' AND tblsojrol_oc.[1st Review Date] IS NOT NULL AND " _ & "tblsojrol_oc.[3rd Review Date] IS NULL AND (DateADD(Day,14,tblsojrol_oc.[2nd Review Date])) >= #" & dt & "#;" objRs.Open strSQL, objConn, adLockReadOnly
I have built a nice database that has a form to enter data which logs in product received, there is a combo box on the same form that is linked through the query builder to auto populate the names from the contacts info table (the receivers of the product received) the contacts info table also contains information that is specific to each name such as locations.
As of now I have created a command button that brings me to the form that shows the information fields I need specific to a name, once I get that I have another command button to bring me back to the main form. How to create an additional list box on the main data entry form so as when the name is entered the new list box or text box (which is best?) will auto populate the information I need on one form instead of going back and forth.
Example:
Requester Name [ auto populate name ] currently linked to contacts info table (working)
(New field) Preferred Location [ need to auto populate location ] from the contacts info table (how do you pull locations specific to a contact name from the same table?
I need to pull data from a master project list to auto update other forms. When someone enters a project number I need it to pull the data for that project into another form so they dont need to keep typing details in. The other forms are trackers for our processes to complete the projects.
Let me know what you think...would a subform pull the info automatically somehow?
I have a form, with a tab control on it. Each tab has a subform to display data. One tab is meant to be a summary tab of the rest, so I want to pull data from certain controls on each of the other tabs to display on the Summary tab.
If I set the ControlSource to Forms!subfrmLABOUR!txtTotalHours.Value the control just displays #Name?
I assume that means it can't reference the ControlSource. Is it just a syntax thing? I've tried various methods, but no luck.
I have a Mainform [FrmReconcileMain] and it contains a Subform [FrmReconcilesub]
What I'm trying to is, on the Main form type in a statement date in textbox [TxtStatementDate].
I have a checkbox on my subform [ReconciledYN], along with a textbox [TxtReconcileDateSub]. when I click the checkbox, it simply pulls the date from the mainform and populates the date in the subform.
I've even tried experimenting on a simple form (with no subform) to see whats going on, but still I can't get it to work, even on a simple event such as this...
If [yourcheckboxname] = -1 Then [controlnametoupdate] = date() Else [controlnametoupdate] = ""
Ive made a booking system in Access and need to convert it so it can handle multiple users on a network. The problem i have is that when someone selects a block of time on one computer i need the other systems to lock out those blocks on their screens. I have a table which records each user's activities so i could always use the On Counter event on the form to repeatedly check if there is any blocks to be locked out according to data in that table. The obvious problem with this is the huge overhead.
Is there any other way round this? or is there any way of detecting changes to data in a table so that i can run code to lock out the blocks only when theres a change?
I’m working on an Access 2002 project where I need some help with coding. I’m a neebie to Access and Visual basic so a few pointers to get me on my way would be much appreciated.
The project is dealing with logging vehicles on and off site. I have one database that contains a list of all regular traffic in three fields – Registration Number, Company Name and Type. After the last update this contained about 300 records. I called this MAINDATABASE.mdb.
I have another database that runs as a user input screen and front end called WORK.mdb. This contains the above three fields and three others, Time In, Number of Occupants and Time Out.
I have already done the coding for Time In and Time Out where the user just double clicks the field and the system time is entered. The important field is Registration Number. What I need is the following:
When a vehicle comes on site the user double-clicks Time In on the form which inserts the present time (done). The user then tabs to or clicks on Registration Number. An input box comes up where the user types the reg number. I need the code to take this input, search the MAINDATABASE database and if the reg number is found return the values for Registration Number,Company Name and Type in that row of the database and then insert those values into the corresponding rows in the WORK database. If the reg number isn’t found then a message box comes up telling the user to enter details manually and input the string entered by the user into Registration Number field on the WORK database and leave the user to enter the values for Company Name and Type. I have the code for the Input Box and the Message Box at the end!:
Private Sub REGISTRATION_GotFocus()
InputBox("Enter Reg number...") ……..
MsgBox "Registration number not found. Please enter details manually"
End Sub
I just need the stuff to do the work in between!! Any pointers are very welcome……
I have prepared a table in a database which includes a yes no box to one of the fields.
The idea of the yes/no field is that this is recording if the record is awarded. What i would like to achieve is that if the yes no box is checked "True" i would like to be able to copy some of the data from this table into a new table and that the form associated with this table opens up to enable the user to populate the remaining fields.
I believe i need to run this as an on click event procedure but cannot identify the code to enable the data to be copied accross to the other table.
I have a table called "EquipmentRequired" which is populated by 4 append queries,
5000BaseReq, 6000BaseReq, 6000IFBBReq, EquipmentReq which get some of their information by counting fields in another table but all have the same field names.
The queries contain all data that is initially used to append new records to the table and this works fine.Unless some information changes or a record is added then I would like to add a button to a form and call it "update equipment" behind which would run a vba code firstly to delete all the records in "EquipmentRequired" table then run the 4 queries without the warnings and re-populate the table.
I'm totally newbie so please go easy on me, I finally learnt how to make database and tables by reading online tutorials. I'm now able to create a simple database in access, using tables and stuff. I want to ask, say i put 500 entries in my database and then i want to search for some specific data, how do i do that?
Say I'm making a database of model's agencies, and i want to pull all the models working in same agency and stuff like that, how do i do that. Do i have to create queries or forms? i'm confused, please help.
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
I'm working in Access 97 (though I can use Access2000) and Outlook2000 and am trying to write a process that will go into a specified folder in a user's outlook and pull in the data from the emails there. The data will be in a standard csv format - but my biggest problem is trying to figure out how to systematically go into the folder, import the data from the email, and then move to the next email in the folder...
I have a form with a subform. I have the subform running a update query on a command button in the subform. The subform has the textboxes and I cant seem to get the query to pull the data from those textboxes. I have used the normal [forms]![form_name]![textbox_name] in the past but everything I try to pull the subform textbox data is not working.
I have tried [forms]![form_name]![subform_name]![textbox_name] [forms]![form_name]![subform_name].[form]![textbox_name] [forms]![form_name]![subform_name].[form].[textbox_name]
Nothing works. What is the correct syntax for this? Any help would be great !!! Thank you in advance.
I posted the following code on a button (report to a first sergeant). I can't pull e-mail addresses from the report (rptLateByUnit).
=========
DoCmd.SendObject acSendReport, "rptLateByUnit", acFormatPDF, Me.CCF_EMail, Me.CCS_Email, , "FOUO: Open Personnel Data Discrepancies", "This document contains information which must be protected in accordance with AFI 33-332, Air Force Privacy Act Program, and DoD Regulation 5400.7R, DoD Freedom of Information Act Program; and Privacy Act of 1974 as amended applies. This document is For Official Use Only." & vbCr & vbCr & _
