On my form I am opening a listbox onclick of a command button. Form recordsource is from a select query. In the listbox rowsource I have this:
SELECT tblWO.Status, tblWO.Property, tblWO.Asset FROM tblWO WHERE ((( tblWO.Status) Like [Forms]![FrmFilter]![FrmFilterCombo] & "*"));
This only returns the Status column and has all of the records instead of being filtered by FrmFilterCombo on the FrmFilter form. Where is my error?? I appreciate any help you can offer!!!
Hi, I'm new here, so I hope I'm posting this in the correct place. I've searched the forum to see if there are any existing threads that might help me, but I've not found anything that does... (I think this thread ( http://www.access-programmers.co.uk/forums/showthread.php?t=93444&highlight=Copying+data )may be trying to achieve something similar to me, but I'm a beginner and don't really understand it)
I shall stop waffling! I'm not entirely sure that what I'm trying to achieve is possible, I expect it probably is!
Right, I have a form (frmGroupRegister, which contains exactly the same fields as the table it comes from, tblGroupRegister), which consists of three things:
-GroupDate - The date a group took place on. It is my primary key, as no more than one group occurs on a specific date.
-ParentList (A listbox which contains a query showing the ID number, forename and surname of everyone in a table, tblParentDetails)
-ParentsAttending (A blank listbox)
I would like to place buttons in between the ParentList and ParentsAttending, which would allow users to conduct a 'register' of attendance by copying individual/multiple details from ParentList into ParentsAttending (much like you get when choosing which fields to include in a form when using a wizard for example). I would also like them to be able to remove people from ParentsAttending by using a button in case of accidentally adding the wrong person into the ParentsAttending box.
I'm aware that another, probably simpler way of achieving this would be to use a tick-box system, but I feel that visually, the first method would both look better and demonstrate who is present more clearly.
Any help would be much appreciated, but my Access skills are quite basic and things will probably need to be spelled out for me. I'm using Access 2000 and Windows XP. Thanks for your help, Alice :)
I am using the selections made of the form to generate a query for the user.
I have a CITIES listbox that is populated with values from a stored query.
I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.
I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this
Code:
SELECT DISTINCT (t_location.LOCATION) AS Expr1 FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));
I also want multi-select so that is you can un-select all and get the results for all cities.
Here is my half thought approach.
Code:
Private Sub CityList_AfterUpdate() 'Dim LocQryStr As String 'Dim r As Integer 'Dim ctl9 As Control 'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
[Code] ...
I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.
I have a list box called "product list box" based on a query called "searchqry", i also have another listbox called "type list box" , how do i get the type list box to only show "types" based on the section in products list box?
1. On my main form, I have a listbox, I would like to edit the values of the listbox.
To do this, I have a popup form with 2 listboxes, one to have the values of the listbox on the main form, and the other listbox with option values for the 1st
1) how to i pass the rowsource sql of the listbox on the main form to the listbox on the popup form
2) how on closing the popup form, do i update the rowsource sql listbox on the main form from the changed value of the popup form listbox rowsource sql
Okay then, after much trouble and confusion, I finally realized I need to use an Extended listbox in order to allow for multiple items to be selected from a list on my form (rather than the evil multiple selection combobox!).
However, now I am trying to figure out how to make one listbox (IndustryClassification) only be visible if the item "Industry" is selected in another listbox (TypeOfBusiness). Coding I can use for this in the AfterUpdate event of the listbox?
I've got this multiple select listbox which writes data into a textbox:
Private Sub List2_AfterUpdate()
Dim Cursisten As String Dim ctl As Control Dim Itm As Variant
Set ctl = Me.List2
For Each Itm In ctl.ItemsSelected If Len(Cursisten) = 0 Then Cursisten = ctl.ItemData(Itm) Else Cursisten = Cursisten & "," & ctl.ItemData(Itm) End If Next Itm Me.txtCursisten = Cursisten
End Sub
And I've got a SELECT ALL button to select all records in the listbox:
Private Sub cmdSelectAll_Click() On Error GoTo Err_cmdSelectAll_Click
Dim i As Integer
If cmdSelectAll.Caption = "Alles Selecteren" Then For i = 0 To Me.List2.ListCount Me.List2.Selected(i) = True Next i cmdSelectAll.Caption = "Alles De-Selecteren" Else For i = 0 To Me.List2.ListCount Me.List2.Selected(i) = False Next i cmdSelectAll.Caption = "Alles Selecteren"
The only thing is that when I use the SELECT ALL button, the function List2_Afterupdate doesn't work anymore. There must be a simple solution but I just can't figure it out. Can anyone please help me?
Hopefully somebody can help me on this one. I searched the whole internet and access forums, but I didn't find the exact solution for my problem.
I've got a table with students, a table attendance, where I now only save the students who are absent, but I would like to save also the students who are PRESENT (at the same time). I've got a combobox where I filter the Class, which then updates a listbox with the students from that class. What I do now is select the students from the listbox and then press a save button and it saves the records to the table absence with STATUS: ABSENT.
I would like to save the NON selected students also in that table, but with PRESENT in the column STATUS.
I thought of making another listbox next to it, where after selecting the absent students, they wil apear and disappear in the PRESENT table so I can store all the information. But the only problem is that I can find this solution when the listbox is populated by a list of values instead by a table or query. And the other solution is to store the temporary data into 2 different tables, but that's not working for me because it's a multi user database and everything will be messed up.
Hope that someone can help me, I will be very happy.
Firstly, is it possible to hide unchecked values in a listbox? I have a user with several roles and I want to only show the ticked roles in the listbox.
Secondly, can you create a hyperlink on listbox values? i.e, if I click on "Manager" in the roles listbox, it follows that to another form and opens the record about managers?
Hello. Recently posted for advice on building an access database on health service training. I had a lot of help but I suppose I haven't really grasped relationships yet. My database looks like this:
I have 1:M tb_staff to tb_training_done and the tb_training_done is linked to the tb_courses 1:1 - not sure if this is right but it's the only way access will do it.
Anyway, my idea was to use a form and subform to enter training done but after setting up the lookup it won't allow duplicate courses i.e. same course done each year. Can anyone explain how the lookup works? Where are the details of courses done stored?
im having a bit of trouble generating a field in one of my queries. i think its fairly simple, all i want to do is have the field in my query to show a date, that is just calculated by subtracting 5 days from the current day. i have something that looks like =Now()-5 but that doesnt work obviously. so im just asking whats the imput going to look like if you want to subtract 5 days from a date? oh and im using short dates for the date inputs
I am trying to build a report that lists all call in's and all tardies on the same sheet. They are two different tables, that I made queries out of because of calculation fields. Do you know how I can include all the needed data in the same report?
What I'm getting when I try is either all call-in's for each name that has tardies or vice versa. I need to see all data on one report
The reason I didn't just put all the data in one table was because we track call-in's by occurence, but we track tardies by pay period. So each call in is logged in the call-in table with the date it happened on, and the tardies are logged in by pay period, so only the total is stored in the field.
Do you have any ideas? I'm really stuck and I've been workin' on it for three days now and I don't think I'm going to find the solution by myself. Sorry. Thanks!
Gee, I know this is probably so simple...I've never in my life worked with any kind of data base program. I've spent 2 hours trying to make it work and it doesn't. I've tried so many things that I can't even remember what steps I did. I'm trying to create a one column list for phone numbers, no names or anything else...the most important thing is that when I add new phone numbers to this list I want Access to alert me that it is a duplicate and not allow me to add it.....first of all it won't let me add entries with dashes such as 312-5964, I tried selecting "numbers" and also "auto number", both didn't work, what am I doing wrong...can someone give me the steps from the beginning please....I don't have time to figure this out and it's holding me back from finishing a task. I have to get working on my Video Professor Learning CD for Access which I have at home, but right now I'm at work and can't do that...hellllllllp! I'm ready to tear my hair out :eek: Thanks, Rosey
I have an Access DB I have to put on numberous laptops. The DB is being transported via a CD. When i try to copy the DB from teh CD to a new folder on the laptop I get messages that the DB may have a virus and it cant be copied, or I need to install a microsoft service bulliten...xxxx ( I am not sure what the number is). The DB does contain VBA code and I am certain it is virus free. Is there some setting in access which enables this file to be copied on laptops which do contain virus or firewall protection or must I disable virus protection on these machines before instlaling the program or is it another issue altogether. Thanks.
I have genreated an autonumber in my table, at this is used as key. after importing 135 records it starts to jump 48 records before it creates a new. This means that with every record i import after 135 i have a space of 48 between each. So what sort of thing can cause this? and even better how do i fix it?
I have been using the forums for the past month and a half, and they have been extremely beneficial to me. I am working on a database that cross references my companies Steel standards to American standards. I am trying to make it searchable in a couple of different ways. I have borrowed code found on this site to make two different search engines. One works very well but the other I am getting hung up when I try to switch from the previous code to mine. below is an example of my code. It is taken from code placed on here previous called "SampleSearch"
Private Sub cmdSearch_Click() On Error Resume Next
Dim sSql As String Dim sCriteria As String sCriteria = "WHERE 1=1 "
'tblSubject qrySearchCriteriaSub If Me![Spec] <> "" Then sCriteria = sCriteria & " AND qrySearchCriteriaSub.Spec = """ & [Spec] & """" End If
If Me![SteelType] <> "" Then sCriteria = sCriteria & " AND qrySearchCriteriaSub.SteelType like """ & [SteelType] & "*""" End If
If Me![Group11] <> "" Then sCriteria = sCriteria & " AND qrySearchCriteriaSub.Group11 like """ & [Group11] & "*""" End If
If Me![Group143] <> "" Then sCriteria = sCriteria & " AND qrySearchCriteriaSub.Group143 like """ & [Group143] & "*""" End If
If Me![Substitute1, Substitute2, Substitute3, Substitute4, Substitute5, Substitute6, Substitute7, Substitute8, Substitute9] <> "" Then sCriteria = sCriteria & " AND qrySearchCriteriaSub.Substitute1 = """ & [Substitute1] & """" End If
I apologize also because I do not know how to post this in a scrollable window. If someone could help me with that too.
The problem with the code is that I am using a form to filter a query, this will then display the table of the steels that meet the requirements. However, I have altered all of the fields to my names and the combo boxes have my information, but they do not seem to place any parameters on the query. I attached the original DB that I got this code from any help would be appreciated.
I have a access 2002 database that was handed down to me.. this program does some document control, set up with access rights for different users.I got into it to clean it up a bit and I've hit a snag. I converted the database from a 2000 to a 2002. Amidst playing around with the Log-In screen somehow my Log In button stopped working; except if you type your password and then hit enter.Now the Log In button ( cmdLogIn_Click() ) is pointing at the same exact script as the ( txtpassword_KeyDown(KeyCode As Integer, Shift As Integer) ) function.I don't know any VB and I'm not really familiar with Access, so if anyone could decode this for me I would be really grateful. Here is the script that those two functions are pointing to:Private Sub LogIn_Click() Dim strPassword As String Dim strPasswordAttempt txtPassword.SetFocus strPasswordAttempt = txtPassword.Text cboUsername.SetFocus strPassword = DLookup("[Password]", "User", "[Username] = '" & cboUsername.Value & "'") If strPasswordAttempt = strPassword Then strUsername = cboUsername.Value intAccessLevel = DLookup("[AccessLevel]", "User", "[Username] = '" & cboUsername.Value & "'") bolApprover = DLookup("[Approver]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditDocumentInfo = DLookup("[EditDocumentInfo]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditUserInfo = DLookup("[EditUserInfo]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditReferenceDocuments = DLookup("[EditReferenceDocuments]", "User", "[Username] = '" & cboUsername.Value & "'") bolViewAllApprovers = DLookup("[ViewAllApprovers]", "User", "[Username] = '" & cboUsername.Value & "'") bolDocumentControl = DLookup("[DocumentControl]", "User", "[Username] = '" & cboUsername.Value & "'") ' MsgBox "Your access level is " & intAccessLevel Startup ' run startup sub DoCmd.OpenForm "Welcome", acNormal DoCmd.Close acForm, "Login" LogEvent strUsername, Now(), "Login Successful", "Network Username: " & strNetworkUserName & " on Computer " & strComputerName 'LogEvent strUsername, Now(), "Login Successful", " on Computer " & strComputerName Else MsgBox "That password is incorrect. Try again." LogEvent "Unknown", Now(), "Login Failed", "Attempt: " & CStr(cboUsername.Value) & " , " & strPasswordAttempt & " Network UserName: " & strNetworkUserName & " on computer " & strComputerName End If End SubWhen you click the Log In button it does actually run the script, but it gives me the "The password is incorrect. Try again." message even if the password is correct. I can just click into the txtpassword box and hit enter and it will log me into the database.P.S. I say again, this isn't my code.. its probably super ugly, but I cant' tell :o
Like I stated on the title, this forum got me into trouble. By following the reading in this forum and some recommendations from the regular users I have created thre database for the place I work at. now they want me to be the official DB admin! :eek:
So now I'm coming back to you guys for more advice... :rolleyes: what reading should I do to better my Access knowledge over all.
Any recommendation is welcome.
I have already contacted th local college and they do have a distant learning class for Access and I will be taking it the next semester, but I would like to do some reading into becoming a REAL Access programmer with a solid foundation.
There must be some good books out there to purchase to get me started. So all are welcome to give me some feedback.
Hey guys- I have a software program that I am exporting data out of into excel (it's SQL based). From Excel, I then am importing into Access into an existing table previously created from a similar file (same columns, just different record sets). I am running into problems when importing files...
If I import into a new table, and have Access create the table based off of the Excel file- it works fine. If I try to import more records into this same table, it tells me it failed to import and that an error occured (no error's table created, no error number given). If I delete the original records in the table, and re-import the SAME RECORDS that were originally imported to create the table- it doesn't work. It will only import my files if it creates a brand new table for each import, every time.
Can Access not import into an existing table? It should- but I dont get what's going on here. I have tried DBF, HTML, txt files- all the same results. I really don't want to have to create a new table each time- as now my Queries will have to be updated everytime I create a new table. Can you please help?
I don't how this happened but a crucial field (ClientID) in my Clients table changed from Autonumber to Number. It's the primary key and linked to other tables. Access won't let me change it back...and until i do my entire database is at a standstil - cannot enter new clients.
What I am trying to do is when someone is filling out the first table (tblLoanDetails) they have a dropdown to choose the escrow company, then they must choose the escrow officer
Each escrow office can have many officers, however, when they choose the escrow officer from a dropdown, I only want that offices escrow officers to appear in the dropdown.
I have been asked to redesign a database that tracks a huge number of data points. These are projects and the original table had over 100 fields. I have managed to separate them to related tables in an attempt at normalization. They are:
tbl_workorders (main project info) tbl_services (services ordered) tbl_contacts (internal company contacts) tbl_customers (customer information) tbl_project_dates (milestones of project)
Now this is different from other databases I've designed because all of the tables need a one-to-one relationship with the main table (tbl_workorders).
Am I heading for trouble with so many tables existing in a one-to-one? Also, The table tbl_workorders has its primary key as an autonumber. I want any new order on this to create matching foreign keys on all the other tables...I assume this should be handled since I have enforced referential integrity with cascade on update/delete for all the other tables.
Hey, I have a query to calculate the stock balances and their values.....the client is using FIFO method...therefore, the stock may have different values at different stage......but they want to display the latest unit price in the report.......I selected "Last" at the UPrice column in the query, but the query doesn't give me the value I want.....
e.g.
Date In Out UPrice 03/12/05 12 0 22.00 03/14/05 0 12 22.00 03/15/05 15 0 24.50
In this example, the report should displays 24.50, but it displayed 22.00..... can someone point to me where I have made the mistake?? Thank you
Hi, im realy realy stuck this time. This is the problem:
i have 2 tables. groupproject, employeproject.
Groupproject has: groupcode(pk), employeCode(pk), projectCode(pk) employeeGroup has: employeeCode(pk) and projectCode(pk)
a project can have a group and a group can have members. A member is related to a project. Now i want to display all the members who are not in the selected group . For example, i select group 1 and i display all the members who are not in 1. The problem i keep have is that for example:
employee 1 is on group 1 and in group 2 and goup 1 is selected, he wil be show. This is because i use a statment "<> selectedgroup" . So the query runs trough te tables, comes back with group 1 and says, ok employee 1 is in the group i will not show. BUT THEN the query comes to eployee 1 in group 2 and the query says, hey your not in group 1, i throw you on the list.
can someone help me with this irritating issue. i tried this query
SELECT ProjectGroup.ProjectCode, ProjectGroup.GroupCode, ProjectGroup.EmployeeCode FROM ProjectMembers LEFT JOIN ProjectGroup ON (ProjectMembers.ProjectCode=ProjectGroup.ProjectCo de) AND (ProjectMembers.EmployeeCode=ProjectGroup.Employee Code) WHERE (((ProjectGroup.ProjectCode)=Forms!GroupView!Proje ctCombo) And ((ProjectGroup.EmployeeCode) Is Null)) Or (((ProjectGroup.GroupCode)<>Forms!GroupView!GroupView));
I have a table where I'm recording when I need to follow up with a client. From this table I want to create a query where as the criteria is everything more then 30 days from todays date. I built an expression of Expr1:[date of follow up] - date(). I set the criteria for <30. It gives me everything overdue ( a negative number) and everything going to be due in 30 days or less. However if I take the 30 out and substitute a parameter"[How Many Days]". It only gives me the positive numbers. Why is that and what can I do? I would like to take this query and build a report off of it where as when I open the report i will have to answer that question and it will give me everything overdue plus what will be due.
