I have a database that is split - one central PC holds the back end, and then there are multiple end users with a copy each of the front end.We have updated a drop down menu on a field in the back end to add some new entries, but this is not showing up on the front end copies.Is there an easy way of updating the front end to show the changes?
View RepliesI have a drop down list where you pick from 6 recruiters. i put it on my candidates from and when yiou change a recruiter on one candidate then all the candidates get changed to that recruiter. Any ideas what i did wrong?
View 1 Replies View RelatedPlease help me fix my database
If you take a look at the tab called insurance i have a list box. How do i filter the list box so that it will only display the patients insurance based on the demo tab.
Also i have other records in the table called people. they are not showing in the people form. when i use the mouse wheel automatically creates new record please help
i have developed an application in access 2010 . and split into front and back end . now i want to add more tables in back end and i need to define lookup list in table definition from the query presently in front end . when i get into lookup list and query builder doesnt show front end content ... how to solve this problem ?
View 2 Replies View RelatedI have done this in Excel before, but not Access. I do not know VBA. I figure this will have to be done in a query or a macro. I don't even know if all of this is possible in Access. I need to be able to split an Address field into:
Street Number
Street Name
Street Type
Street Direction
And the purpose of this is so that I can pull out the Street Type (Drive, Road, Lane, etc) and update the abbreviations (DR, RD, LN) to the Street Type spelled completely out.
I did this in Excel by creating a Named Range "Types" on a sheet that has the abbreviations in Column A and the spelled out versions in Column B so that I could convert the abbreviations to complete street types. I broke down the entire address into each part on another sheet. Then I did a VLOOKUP to look up the Street Type in Column B in the Types range. Works great! And the only way I knew at the time to do that.
But, by doing this, I have to get the data I need from a download into a spreadsheet, break the address fields down on a second sheet, do all kinds of field update conversions (to get the field names from the download to match the field names in my Access table). This takes extensive Excel programming. I just thought there might be a way to do it by simply importing the data from the download straight into Access. That is easily done, but the Street Types aren't consistent.
There may even be a better way to do this than splitting....something like if a field CONTAINS DR update it to Drive. This would be a long process to set up because there are so many different street types to consider.
This is basically for the purpose of finding duplicates. If there is one entry called 123 Main St and another called 123 Main Street, they are not going to show up as duplicates, rather as two separate records.
This one is driving me NUTS! I have a form with a combobox, a few textboxes, and a sub-form.
On Load the form is populated with a sql command/rcSet.Fields() results.
The user then chooses a status from the combobox to narrow down the results. The combobox OnChange event looks like this...
sDate = Forms("frm-MENU").txtS_Date.Value
eDate = Forms("frm-MENU").txtE_Date.Value
xJob = Forms("frm-HD/DVR_CC").lstJob.Value
On Error GoTo hd_dvrErr
'this gets the top of the HD/DVR form
sSQL = "SELECT Count(*) AS Total, Sum(IIf([pmt_meth] In ('C','E'),0,1)) AS Error, Sum(IIf([pmt_meth]='C',1,0)) AS [Credit Card], Sum(IIf([pmt_meth]='E',1,0)) AS EFT " & _
"FROM [tbl_HD/DVR_CreditCard(*)] " & _
"WHERE ((([tbl_HD/DVR_CreditCard(*)].CDATE) Between #" & sDate & "# And #" & eDate & "#) AND (([tbl_HD/DVR_CreditCard(*)].JOB_TYPE) Like '" & xJob & "*'));"
Set db = CurrentDb()
Set rcSet = db.OpenRecordset(sSQL)
With Forms("frm-HD/DVR_CC")
.txtTotal.Value = rcSet.Fields(0)
.txtError.Value = rcSet.Fields(1)
.txtCC.Value = rcSet.Fields(2)
.txtEFT.Value = rcSet.Fields(3)
End With
'this gets the bottom or subform of the HD/DVR form
sSQL = "SELECT IIf([Agent]='UNKNOWN','xxxxx',[REP]) AS [Agent ID], [tbl_HD/DVR_CreditCard(*)].Agent, [tbl_HD/DVR_CreditCard(*)].JOB_TYPE, Count(*) AS Total, Sum(IIf([pmt_meth] In ('C','E'),0,1)) AS Error, Sum(IIf([pmt_meth]='C',1,0)) AS [Credit Card], Sum(IIf([pmt_meth]='E',1,0)) AS EFT, Sum(IIf([pmt_meth] In ('C','E'),0,1))/Count(*) AS [Error Rate] " & _
"FROM [tbl_HD/DVR_CreditCard(*)] " & _
"WHERE ((([tbl_HD/DVR_CreditCard(*)].CDATE) Between #" & sDate & "# And #" & eDate & "#)) " & _
"GROUP BY IIf([Agent]='UNKNOWN','xxxxx',[REP]), [tbl_HD/DVR_CreditCard(*)].Agent, [tbl_HD/DVR_CreditCard(*)].JOB_TYPE " & _
"HAVING ((([tbl_HD/DVR_CreditCard(*)].JOB_TYPE) Like '" & xJob & "*')); "
Set qdTemp = db.QueryDefs("qry_HD/DVR")
qdTemp.SQL = sSQL
qdTemp.Close
If Not Forms("frm-HD/DVR_CC").FormFooter.Visible Then
Forms("frm-HD/DVR_CC").FormFooter.Visible = True
Else
'DoCmd.Close acForm, "frm-HD/DVR_CC(Footer)", acSaveNo
End If
DoCmd.OpenForm ("frm-HD/DVR_CC(Footer)"), acNormal, , , , acHidden
Forms("frm-HD/DVR_CC(Footer)").Recalc
Forms("frm-HD/DVR_CC").Refresh
Forms("frm-HD/DVR_CC(Footer)").Refresh
' DoCmd.MoveSize Height:=Forms("frm-HD/DVR_CC(Footer)").WindowHeight + Forms("frm-HD/DVR_CC(Footer)").FormFooter.Height
exit_hd_dvrErr:
Exit Function
hd_dvrErr:
MsgBox Err.Number & "-" & Err.Description
Forms("frm-HD/DVR_CC").FormFooter.Visible = False
Resume exit_hd_dvrErr
End Function
I have used similar code on another form and everytime the search criteria changes the sub form updates to reflect such. I know I am missing something small; can somebody please point it out?:D
I need to have the subform show the updated (choice from combo) criteria.
If this seems to cloudy, please let me know and I will try and revise
When I add new records to the form and close the form, the tables are updated with the new records, but the new records don't show in the form (navigation).
View 14 Replies View RelatedHi All,
I am wanting to build a table which consists of all the queries and tables in the database.
I am expecting column 1 to have all the queries and column 2 to contain all the tables that make up the query. So the table will look like this:
Queries - Linked Tables
Query1 - Table1
Query2 - Table1
Query2 - Table2
Query3 - Table1
Query3 - Table4
As you can see a table can be linked to any number of tables.
Now for my actual question. I don't want to have to type in all the queries and tables. I would like to choose any query and table from a drop down of all possible queries and tables that are currently in the database - like a list, where the list contains all the possible values.
I would then select the relevant entry.
Any pointers would be great.
Thanks,
Evan
I've created a drop-down box on a form, that allows a user to choose from three names.
However, when I choose say name A, it then populates all the other entries with name A.
For example:
record 1 has name A
record 2 has name b
record 3 has name a
if i then enter a new record, and enter name C in it, it changes records 1, 2 and 3 to show name C too.
is there a setting i need to change or something?
I have a field in which I wish to post several contacts. The contacts are listed in a table and populate the specific field via a drop down. In this specific field I want to select "mary" "bob" and "fred" (all three to display) however only one is displayed.....
View 3 Replies View RelatedI have a drop down list to select a committee, but my list is only showing half of the list. There's no scroll bar, and when I try typing in one of the others, it tells me it's not on the list and asks if I would like to edit the list. When I select edit the list, it shows all of them.
View 2 Replies View RelatedI'm a new to Access, SQL. I need the following output:
Input:
PHP Code:
PO Vendor State
a, b 1,2 IL
c, d, e 3 TX
f 5 CA
Output:
PHP Code:
PO Vendor State
a 1,2 IL
b 1,2 IL
c 3 TX
d 3 TX
e 3 TX
f 5 CA
I found the following SQL code from another forum, which splits entries in a cell separated by commas into 2 rows, output being:
PHP Code:
PO Vendor State
a 1,2 IL
b 1,2 IL
c 3 TX
d,e 3 TX
f 5 CA
SELECT * INTO ImportedData
FROM (
SELECT PO, Vendor, State
FROM SourceData
[Code] ....
How to run a loop so that if a cell has n entries separated by commas, I want them to be split into 'n' number of rows.
How do I requery the list box in the form after the combobox is updated? the name of the combo box is txtFilterClassName.
View 3 Replies View RelatedI am working on a database. I changed some of the data that was already in the database and it saved without any problems - when I reopened the database, the data was still there. But when I try to input completely new data, none of it will save (it is a database of employees and their information). So, just to make it more understandable, when I change one piece of information, it saves without any problems, but when I want to add a new employee together with their information, save it and reopen the database, it is not there.
View 4 Replies View RelatedI am creating a table in Access 2003, and I need to add a drop down list to one of my columns. See the attached picture - I need to add a drop down list to the "Division" column that will allow the user to select from one of 5 choices. How do I do this?
I'm an Access beginner, BTW. :)
Hello all!
I have a drop down menu that pulls values from a table. When clicked that value pops up in a total field. Currently I only allow 1 value to be selected. When the user (me at this point) chooses another value, the value is added to the total instead of updating with the new value. So the every time it is clicked it just goes up and up. So I decided to add a loop. However it seems my look is not having an affect but obviously it is being read since the first part is being processed. I thought I would post it to gather thoughts... Thank you!
Quote: Private Sub lstbagels_Click() Dim newcharge1 As Currency Dim newcharge2 As Currency Dim oldcharge As Currency If oldcharge = 0 Then newcharge1 = Me.lstbagels.Column(2, Me.lstbagels.ListIndex) Me.txttotal.Value = Me.txttotal.Value + newcharge1 oldcharge = newcharge1 Else newcharge2 = Me.lstbagels.Column(2, Me.lstbagels.ListIndex) Me.txttotal.Value = Me.txttotal.Value - oldcharge + newcharge2 oldcharge = newcharge2 End If End Sub
I have one more question:
I have a ComboBox in a form that is looking at a field in a Table. When I select one of the items from the drop down it finds the record and shows all the fields in the form...
Everything works great, but
THere are quite a few records in the database, maybe 1000. If I use the scroll on the drop down and drag it to its bottom and let go, its not actually at the bottom. I have to do this repeatably in order to get to the full set of records.
Is there and easier way to do this?
I have run into this on other occasions with smaller number of records as well.
Would this run faster if I grabed this list from a query instead? And then set the form record to the value of the drop down (runngin the query)
Thanks in advance...
Hello,
Ok, let me preface this with I'm a newbie at Access, and I'm currently using Access 2003. I'm just hoping someone can point me in the right direction. Right now I have multiple tables with set data in them that doesn't change linked to another table where I have a drop down menu (combo box I think it's called in Access) to easily select the given data from. I want to have a drop down box (combo box) to have a set of given variables I have in a table to put in the search criteria. That way a user doesn't have to type the exact criteria into a query. That way I can easily put in the variables I want to search from in the query with less chance of an error. I'm hoping this is possible.
Ideally if I could get help in how to make a form that would allow me to have the set of columns from the corresponding tables with combo boxes to select from and then be able to have a "subimt" button and have it organize the selected results into that form as well.
Thank you for taking time to read this.
Hi all,
I'd like to modify a query of mine a little ...
Currenlty, when I click on my query and have it begin ... I have it set up where I then enter three seperate pieces of distinct data so that the exact information I'm in need of, which is stored in 2 seperate tables, can then be pulled together and displayed into one final combined table ...
I'd like to modify this beginning point where ... once I click on the query to begin, I can then just choose from a Drop Down list which has all the various rows of information displayed via these 3 unique indentifiers ... I then just scroll through and pick what I need and it then populates the final combined table as before ...
How do I add the drop down effect at the start of the query?
thanks,
TIM
Hi Everyone
I'm not too sure if this request is a query issue or not so please excuse if this is posted in the wrong section.
I am creating a database that picks up the start and finish time of each employee and displays the total hours worked by day and week.
I would like to be able to search by employee name from a drop down list but I am a unsure how to do this.
Can anyone assist?
Many thanks :)
Hi folks,
I want to populate a drop down combo box with a list of 10 years (current year +/- five years). I can enter them as a Value list but I don't want to have to update the list every year. Neither do I want to allocate a table to maintain a list.
Any ideas as to how I can auto populate the combo with a 10 yr span (5 previous and 5 next) and default to current year?
Thanks,
Dave
Hey guys, I have a question that I am hoping you can point me in the right direction on. Before I ask it, I am new to access. I have been looking at templates and reading and things on how to build a database. I have my tables setup, relationships I think are right and my form created. One of the things I am having trouble with is creating a drop down list to be able to pick a customer name for. I setup the combo box and have a customerID as the control source in the properties. I have the Row Source Type as table/query and the Row Source query setup so it takes the info from the customer table.
query:
field: customer.* name
table: customer customer
I think I am doing this the same way the templates have the properties but for some reason it won't allow me to pick from the list. It keeps saying "control can't be edited. It's bound to unknown field 'CustomerID'".
I attached my database. I hope it is clear enough that you might see what I have done wrong. Thanks in advance.
I'm pretty much a noob to visual basic, I've only taken C++ so I dont really know VB language. ok here is my question... I have a form and I added a drop down list through a table. What I want it to do is output into another text box within the same form when an item in the drop down list is selected. I think I have to make an event procedure for the "On Click" or "On Got Focus" selection. please help me out !!
View 5 Replies View RelatedIf i have a drop down list containing employee type names e.g. Plumber plasterer bricklayer from the employee type table. How would i select the bricklayer and bring back records associated with the selected employee type . I have done a query to go and get the records for the employee type and it works for one employee type.
But I cannot get the button to go and get the records when i select different employee types from the drop down list. I could add loads of buttons with the same query and change the employee type but looks a bit crowded and untidy. Am i missing something simple or is it just complicated.
I have a drop down list in a table with name and commission rate. When I choose the name it displays but I want both the name and the commission rate to display in my table. I want to use a data entry form to enter values in the table and I want the same in the form too (when I choose a name I want the corresponding commission rate to show up).
View 3 Replies View RelatedI have a people table for all the people .I set a query to select only those that are Family Navigator Mentors ID,FName,LName..I use this query for a lookup on the family navigator table to select a mentor on the form I want the ID FName and LName to show up not just the ID.????
View 9 Replies View Related