Hi- I am trying to create a Registration form for Events and I'm having endless trouble getting it to work.
Basically, I have four tables: Contacts, Registration, Payments, and Events.
I either cannot delete registrations when I need to-I delete a record in the form, but when I reopen it it is there again; or the pmts table is adding blank records.
So when I try to create a query to get a list of registrants, I will get two fields with the same name and Reg Id, and one with a pmt and one without.
I want one record per person registering and Im not getting that in my database.
Most, but not all, people are paying for events. Some people are staff or scholarships.
I want to be able to see just one record for every person who has registered, whether they have paid or not.
Im either seeing two records with the same reg number, or missing people that have not paid.
I have tried all kinds of configurations as far as relationships and Reg form and Im pretty stumped at this point. Any help would be greatly appreciated!
Hey everybody, I was almost done with this whole thing. It is a pretty basic database but I don't know what happened. If you look at the relationships the Employee Information table and the Metric information table are linked by employee id. For some reason when I enter information into the metric information table (via forms) it does not show up in the employee information table as a subform. Can anybody help please?
With a many to many relationship using a junction table, do I have to manually plug in the ID numbers from the related tables in the junction table for it to recognize the related data? It sounds silly and obvious I guess that you would have to, but when I go to create a form based on a many to many relationship, I would like the ability to enter data without having to enter the related ID numbers in multiple places on the form or subforms to make it work. I guess I envisioned access using auto numbers to automatically update the junction when I wanted to add data related between the "main" table and the distant table joined with a junction. Can this be fixed using a Parent/Child relationship set-up in some fashion?
1.Im wondering why there is no option to zoom in and out as we are working with relationships. 2.And even the option show all is not working; some of the tables in the relationship are kept hidden (screenshot is attached)! How could we work in this environment? 3.I do know the I can use the scroll bars, but they are not enough to perform the work more professionally
These features are available in most of the software!to zoom in/out To pan .To zoom all (extent Where are they in the relationship in Access?
I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.
I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?
I'm designing a relatively simple access database with four linked tables:
- users - computers - licenses - license_users
Each user as a computer_id linking to the computers table; they also have a set of rows in license_users, linked via their userid, that join them to the licenses table. The relationships are enforced with referential integrity. Everything works fine when I enter data manually in the table datasheet view.
I've designed a nice form to make it easier to enter data for all the tables at once, but when I try to enter the data it all goes wrong. I'm unable to enter any data into the users table fields until I have complete the computer and license data. If I enter that first, then start entering data into the users fields, the computer data suddenly disappears. If I inspect the table then the computers and license_users has been entered, but nothing is entered for the user and I can't get the rows to relate to the users!
It's a while since I designed any Access databases, and I'm stuck, so suggestions would be appreciated.
I have a couple of elementar level questions on Form creation process and standards invloving a schema with several M-M relationships. I have a highly normalized design (thereby producing several junction tables). I have attached a picture of my schema to give an ideal of the schema I am talking about.
1. What is typcially the process of creating Forms on such a schema for the purpose of data-entry. Meaning, should there be several Forms (on the order of # of tables) or should there be a few that enables simulataneous data entry to many tables through one Form. What is typically the practise?
2. If there are 3 tables (A, B, C) so that A and C have a M-M relationship and B is a junction table that makes A &B and A & C have a 1 - M relationship, then how does one enter data for A, B, and C so that all the three get populated?
3. Can someone provide a copy or link to a sample database that has a similar normalized schema and Forms built into it that takes care of the data entry to the various tables?
Hi everyone, I've tried several different things to get around this problem and have found out a lot about what won't work!
Basically I am building a HR database. The main table is M_CandidateDetails and the primary key is CandidateID. This is a foreign key in all of the other tables: MCandidateEducation, MCandidateEquipExp, MCandidateSector. (I've attached a screenshot of the relationships.)
Goal: What I want to do is create a continuous form with one record for each candidate. A record will show all of the info pertaining to a candidate. The form is to be used to for searching and filtering candidates.
Attempts.. First I tried creating a query to combine all of the information. (I've attached a screenshot of the query). The only problem with that was if joe bloggs had worked in 3 different industry sectors there would be 3 records for him. (I did have problems with the query not being updatable - solved that though by using the Dynaset property of the form)
Next I tried basing the form only on the M_Candidates table and using listboxes to display the information from the many-side of the various tables. But that didn't work as you cannot use an unbound control on a continuous form.
Then I tried putting in subforms instead of listboxes but you can't use subforms on a continuous form.
I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).
I cannot seem to define a form based on these relationships to input the data.
I've attached screenshots of the table relationships and some nested forms that I need to discuss in my database.
If you look at the forms screenshot you'll see I have a main form "business/cmc issues" that uses a combo box to select a business name; nested into that I have a second form "policy issues log" that holds details of policy issues about that business; then inside that I have a sub form "issue updates" that records brief details about the actions carried out in trying to resolve each policy issue.
The same policy issue can affect more than one business (because of a relationship between the two companies etc) but still needs to be viewed separately. So for example in the business selector combo box I might have business "ABC". In the policy issue it might say "doesn't pay on time". The "doesn't pay on time" issue might also apply to business "123" and so if I picked that business from the combo box you'd see the same policy issue.
Because it's the same issue for two separate businesses, the actions carried out will be the same, so what I want to do is, after a new action is carried out (where relevant) to be able to click a button that would run some code that copies the actions entered in the sub form for business ABC and pastes them into the sub form for business 123 where the product area and policy issue are equal. This is to avoid having to enter the same data twice.
Guys I have just been asked to take a look at an old DB that I did ages ago. The problem relates to the Suppliers Form. It has a SubForm SupplierContacts.
The user can enter a supplier contact name for the supplier account being viewed. If they navigate away from the record and then back the Supplier Contact form is blank? I can't see why this is happening. Been playing around for a while now but still can't see it?
Master / Child Link is OK: SupplierID to SupplierID
The record exists within the main table (tblSupplierContacts)
Hi, I have a sub form (courses). which isn't displaying the information that i have previously entered into it for a particular record.
i.e. I have an appointments system where the my main form 'Tutors' links to a 'courses' sub form. I can enter info into the courses form through the tutors form but when i close it and then reopen at a later time the information for courses isn't displayed for the record which i had previously entered it for. (it is in the courses table though, i've checked).
I am running Office 2003 with the latest updates on XP Professional SP2, again with the latest updates. The install was fresh only about a week ago, and I had only used this install of Access for a few hours before it started doing it!
I have tried creating a totally new database, or using an existing database, and the same happens. This led me to believe it was my Office install. I tried doing a repair through add/remove programs, which successfully repaired Office apparently, yet the problem persists. I even totally removed Office, rebooted, and reinstalled, and tried without updates!
Maybe this should be posted under VBA.... not sure. Havent seen answer that works yet. Its a form with an option group "PickWO" When you pick an option, different fieilds become visible-non visible. I created a field "PickWOvalue" to store the option value "1" or "2". That part works fine.(visible) The problem is when you reopen the form to view the records the option box value does not show or change the fields visible properties. And the on open cuauses an error. Anyone see the problem?
Private Sub Form_Open(Cancel As Integer) Me.PickWOvalue.Value = Me.PickWO.Value End Sub ````````````````````````````````````` Private Sub PickWO_AfterUpdate()
Call NotVisible
Select Case Me.PickWO
Case Is = 1 Me.ReInspectionDate.Visible = True Me.Price.Visible = False Me.WOPreview.Visible = True Me.PrtWO.Visible = True Me.WBMInvoice_.Visible = False
Case Is = 2 Me.ReInspectionDate.Visible = False Me.Price.Visible = True Me.cmdPreTag.Visible = True Me.cmdPrtTag.Visible = True Me.WBMInvoice_.Visible = True
Hi group, I am a newbie to Access and VBA programming. My original post was probably not very specific as it was not answered but I really need help on this!
TABLE1 stores a list of serial numbers and a status field (it contains the word Pass or Fail). The NEWSN table is used to capture new input of serial numbers and a field to indicate if the serial number exists in the first table.
The query based continuous form used to enter the new serial number. Upon entry of the new serial number the form requeries to find the match. If a match is found the remaining fields on the form populate correctly. If no match is found the form does not show the input of this serial number, but it does write the new serial number to the NEWSN table.
How can I get it to keep the new serial number showing on the continuous form and insert a statement into the NEWSN table indicating "No Match"?
I posted a thread on here a few weeks ago about dynamic queries. I was recommended to use Query By Form. So I went to the Microsoft website, followed the instructions, but now it's not working. The query is returning ALL of the table records, no matter how many fields I fill in (or what I fill them in with). I think something may be wrong with the Query criteria, but I typed it just like the site said. If anyone has experience with this, any ideas what I'm doing wrong?
Form has 15 fields (all are related to a field in the table). 3 are combo boxes, the others are textboxes.
Here is the directions I used: http://support.microsoft.com/kb/209645/en-us
I'm working in Access 97 and I've got a form that I want to use to create filters with.
There are a couple of things that I'm not clear about and I'd appreciate any suggestions that you can make:
1. The form seems to remember the last filter, so when I click on the Filter by form button it shows the last filter by default. I've included the following in the click event: RunCommand (acCmdRemoveFilterSort) Me.FilterOn = False RunCommand (acCmdFilterByForm) Surely either of the first two statements should remove the filter, shouldn't they? and if not then what are they for?
2. I can't get the 'OR' tabs to show at the bottom of the filter by form screen unless I apply the filter once and then click on the filter by form button again. Is there any way of forcing these tabs to appear?
I have a table filled with computer monitor information (tblMonitorManagement). I had query that uses combo boxes (cboSerialNo, cboRoom, cboDepartment) on a form for search parameters. The query is called qryMonitorManagement_Sub. The results are displayed in a subform (frmMonitorManagement_Sub). This works for both my desktop and also my laptop based forms/queries/tables.
Although this form works when using cboRoom and cboDepartment, it does not work when using cboSerialNo, so I decided to simplify it down to get to the root of the problem...here's what I did.
I created a new form, created a combo box (again called cboSerialNo), and had it look up the serial numbers in tblMonitors. Simple.
I created a query called qryMonitors_Test, added all fields from tblMonitors (not using the * option). I then set the SerialNo field criteria to the contents of the cboSerialNo on the new form. Simple. For now I'm ignoring searching on anything else.
I ran the query with no search parameters before I added the criteria, and it obviously displayed all the records in tblMonitors. Fine.
After adding the criteria (using Build...to prevent typos), I used the form to select a serial number, run the query and get no results returned.
If I copy and paste the same serial number and change the SerialNo criteria in the query to search for that it works fine. As soon as I tell it to search for the combo box contents it returns no data.
This is driving me completely bat-poo. It's nothing I've not done a squillion time before but it just refuses to work. I've got to assume it's a query issue as opposed to a form issue, but things don't get much simpler than this should be!
Hi I have a Text box that stores a date of when a problem or query is received on to a customer account, when resolved the date it is resolved is inputted in the next text box as a resolved date. My third Text box then needs to display Yes or No depending if it took less than 28 days to resolve.
Ive used a Text box and DateDiff expression to work out the ammount of days in the control source, Called query dauration then a macro to test if its less than 28 days to display the yes or no. But this is not working days Mon - Fri. Has any one any ideas?
platform : access (adp) / sql server Problem : form filter not executing in subforms
When I goto one of my subforms I can activate the form filter. When executing the form filter no records show up, it just keeps showing my criteria. When disactivating the form filter I receive an error message telling that my view must be misspelled (it's about spaces). My view is called Vw_people , as a record source it works fine so it can't be misspelled.
Is the problem the adp connection or the subform? I don't know
Anyone help please ? I'm still fairly new to ACCESS so forgive me if I'm completely on the wrong track !
I wish to add a control button to a form that will prompt me to enter a date and then calculate and display a date nn (fixed) number of WORKING days in the future.
I've seen some functions suggested in the forum and as I'm unfamiliar with this topic, I have tried putting together some code (see below) related to an On Click event for the button. I'm having syntax problems and have been battling away at this for some time without success.
Suggestions please !
Steve
Private Sub cmdDate_Click() Dim FirstDate As Date ' Declare variables. Dim Number As Integer Dim dtEndDay As Date Dim dtinterimday As Date Dim intcount As Integer Dim lnginterimdate As Long Dim intdays As Integer
Dim Msg FirstDate = InputBox("Enter a date - dd/mm/yy") Number = 20
dtinterimday = FirstDate intdays = Number Do Until intcount = Abs(intdays)
If intdays > 0 Then dtinterimday = dtinterimday + 1 Else dtinterimday = dtinterimday - 1
lnginterimdate = dtinterimday
If WeekDay(dtinterimday, 2) <> 6 And WeekDay(dtinterimday, 2) <> 7 Then
intcount = intcount + 1 End If End If
Loop
Msg = "New date: " & dtinterimday MsgBox Msg End Sub
I have a report "Work Order Details" that that is based on a query and opens along with a form that is used to set filters. Based on samples I have looked at and reading, I cannot figure out what is missing. When I select my item form one of 5 unbound combo box 's and click the "Set filter" button, it brings up a paramter box?? If I cancel out the box, I still don't get a result. Just for the heck of it, I filled in a name in the paramter box, still no luck. The report property has "Filter On" as YES. The close button, clear button, and drops downs operate perfectly.
Here is the On Click Event info from my "set filter" button:
Private Sub Command28_Click()
Dim strSQL As String, intCounter As Integer 'Build SQL String For intCounter = 1 To 5 If Me("Filter" & intCounter) <> "" Then strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And " End If Next
If strSQL <> "" Then 'Strip Last " And " strSQL = left(strSQL, (Len(strSQL) - 5)) 'Set the Filter property Reports![Work Order Details].Filter = strSQL Reports![Work Order Details].FilterOn = True End If
I have document management system for 2 projects in one db. I have a login form where user has to select the project id thru a combobox. And I have all the queries based on this combobox to display results in form or report. But after login and selecting the project, login form closes and main form opens to display data pertaining to that project. But it does not work, the moment we click close to open main form again query method apears displaying the same string :[Forms]![frmLogon]![Project]. This happens for all forms, reports. I cant't figure this out. Can somebody please help me?
I have my main form which is connected to my main table - Query 1
I have a lookup table (actually a view from SQL Server) which holds my user name - the username used to login to the database- just one value.
In my main form I have a 'Logged By' field. I want this field to default to my login name when I process a record.
I thought it would be as easy as connecting the lookup table to my main form and then in default value typing My_login.user_name.
Apparently not. It just has the #Name? in there.
Anyone have an opinion on this ?
I can use the =environ("username") in the default value which works to good effect but different users use each others PC and I would like the field to be defaulted to thier database login and not thier windows on.
Please help ! It seems so small and its driving me ceeerazy !
IIf([Forms]![FrmUserSelection]![PricingType]=3,1 Or 2,[Forms]![FrmUserSelection]![PricingType])
My field is Pricing Type and holds records 1 or 2..I would like to pass value from my group option from a form.It works fine if group option has a value 1 or 2 but it displays no records if option value is 3 What is wrong with my code.
