Forms :: Multi-field Primary Key And Subform
May 16, 2014
My Db has a master table with a two-field primary key.
I have a main form, based on a query that primarily grabs data from that table.
The form has a subform based on a table which is the "many" side of a one-to-many relationship with the master table. I'm having a problem adding individual records to the subform.
The subform is linked via Master/child fields to the main form, but only on one of the two fields comprising the PK. When I add a new record in the sub it does not pull the PK data to the FK fields in the sub table and I suspect it is because of only having one of them properly linked.
The subform relates to an annual certification process that we track with vendors. The sub-table is populated each year with the two FK values for all qualifying vendors in one go. If I then need to add a single certification record for a single vendor I want to have a button on the subform that will pull the values from the main form fields, and ask the user for the cert year.
I tried adding a new record in vba via
Code:
Private Sub btnAddSingleRec_Click()
Forms!frmMainForm!subfrmCurrentProcess.SetFocus
DoCmd.GoToRecord , , acNewRec
DoCmd.OpenQuery "qryAddSingleCert"
with the query being an Update query with the following in the Update To line and "Is Null" in criteria:
Forms!Mainform!PKField1
Forms!Mainform!PKField2
[Enter Cert Year]
This doesn't throw an error, but the Access warnings say I'm updating 0 Rows.
View Replies
ADVERTISEMENT
Jul 31, 2014
I have a form that has a FIND button. I want the user to enter the 3 fields that make up the primary key, then find the associated record. How can I do this in Access - not VBA?
View 1 Replies
View Related
Jul 1, 2013
Every time I mentioned multi-value fields I get the same input, don't use them. So I am trying to replace this field with a junction table. This will have multiple records for each record in the original subform. The only problem is that the original subform is set to continuous view and now I get the error that "you can't view a form as a continuous form if it contains a subform".
View 4 Replies
View Related
Aug 21, 2013
I have a main form with multiple sub-forms (each a separate tab). Having imported the fields from the equivalent tables, none of the multi-value combo box fields on the sub-forms work as they do not show anything. I tried implementing one on the main form (to test) and it worked fine. What am I doing wrong with the sub-forms fields?
View 1 Replies
View Related
Aug 7, 2013
I want to use VBA to hide columns in a subform based on what is checked in a multi value look up field.
I am creating this DB for use with sharepoint as a web database, which is why I am using the lookup field to begin with. There will be a client DB to use with some VBA code
So what I have is a lookup field with tests "Test 1, Test 2, etc" on form sample.
There is a subform called results, and I want to hide certain columns based on what tests are performed.
I tried using an If Then statement (code is being run on subform load)
If Me.Parent.fieldTest = "Test 1" Then
Me.Test1Col.ColumnHidden = False
Else Me.Test1Col.ColumnHidden = True
That is basically the code I was trying to use. I am getting an error 13, which I assume is because fieldTest can not = something since it is a multi value look up field.
View 3 Replies
View Related
Apr 1, 2015
I am building a user creation form. I set it up like the screen shot in the first attachement. 3 different tabs.
In tab 1 you create the user which then gets a unique key (DocentenID).
Tab 2 you can input more information about the user (in a different table).
I want it to grab the primary key (DocentenID) and insert that into a field in the other table same goes for tab 3.
View 1 Replies
View Related
Oct 6, 2014
I have 7 multi-select list boxes, which I want the user to be able to select multiple items and have it filter a subform by what is selected. The subform will not be visible.
Here is the code I tried for filtering my subform (which is throwing an error when I call it).
Private Function MasterSearch()
On Error GoTo Error_MasterSearch
Dim StrgSQL As String
Dim WhereClause As String
StrgSQL = "SELECT * FROM MasterTbl"
[Code] ...
The above code was to test just 1 of the 7 listboxes. When I call it on click for the command button, it throws an error about the "Call MasterSearch".
View 1 Replies
View Related
Aug 5, 2014
I've imported some historic user data from some spreadsheets and I have a field which is a unique PIN code for each user. This is set as the primary key on my new table. Duplicates are not allowed. It's a text field with values ranging from 0005 through to 9576. The maximum allowed range of values will be from 0001 to 9999. There are currently only 300 records in the table so as you can see, there are lots of PINs available for use.
I'm building a form to allow a new user record to be created and want to automatically allocate the next available PIN. So if I was entering a new record now, the PIN to be allocated would be 0001.
how I can create an event for creating a new record that looks up the next available free PIN.
View 4 Replies
View Related
Oct 7, 2013
How do you create a Multi-Value Field using a Combo Box on a form?
View 1 Replies
View Related
Aug 16, 2014
I have a Job Plan form containing a selector that chooses a system number from another table. I want to (inside the same form) look in a third table that ties system numbers to equipment items (one system has multiple items of equipment) and generate a list box displaying those equipment items. I think I know how to do that bit.
I then want the user to be able to multi-select from the list and somehow to save those selections against the Job Plan number.Is the best way to do this to create a multi-value field in the Job Plan table and store them in there?
From what I read, the best way is to create a join table between Job Plans and Equipment Items, but I'm not quite sure how to connect the selected items in a list box to that - is there some sort of obvious way to do that?
View 2 Replies
View Related
Jan 3, 2015
Trying to use some code to 'Select All' from a 'Check Box Drop Down' Multi Value Field . The table field size is long integer.
When trying to "Select All" the code returns a RTE 3163 "This field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data".
Private Sub cmdSelectAll_Click()
Dim SelVals, i
ReDim SelVals(0 To lkupAssignedTo.ListCount - 1)
For i = 0 To lkupAssignedTo.ListCount - 1
SelVals(i) = lkupAssignedTo.Column(1, i)
Next i
lkupAssignedTo.Value = SelVals
End Sub
Not my code, but just something I have found and trying to adapt. Tried changing the field sizes but no luck. Maybe something to do with declaring variables perhaps??
View 3 Replies
View Related
Mar 16, 2015
I have a filter on a form : There is a text box (txtSearchP) where the user will enter their search term then click button to apply the filter:
Private Sub cmdSearchP_Click()
' Filter by Programme Description
Me.Filter = [Programme_Desc] Like "*" & Me.txtSearchP & "*"
Me.FilterOn = True
Me.Requery
End Sub
This works fine but actually I want to search two fields for the text entered in txtSearchP [Programme_Desc] OR [Programme]
I've tried this but it doesn't work:
Private Sub cmdSearchP_Click()
' Filter by Programme Code and Description
Me.Filter = [Programme_Desc] Like "*" & Me.txtSearchP & "*" OR [Programme] Like "*" & Me.txtSearchP & "*"
Me.FilterOn = True
Me.Requery
End Sub
I know I could give the users two text boxes and two Buttons one for each and this would work but I don't want the form to be cluttered.
View 4 Replies
View Related
Aug 26, 2013
I have a form where a user can select from a listbox (pulled from a database) and "Add product" - it then goes into a Textbox, each taking a new line when "Add product" button pressed. Then when they press "Add Customer" those details along with other entered details gets stored in my Customer Database..
The textbox store multi-line entries in the "ProductName" field heading in database.
Is there anyway i can use a List instead of the textbox? Adding individual entries to one certain customer? I have an EDIT facility aswell so would be easier to have a "Remove Product" button instead of manually backspacing a whole product entry from another textbox (containing all products, on new lines).
View 4 Replies
View Related
Dec 17, 2013
I have a form that uses a multi select combo boxes. When more than one person is in the database, if a user is updating any of the multi select combo boxes, it locks out other users from making changes to those fields on different records. Looking for solution so that every user in the database can make changes to those multi select fields on their own specific records?
View 1 Replies
View Related
Oct 9, 2014
How to add a multi valued fields in a form for example a student may have multiple hobbies, access can do this using using lookup option, but access stores multiple values in one field which is difficult to analyze, looking for efficient way of adding multiple option.
View 8 Replies
View Related
Oct 4, 2013
I have a table name ex. equipment and many fields inside ex. date, equipment name (combo box) and others.
I need to make form for all fields and button search once I click on it will open a report depending on a fields i selected.
I think that I have to use VBA
View 8 Replies
View Related
Nov 30, 2013
The main form has textbox & a Listbox with which I filtered a datasheet inside a sub form. Everything worked fine only the listbox [Discipline] is not working !! it cuase Run-time error: 3831. The multi-valued field "[Category]" cannot be used in a WHERE or HAVING clause.So how do I filter a multivalued Listbox field [Discipline] ?
Private Sub cmdSearch_Click()
'On erorr GoTo errr
Me.tblFLM_subform1.Form.RecordSource = "SELECT * FROM tblFLM " & BuildFilter
Me.tblFLM_subform1.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub
[code]....
View 3 Replies
View Related
Jul 7, 2014
This is what the search box must do:
- Searches 3 fields (StudentID, FirstName, LastName) and jumps to the relevant record.
The closer to the search box in the navigation bar below, the better, so this is what I would really like too.
- A repeat click would take me to the next result/record (if any) with the same criteria
- Instant search as soon as I start typing
View 11 Replies
View Related
Jun 15, 2014
I have a multi-select listbox (called PotentialItems) which behaves perfectly unless one of the fields has a null value in it, in which case I get an error 13, "Type mismatch" in the following code. It is valid business-wise for column 9 to be null.
Code:
Dim varItem As Variant
Dim dblTargetPrice As Double
With Me.PotentialItems
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
dblTargetPrice = Nz(.Column(9, varItem), 0) '<----- Error here
End If
Next
End With
View 5 Replies
View Related
Aug 8, 2014
I have a split form on my database where I have my combo boxes bound to a primary key but they display the description field so it's much easier to understand. Is there a way to do this with option groups? In other words, I would like the datasheet part to display the option group labels rather than the values so it is easier to understand.
View 1 Replies
View Related
Sep 4, 2014
I have a form called frm002_PAF_MonitoringMAIN and on this form I have a subform called frm002_PAF_Monitoring. The subform has a button to another form for contact details called Contact_Details. The Contact_Details form opens with the details of the person who I have selected on frm002_PAF_Monitoring. There are some fields on frm002_PAF_Monitoring that I would like to be populated on Contact_Details when opened, like FirstName and LastName. How can I do it?I have tried on afterupdate event on Contact_Details below but those don't work:
Me!FirstName=[Forms]![frm002_PAF_Monitoring]![FirstName]
Me!FirstName=[Forms]![frm002_PAF_MonitoringMAIN]![frm002_PAF_Monitoring]![FirstName]
Me![FirstName].Value=[Forms]![frm002_PAF_Monitoring]![FirstName]
Me![FirstName].Value=[Forms]![frm002_PAF_MonitoringMAIN]![frm002_PAF_Monitoring]![FirstName]
View 11 Replies
View Related
Jul 18, 2013
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
View 10 Replies
View Related
Aug 19, 2015
I am currently stuck on set focus property. I have a main from with nested subform. I am trying to move the focus from last field of the subform to another field on the main form.
Customers(mfrm)....>Addresses(sfrm)...>Orders(sfrm Add)......>OrdDetails(sfrmOrders)
Now I have a field name [Securedesign] in frmOrderdetails and I want the tab order to navigate to field [CustomerID] in frmAddresses which is a subform to frmCustomers.
View 2 Replies
View Related
Oct 12, 2005
At the moment i have an exisiting database that was not created by me. It consits of about 7 tables and a main table. Each table has a Primary Key that is a joining field. The Main form is built with subforms and when i enter a number in the primary key filed of the main table it enters that number in all the other primary keys in the other subforms.
i have now tried to create another subform to put into the main form but i cannot get this "link" between the primary fields to work.
can someone please help.
View 1 Replies
View Related
Jul 10, 2006
Hi all.
I have searched everywhere but I cannot find the anwser. I'll try to explane.
I have a table in ma database, to make it easier, let's call it Table1.
Table1:
Id,Name,Category
Now, In my form, I wanna see this table. I did it with a list, and that works perfect. Now, I want the next thing to happen:
If category = 1, then the backcolor of that record should be yellow
If category = 2, then the backcolor of that record should be green.
Now the problem is, how can I get multicoloring in my list? or in a subform?
Thx
View 4 Replies
View Related
Dec 3, 2006
I have one main table. There are about 5 main fields that my users seach often. Usually they just open the table and filter. I do not like that.
I want to build a form that has the 5 main fields available as search fields.
Name
SS
Company
Date
COC
are the fields most often searched for. Can I make a form that will open with a blank subform on the bottom and these 5 fields empty across the top. Once a user enters into one of the fields and hits enter it will bring up the those records?
Thanks.
View 14 Replies
View Related
