I have a select statement (AlphaName and StaffName are variables) in a module that woks fine, its been tested with a basic insert. what i want to do is get this result into a combo box without creating another table?
I have been spending all my today to fill a combobox dynamically, but have not been able yet.
I have a combobox and a pass-through query in access, which is working fine and fill the details into the combobox via data source. Now what I am planning to do is to update the combobox source as soon as value in a text box changes.
here is the code I am using, but it is not working:
Dim rs As Recordset Dim qDef As QueryDef Set qDef = CurrentDb.QueryDefs("get_data") qDef.SQL = "SELECT Initial + ' (' + Name + ')' uws FROM EM.dbo.UW" _ & " WHERE lob = '" & addSingleQuotation(Me.CMB_LOB.Value) & "'"
Me.cmbUM.RowSource = qDef.SQL Me.cmbUM.Requery
I also used Recordset, but did not work:
Set rs = CurrentDb.OpenRecordset("get_data") Me.cmbUM.RowSource = rs!uws
I have a basic design question that I am not sure how to address.
I am trying to build a simple data entry database with a form to take input from the user, store the values in a table. Once the data is gathered into the table I want to use this table to print a report of each record (entered using the form before).
To achieve this objective, I made a form (frminput) with some text fields. Most of the fields on this form are Bound fields to a query (qrymaster). One of the field is a Combo box (whose value is shown from another table). I have designed the RecordSource of this Form to be a Query (qrymaster).
This is a basic Data Entry form where the user selects the Combo box item and based on what he selects, some of the fields in this Form gets pre-filled. The rest of the fields on this form are bound to the query "qrymaster" and the user has to type these fields manually.
Now, I want a Save button here that would save all the values on this form to the table "tblmaster". How do I do this efficiently keeping in mind all the normalization laws on the database?
At this time, behind the Save Button, I have included a SQL statement to insert all the field values into the table "tblmaster".
I am sure there is a better way to do this. Can someone point me in the right direction please?
I know how to have multiple columns fill a lookup in a combobox both from a table and a query. But I need to have the other fields that aren't saved by the combobox saved in the neighboring columns. So, my primary table is a master list of chemicals to be analyzed along with their respective registry numbers. I know what most of the programmers say about repetitive data being bad form etc. These names and registry numbers will NEVER change, so I'm not worried about a change causing problems later. I'm trying to build a separate tables that will have specific chemicals and the methods that they are analyzed under that effectively copy from the master list, but add their own quality control criteria. Further more, not all of my clients need all of the chemicals that are available for every method. It should be noted that not all chemicals are analyzed by the same methods and that some methods will have some of the same chemicals as others. I need the registry numbers because this is what the analysis software uses to uniquely identify each chemical and I need the name because names are easier for me. Long story short, I need both of these pieces together. It was suggested to have a macro copy the remaining columns from the dropbox in the table to the other columns in my table, but I'm not sure how to do this (I'm still very new to Access and my VB is very rusty). I understand how to do this for a form, but when client reporting lists become involved later on, this will make my database very bloated to have a form to populate each respective table.
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 Table with 57 fields. I would like to display this table in a form as a subform, but only certain fields depending on what selection is made in a combo box.
For instance, if the user selects "Missing Information" in the Combo Box, then the form will show a few standard fields such as ID, Market, Sales Manager, and then some specific ones such as date missing information requested and date missing information received.
If the user selects another option, again the standard fields will remain plus a few different ones.
I have done much searching on this and feel like I am so close but so far. I have looked into controlling the record source of the subform, columhidden =false and a multitude of others. All of which may or in fact probably do work in this situation but I can't seem to put it all together.
I'd like to ask you to help me in the following. The issue is basic I think, but not for me...
I'd like to transfer the data entered in the fields of a form into the fields of a table (that uses the same values). This table is connected with one to one relation to the table which the form bases on.
(An automatic data-transfer would be desirable. If it is difficult a button will do as well.)
Please write if you have got any good idea or solution.
This is simple Access but I am quite simple so help would be cool all I want to know is how to put text from one field into another, but not every time. lets explain I have an order form and two fields , one date booked , and one date requested. if date booked is empty I want to copy the info from date requested to it, if its full I want Access to say its already booked.
its porbably something along the lines of where datebooked.txt = "nothing" then datebooked.txt = date requested.txt or something like that anyway
I have a list of 4300 clients. The clients' personal information and ID numbers are listed in the table BasicInformation. Another table, DiseasesAndValues lists 17 diseases and a "scheduled value" (long story short - an amount of money) for each disease. Each client has one of these 17 diseases. I would like to have a column in the BasicInformation table which shows the scheduled value for the disease that a particular client has by going back to the DiseaseAndValues table and checking to see what the scheduled value of their disease is. Alternatively, I could use a query that could enter in these values with a click of a button.
I have the following code (which everyone uses) but I have a challenge.
I ONLY want to use two (maybe three) fields from the database! (Subject & Body... with the POTENTIAL of an attached document.
I want to fill in the "to" section AFTER the email is open. This will allow me to make changes if I need to before it is sent.
Here is the code I am trying to work with. (It should look familiar) but I can't get it to keep outlook open!
Code: Private Sub SecondEmail_Click() 'open Outlook, attach zip folder or file, send e-mail Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem
I have a query that selects records in a certain date range. Then I have a textbox that gets an input of the earliest date of that range... I used the code...
=DMin("<field name>","<query name>")
What I want to do is fill in other textboxes next to that one with the other fields' data for that corresponding record. So for example, the query runs and outputs the data and part number 2123 was ordered 10/2/2012, which happens to be the earliest in that particular date range. So the one textbook does work and outputs "10/2/2012"...Now I want another textbox right beside it to output "2123".
I have the code below which takes information from a form on access and sends it over to the correct place on an excel spreadsheet template. This works fine but I then need it to save and send on outlook.
The issue I am having is that the saved document is not attaching to the e-mail. The subject etc all work fine but the excel spreadsheet just doesn't attach. When I go into the folder I have specified for the document to be saved in it isn't there either. :0(
The code for the e-mail "callmail" function works perfectly for word documents but I don't know if it is different for an excel file.
Code:
Private Sub Command154_Click() On Error Resume Next Dim appExcel As Excel.Application Dim wbook As Excel.Workbook Dim wsheet As Excel.Worksheet Set appExcel = New Excel.Application
I have staff table which is a lookup field on the phone call table.when the phonecall form opens I want the employee field to auto fill in the employees name from the lookup field according to there security level.so something like this
On Open Select Case Forms!frmLogin!cboUser.Column(4) Case X = 4'the employees security level' Case X = Insert Table_tblPhoneCalls!EmployeeID"4" into Form_frmPhoneCalls!Employee. End Select
of course this is a syntax error as I do not know the correct code words.
I currently am working on a small inventory project. I have a table with the fields "Part Number" which is my primary key, "Description", "Cost", and "Sale Cost". I have a second table that I would like to use to keep track of purchase orders. It has the fields "Part Number", "Description", "QTY", and "Cost". I would like to be able to open purchase orders and be able to select a part number from a combo box that pulls "Part Number" from my item list.
So I can enter items in to my item list and later when I do purchase orders I can go to purchase orders select my item and have it automatically fill in the description and cost in my purchase order. If the item does not exist I can enter in the item in to the item list table. At this point i'm not worried about a prompt to enter in new items if they don't exist in the item list table. I just want to my Purchase Orders table to be able to autofill description and cost by selecting a "Part Number".
umm.. i have a form with a combo box which is linked to a table which gives me customer id numbers. and i have a listbox which i want the names and addresses to appear when i select a cusotmer id number from the combo box
so on the list box i put " SELECT CustName FROM Customers WHERE CustID=$combo43; " in row source; if i change $combo43 to 0 the name appear but that is fixed and i want it 2 change when i select a cusomer id number from the combo box
I'll start with explaining what my goal is.. I have a table with workorders, it has a column "date planned" so I can give all the work orders a date when to be executed.
On the other hand I also have a table with the ID of every technician and the dates when their vacation starts and ends, so 3 columns, 1 text, 2 dates.
To link the 2 I use a table "schedule" where I have 4 columns, "ID", "WO", "TechID". WO refers to the workorder nummer that can be found in the schedule table.
My end goal is to have a form with a subform "schedule" where I can see all the workorders in dataview, when I select a workorder in the table I want to fill up a combobox with all the technicians available, so that means that all the ones on vacation on the planned date of that WO are not included in the combobox..
I tried making a select query, but I have no clue how to make a "select ... where (date) is not between ... and ..."
I found a snippet of code online that I'm trying to use in an asset tagging database I'm developing, but I'm struggling to get it working. To start, I have a multi-column ComboBox that displays information in this format:
When you select an asset to be assigned to an employee, the ComboBox displays only the asset number (e.g., 1001). While that information is pertinent to our I.T. group, when Human Resources goes to collect an asset from an employee, they don't want to be taking cases off of phones or tablets to verify they have the correct asset number. They want to see the person has an iPhone5s and an iPad2 that they have to collect. So, what I'm trying to accomplish in my VBA is to have access read all the asset numbers and provide the descriptions of those items in another field.
The code I have so far is:
Dim ctl As ComboBox Dim varItm As Variant, str As String str = "" Set ctl = Me.Combo217 For Each varItm In ctl.ItemsSelected str = ctl.Column(2, varItm) & "," Next varItm Me.Text207.Value = str
Since this is code is something I found online, I'm not sure why I can't get it to work. I've never worked with the Variant declaration, but I think this may be where the code is breaking because whenever I remove the "For Each...Next", the code correctly assigns the value of column 2 of my very first row to my text box (Text207). Everything I've seen looks as though I don't have to declare varItm because it's function is to represent the rows that are checkmarked for ctl.ItemsSelected.
I want a command button to "Requery" a combobox. the combobox uses a query to determine the records listed (it lists incomplete records). after completing the record, i'd like to hit a command button that will "requery" the combobox so that the recently completed record is no longer listed.
I have a subform for a hotel list, I have a combobox on the subform that show the list of the hotels, what I need is to create some fields on the subform wich shows me all the details of the hotel as address, email, facilities once I have selected a hotel from the combo list. How can I achieve this! Thanks Marco
Below is the main data entry form of my application. We disrtibute a product called MC Cloth to Shops to display. After a month we visit again and take stock of products sold, replenish and the shop pays for the products sold. the Database keeps a record of the shop, products displayed, refilled, sold and respective payments.
The dtabase and the form is loaded in Handheld (PDAs) by the sales people who enter data during the visit and then synchronise with a master on return
http://affiliatesexcel.com/MC_main_form.jpg
I need to sum up values in one field for example MC Refill from the first record till the new record and show it in another field, for example MC Refill Total
Another Forum answer to my question about summing up values in one field to be used as default in a second field showed that a search needs to be done based on base field (in this case NAME of customer).
However since I use a COMBO BOX to enter this NAME field values and then select it from a pull down list to create new records, I have this value ONLY in the very first Record. All subsequent records have all other values where as the NAME value remain empty.
This makes the search function impossible based on the NAME value. As a solution I would like to AUTOMATICALLY copy the NAME value to a second field (for example CUST_ID) during creation of each NEW RECORD so that I can then base my search on this field instead of the Name field (with empty values)
Currently I have the code below which works correctly for entering NEW Data and for recalling by Pull Down . (I have tried a mehod which entered the values for all records but this clutter up the Pull down with SAME Name for repeated records making the PULL DOWN unusable.)
I have tried to copy the Name value to Cust_ID value for each new record but the code gives an error.
+++++++++++ Code:
Private Sub Name_Combo_AfterUpdate() ' This procedure tries to find the matching product's record. ' If the matching record is found, the procedure goes to it. ' If the record isn't found, the focus stays on the current record.
Dim Criteria As String ' This is the argument to the FindFirst method. Dim MyRS As Recordset ' Recordset used to search. Dim ComboName As String ' The name of the company to search for. Const IDYES = 6
Set MyRS = Me.RecordsetClone ' Build the criteria. ComboName = Chr$(34) & Screen.ActiveControl & Chr$(34) Criteria = "[Name]=" & ComboName ' Perform the search.
MyRS.FindLast Criteria If MyRS.NoMatch Then
Response = MsgBox("Could not find the Supplier Name: " & ComboName & " Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48) If Response = IDYES Then MyRS.AddNew ' Create new record. MyRS("Name") = Screen.ActiveControl MyRS.Update ' Save changes. MyRS.Move 0, MyRS.LastModified ' Go to new record Me.Bookmark = MyRS.Bookmark ' Go to new record Else GoTo Endsub End If Else MyRS.AddNew ' Create new record. MyRS("Name") = Screen.ActiveControl MyRS("Cust_ID") = MyRS("Name") MyRS.Update ' Save changes. MyRS.Move 0, MyRS.LastModified ' Go to new record Me.Bookmark = MyRS.Bookmark ' Go to new record
'Me.Bookmark = MyRS.Bookmark
Dim recNo As Long
' for this to work there cannot be any RecordNumber with a value of 0 ' it finds the highest record number for the name in the combo box ' and returns 0 and exits if no record found. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0) Debug.Print "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'" If recNo = 0 Then Exit Sub End If Me.Text90.SetFocus DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
End If Endsub: MyRS.Close
End Sub
+++++++++++++++++++++++ The question is is there an easier way to achieve the summing function ?
I'm working on a db that will track students for a summer camp. One of my fields in my student records (CamperInfo) is "School". Currently, the field is set up as a combobox in the CamperInfo Subform in the FamilyInfo form. I would like the combobox to be able to add schools if the school is not listed by a pop-up when the "add school" (or whatever) is selected.
Code:
Private Sub School_AfterUpdate() On Error GoTo Err_School_AfterUpdate Dim strForm As String If Me.School = "Schools" = True Then strForm = "Schools" DoCmd.OpenForm Me.School Err_School_AfterUpdate: MsgBox Err.Description End If End Sub
As you might have guessed, it doesn't work. no errors, it just doesn't do anything. I added the "Schools" data.
I setup a combo box where I specify the data to be pulled from a table. The table just contains the primary key (id) and a name, which is what I want loaded in the combobox pulldown.
When I run the form, the names appear but when I select, the id is written to the table instead of the name.
What settings am I missing and why is it taking the id instead of the name ?