If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Using a Combo Box to find a Record
Hi Allen
I have checked the forms Data Entry properties and the FilterOnLoad property. These were all correct. However i have managed to get the form to work! I am not sure why and was wondering if you could explain it to me. It may be something very simple that i have overlooked. Here goes. My tables again (from left to right): TblArtists ArtistID (PK) (Autonumber) Artist_Name TblTracks TrackID (PK) (Autonumber) Track_Name ArtistID (Number) RecordingID (Number) TblRecordings RecordingID (PK) (Autonumber) Title Date Number of Tracks I made the query and added the fields i wanted. I played around with which table fields such as ArtistID would come from(e.g., TblArtists or TblTracks). As explained i encountered problems with the code you gave me, displaying the records and not working once i saved the form. I got the form to work when i added the TrackID from TblTracks. Is this the reason the form now works? Due to adding this field every track on the record is displayed as opposed to just each record but it works. I am unsure why as TrackID is a primary key but is not directly linked to TblArtists or TblRecords. Is there any way on opening the form that the combo box is blank and no records are shown? Is there also a way to make sure no data can be added to the combo box or to any of the fields? I just want this form to view records not add, change or delete the. Many thanks for the help so far. Adrian "Allen Browne" wrote: Something else is going on. Perhaps you have the form's Data Entry property set to Yes, so it doesn't show existing records. Or perhaps FilterOnLoad property is Yes so it runs a prior query. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "adrian007uk" wrote in message ... Hi Allen This just gets stranger and stranger. I have managed to get the combo box/form working. However, once i save the form and go back to it, it does not work. I can select an artist but no records show up! When i first create it everything works fine. Adrian "Allen Browne" wrote: So, the primary keys are autonumbers, and you have the relationships defined with Referential integrity. The cascading boxes you checked in relationships won't cause the problem he cascading updates are meaningless (since the autonumbers never update); cascading deletes may be dangerous (i.e. if you delete an artist or record, it automatically deletes all their tracks too.) It should work as suggested. Are you still getting the "parameter error" message? If it's just that you are not getting the other records then something else is wrong. For example, if you have Martin Buble in tblArtists twice, you will have 2 different autonumbers for him, and if you pick the wrong one, you won't get the matching tracks shown. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "adrian007uk" wrote in message news Hi Allen Thanks for all your help so far. I have gone through your list. The random numbers were in the artists table for some reason so i managed to get rid of them. I examined the tables and all 'auto numbers' matched up with 'numbers' in the corresponding tables. There was one difference where i had RecordID in one table and RecordingID in another. I have now corrected this. I had all the relationships correct and had ticked for 'Relational Integrity' (i have all the boxes ticked, coulkd this be an issue?). The combo box is unbound. I have tried creating it from the wizard and from scratch but i am still having problems. The format of the combo box is set to general number Since i corrected the 'RecordID/RecordingID' issue i am now having a problem with: Me.Filter = "ArtistID = " & Me.cboFindArtist and sometimes: Me.FilterOn = True Other issues i should mention is for the 'artists' field in the tracks table i have made a 'not in list' combo box to add artists when required. Could this be the problem? I am using access 2007. "Allen Browne" wrote: The symptoms you describe suggest that there are multiple issues here. If you have the RowSource correct, then you must have an artist in your table with the name 111 or something. It also sounds like you have an issue here with data types. Examine the tables. If the ArtistID in the artist table is any kind of number (including AutoNumber), then the matching field for AritstID in the other table must also be a number. Make sure you have created a relationship between these tables (using the Relationships window), *and* that you checked the box for "Relational Integrity." If you try to check the RI box and there are errors, sort out those issues first: it will help to get this issue solved. Also, check that the combo you are using to filter the form is *unbound* (nothing in its Control Source property.) And set its Format property to: General Number so Access understands its data type. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "adrian007uk" wrote in message ... Hi Allen I have managed to 'sort of' get the combo box working. It shows the records for only 1 artist. The remainder of the time the form stays blank (i.e., i select an artist nothing happens, select another nothing, select one and it shows the records associated with it but only that artist). When i look at the query i can see all of the data but it is not showing up on the form! Also i have some random numbers in the combo box as an option to select! The numbers are three 1's and a 2 then the artists shown in alphabetical order. Adrian "Allen Browne" wrote: When Access requests a parameter, it means that it can't find the name you are looking for. Perhaps your form's source table/query doesn't have an ArtistID field? Perhaps you don't have a text box with that name on your form? -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "adrian007uk" wrote in message ... Hi Allen I have followed your instructions but i am getting a box that says enter parmeter value on the form and then says to enter ArtistID. I can't get the records to show up when i select an artist from the combo box. Adrian "Allen Browne" wrote: 1. Create a query that uses the tblRecordings and tblTracks tables. Output the fields you need for your form, including the ArtistID. Save the query. 2. Create a continuous view form, so it shows one record per row. 3. In the Form Header section of this form, add an unbound combo box where the user can choose the artist they are interested in. Give it these properties: Column Count 2 Column Widths 0 Name cboFindArtist Row Source SELECT ArtistID, Artist_Name FROM tblArtsts ORDER BY Artist_Name; After Update [Event Procedure] 4. Click the Built button (...) beside the After Update property. Access opens the code window. Set up the code as follows: Private Sub cboFindArtist_AfterUpdate If Me.Dirty Then Me.dirty = False If IsNull(Me.cboFindArtist) Then Me.FilterOn = False Else Me.Filter = "ArtistID = " & Me.cboFindArtist Me.FilterOn = True End If Me.cboFindArtist = Null End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "adrian007uk" wrote in message ... I have 3 tables namely: TblArtists ArtistID (PK) Artist_Name TblTracks TrackID (PK) Track_Name ArtistID RecordingID TblRecordings RecordingID (PK) Title Date Number of Tracks What i want to do is use a combo box to select an 'Artist' and disply all 'Records' associated with the artist. If the artist was in the 'Records' table i'm sure it would be simple but because 'Artists' is in a seperate table i cannot figure out how do make it work. Could somebody please help me? Do i need to make a sepeate query? Shold the combo box be bound or unbound? Thanks |
#12
|
|||
|
|||
Using a Combo Box to find a Record
To prevent the user changing the value in the controls on the form, set
their Locked property to Yes. To load the form so that it initially displays no records, give it a filter that no records match, e.g.: Private Sub Form_Load() Me.Filter = "(False)" Me.FilterOn = True End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "adrian007uk" wrote in message ... ... i have managed to get the form to work! ... Is there any way on opening the form that the combo box is blank and no records are shown? Is there also a way to make sure no data can be added to the combo box or to any of the fields? I just want this form to view records not add, change or delete the. |
#13
|
|||
|
|||
Using a Combo Box to find a Record
adrian007uk wrote:
Hi Allen Is there any way on opening the form that the combo box is blank and no records are shown? This question has two parts. 1. When you first open the form, the combo box is always blank. If it is not, than you have a bound control. Change it to unbound. (your control ControlSource is blank in the properties tab "Data") 2. If you want no record shown, just set the Form's property Data Entry = Yes. However, you next question is..... Is there also a way to make sure no data can be added to the combo box or to any of the fields? I just want this form to view records not add, change or delete the. If you want to prevent Add, Edit, and Delete on the form, set the Form's properties 1. Data Entry = Yes 2. Allow Edits = No 3. Allow Deletions = No 4. Allow Additions = No -- Please Rate the posting if helps you. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200909/1 |
#14
|
|||
|
|||
Using a Combo Box to find a Record
Edits: Data Entry should be No.
AccessVandal wrote: 1. Data Entry = No 2. Allow Edits = No 3. Allow Deletions = No 4. Allow Additions = No snip... -- Please Rate the posting if helps you. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200909/1 |
#15
|
|||
|
|||
Using a Combo Box to find a Record
Thanks for the help. Do you know how i would stop the form/combo box showing
duplicate entries? Thanks Adrian "AccessVandal via AccessMonster.com" wrote: Edits: Data Entry should be No. AccessVandal wrote: 1. Data Entry = No 2. Allow Edits = No 3. Allow Deletions = No 4. Allow Additions = No snip... -- Please Rate the posting if helps you. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200909/1 |
#16
|
|||
|
|||
Using a Combo Box to find a Record
On Mon, 7 Sep 2009 15:25:01 -0700, adrian007uk
wrote: Thanks for the help. Do you know how i would stop the form/combo box showing duplicate entries? Base the Combo Box on a query with its Unique Values property set to Yes. -- John W. Vinson [MVP] |
#17
|
|||
|
|||
Using a Combo Box to find a Record
Did John's suggestion on the query properties work for you?
adrian007uk wrote: Thanks for the help. Do you know how i would stop the form/combo box showing duplicate entries? Thanks -- Please Rate the posting if helps you. Message posted via http://www.accessmonster.com |
#18
|
|||
|
|||
Using a Combo Box to find a Record
I have not had chance to try it out yet.
I think i have figured out why i was having so much trouble initially with the form. In the record source of the form, it shows it is based on a query, but at the end it has "WHERE ArtisitID=3". I removed this and everything seems to work but it is showing duplicate enteries. I am hoping the advice John and yourself has given me will sort this last problem out. Adrian "AccessVandal via AccessMonster.com" wrote: Did John's suggestion on the query properties work for you? adrian007uk wrote: Thanks for the help. Do you know how i would stop the form/combo box showing duplicate entries? Thanks -- Please Rate the posting if helps you. Message posted via http://www.accessmonster.com |
#19
|
|||
|
|||
Using a Combo Box to find a Record
As to why you're having dulplicates entry, I don't know. Remember we can't
see your DB. Perhaps it was due to incorrect relationships, indexes, and etc. or just simply a dual entry in your table. It is better that you show us your query string. Go to your query for the form and combobox and open the query editor in design mode. In the left top of your menu icon "View", click the tiny down triangle/arrow, select the "SQL View". Copy and paste that into your Internet browser when you're replying. According to your tables, there should be only one Primary key indexed. TblArtists ArtistID (PK) 'indexed no dulplicates Artist_Name TblTracks TrackID (PK) 'indexed no dulplicates Track_Name ArtistID RecordingID TblRecordings RecordingID (PK) 'indexed no dulplicates Title Date Number of Tracks If a single table query on TblArtists "Select * From TblArtists" has dulplicates, it means that you have two or more entry in your table and you'll have to correct your data. adrian007uk wrote: I have not had chance to try it out yet. I think i have figured out why i was having so much trouble initially with the form. In the record source of the form, it shows it is based on a query, but at the end it has "WHERE ArtisitID=3". I removed this and everything seems to work but it is showing duplicate enteries. I am hoping the advice John and yourself has given me will sort this last problem out. Adrian -- Please Rate the posting if helps you. Message posted via http://www.accessmonster.com |
#20
|
|||
|
|||
Using a Combo Box to find a Record
Hi John
This solved the problem thanks. Adrian "John W. Vinson" wrote: On Mon, 7 Sep 2009 15:25:01 -0700, adrian007uk wrote: Thanks for the help. Do you know how i would stop the form/combo box showing duplicate entries? Base the Combo Box on a query with its Unique Values property set to Yes. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|