I have a form that displays an updatable subform. The main form has search fields and a search button that when clicked will reset the record source of the subform and then do a refresh. The problem is that after the search the subform stops becomes locked. Here is the code:-
Me!sbfDepartmentSub.Form.RecordSource = MySQL
Me.Refresh
I have tried Me.Requery also.
Hi,
I just created another query, but with this one I cannot change any of the data. What could be the reason?
Thanks
Unable to use Between query without pulling dates out of range.I
f I set the Criteria to Between [Enter the start date:] And [Enter the end date:] and input the dates 1/2/2014 & 1/15/2014 i'm pulling data for those dates but for year 2011, 2012, 2013 and 2014 as well.
if I enter in Between #1/2/2014# And #1/15/2014# it gives me just the dates I requested.I need to be able to have a user use the macro and just enter in the dates they need data for.
Also, I am using the criteria on a date ime field that I have set up an expression on - CreateDate: DateValue([TABLE]![date])the date in the table is stored with time and I just need to show the date.
I am trying to make a crosstab query to filter my records from my table.
Here is the scenario.
I want to make a query that will return me my Rep ID, Rep Name, his Bonus and his GV-Q (another value) based on every month.
Now I make a crosstab query and here is the syntax.
Code:
TRANSFORM First([TBL Qualification Data India].[Bonus Rank]) AS [FirstOfBonus Rank]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;
This resulted in a column for Rep Number, one column for Rep Name and columns for all the period of Bonus I am going to have., so there are basically 9 columns for this till this month for each month and bonus value shows as values for all these month (period) columns.
Now in this same syntax, I want to have my Rep GV-Q value as well as his bonus to show in the same query, I read and came to know that it's not possible to directly have two values or two column headings in a crosstab query, I must have to make a new crosstab query and then use a normal select query to display records from these two crosstab queries, so I went ahead and made a new similar but with one value field changed crosstab query and here is the syntax for that.
Code:
TRANSFORM First([TBL Qualification Data India].[GV-Q]) AS [FirstOfGV-Q]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;
Now after this how to make a select query to show the data from these two queries.
I can make a normal query based on these two crosstab queries and manually add all fields and then I would have my result but then after every month I have to manually enter these two extra month details from both crosstab queries to my final query and that's not what I want.
Is there any method to do this by gathering data from these two queries into one and achieve the result I want or if there is any other approach to tackle this.
To explain my database and my need for output, I am attaching few pics to make things easier if I made some mistakes in explaining my problem. It's included in attached zip since I am not able to post images or links.
Hi,
I have a data entry form that is based on four tables which are linked by a field 'fileno'.
I am now trying to enter new records via the form and update the four tables but i'm not permitted to do so. It seems that all the fields are locked for display only.
Can anyone help?
Thanks
Will.
I created a form with a query as it's source. I am able to open the tables themselves and update them, but when I go to a field on the form I'm unable
to update. What could be wrong since I have full update rights to these tables? I've checked the properties and they appear to be set correctly.
I'm trying to have the user select an item from a combo box which gets its values from a table with multiple columns. Once something is selected in the combo box the form will auto-update the adjacent text box to a value in the same row (different column). The combo box is called 'Media' and the text box is called 'Tape Cost'. When you select a type of tape in the combo box the adjacent text box should populate a value determined by the second column of the table. Instead I'm getting a message that the, "Method or data member is not found" and it highlights the red text below.
Here is the code I'm using:
Private Sub Media_AfterUpdate()
Me.Tape Cost = Media.Column(2)
End Sub
Not sure what I'm doing wrong here. Thanks!
I have a form which is based on a query which selects information from three different tables. All fields apart from one are locked, all are enabled. The table the field I wish to update is native to the current DB i.e. is not a linked table.
when I activate the form and try to click on the Check box, which is not locked, I get a beep but it does not change state.
I am using Access 2007 on win7 Pro.
I have created three forms using three tables tblUser, tlbMeasure and tblSubMeasure. The tblUser is linked to the tblMeasure table via UserLogin and tblMeasure is linked to tblSubMeasure via MeasureID. The forms are created with frmMainMeasure being the main form and frmMeasure6 a subform and frmSubMeasure a sub with frmMeasure6.
I want to be able to add sub measures that will be linked to each MeasureID and each MeasureID link to each user via UserloginID. The forms load the data in the sub forms but when I try to add sub measures using a button I added on the frmMeasures6. The button works when I try work on the form fine but when I try via the main form I am not able to add or view other records within the table.
When I click the Add button I want to be able to load back the same MeasureID number in fmrSubMeasure to ensure that it is linked to the same measure and the MeasureID is linked to the same UsrrLoginID.
The code is included below and I've attached the file as well.
Private Sub cmdAdd_Click()
If Me!frmSubMeasure.Form.Dirty = False Then
End If
Me!frmSubMeasure.SetFocus
DoCmd.GoToRecord , , acNewRec
[Code] ....
There are 2 levels of forms, the main parent form is called INSPECTION. Then there are two subforms: TESTRESULTS and addTESTRESULTS.
the form TESTRESULTS is linked to the TESTRESULTS query "QTESTRESULTS2". This query looks at two hidden fields on INSPECTION called FILE and REPORT NO. The query finds all fields inside of TESTRESULTS that match the two criteria. This works fine. The form TESTRESULTS is linked to the QTESTRESULTS2 query as direct data source link. This works fine.
a temporary table has been setup to append test results to TESTRESULT table. This works fine. What doesn't work fine is updating the subform TESTRESULTS when needed. I want it so that the welder enters his information, clicks save, the info gets appended to TESTRESULTS table and instantly updates on the form for them to verify.
[inside subroutine for saving a record] all efforts at accessing this form have failed. I've tried !form!subform!requery, it has failed, I've tried making dummy fields inside the form and accessing them and that has failed, I've tried .setfocus on the form and that has failed, each time the compiler tells me 'unknown field', when in fact I'm trying to hit the form itself.
Its worth mentioning that I've set events [on this form for any kind of update event including mouse click] to Me.requery for this form when you click on it, it updates, but I need to automate this somehow, and the form just seems untouchable via VBA.
After a scary recovery I have a new problem: Intially I changed the 'Data Entry' property on a subform, in a multi-tab form :eek: My tabs all dissappeared. (My Bad!:o )
Now that I have recovered I get a message when I close the form, asking do I want to save the changes to the design of the query.
This only seems to happen when I have read a second or third etc record that displays a different set of records in the subform.
This is a HR mdb with leave records in the subform.
Any help would be appreciated.
PS I thought I had posted this 2 hrs earlier, but can't seem to find it. Hoping this isn't a second posting ...:confused:
.... added an image of error
I have a form/subform that I want to link together using a memo column, yet when I try Access tells me that the column datatypes are incompatible. I've even tried making both the form's and subform's recordsource be the same table. Any idea how I can do this?
View 6 Replies View RelatedWhen I try to edit fields in a subform the cursor always jumps to the first record's first field with a tab stop. I can not complete the modification and the cursor jumps back to the first record. When on the first record and on any other field the cursor will jump to the first field with tab stop.
I use Access 2003.
I am trying to close a form with a subform.
I enter data in the main form and then the subform.
The main form has a save btn with an on click event which includes at the end of code
If Me.Dirty then
Me.Dirty = false
DoCmd.Close
DoCmd.OpenForm "FormName"
However the Form will only close and open the required form if I remove the two "Dirty" Code lines.
I am obviously trying to ensure that the data is saved on both the main and subforms using the If Me.Dirty......code
Trying to update a field in a record in my form after selecting from a comboox.
This an orders form with a record per row for order items. I select the product from a dropdown list which is populated by a dynamic query from the afterupdate event on another combo. When I do the selection access reports the following error:
Runtime error 3331
to make changes to this field, first save the record
Debug takes me to the line :
Me!product_id = DLookup("product_id", "products_table", myvar)
from :
[code]
Private Sub comboProd_description_AfterUpdate()
Dim strFilter As Integer
comboProd_description.Value = comboProd_description.Column(1)
myvar = comboProd_description.Column(0)
Me!product_id = DLookup("product_id", "products_table", myvar)
[end code]
I'm lost as to what to do (no such thing as beginners luck!!). I'm not even sure if the error is from the combo box or from the field that it is trying to update (product_id)
Thx for looking
Kev.
I would like the subform in the lower left corner to be blank when
I open the program. It is populated from my tblClients table.
How it works in general is you select "x" number of items from the
list box...hit the search button and the results go through a query
and then show up in the subform.
everything works ok but, like I stated I would like the subform to
be blank when the program is first started up.
It seems to be retaining information from the last start up in the
subform box.
Thanks for your help...it's coming along great so far...
see attached doc...
Im trying to work on an db in Access 2007 that was migrated from Access 2003 (in fact its been migrated several times starting from Access 97). It executes and runs with no problems in both versions.
The problem is when trying to open some queries (not all) - Access is unable to open the query in design mode and gives me this error :
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
However, as I cant open it - I cant check it. Im pretty sure none of the fields have invalid characters (they do have spaces) and Im not sure how long is too long....
I am working with a sub-form where once a staff member enters there sub measure I would want to create a duplicate of that record. The problem I am having is that once you enter the sub-form and click the duplicate button it creates a duplicate of the record selected but overwrites the first record in the table. I want it to create a new SubMeasure Number which is the primary key and assigns the record the next available number.
Also if I try to add another record after one has been added I get runtime error "3021" - No current record. I would have to close the form and reopen for it to be able to add again.
I have attached the code below:
Private Sub cmdDuplicate_Click()Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form
'Return Database variable pointing to current database
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone
[code]....
I am trying to create duplicate records from a main form frmManagers which has a subform frmSubMeasure. I have placed the duplicate button on the main form. It creates a duplicate of the main form data and gives me the option to add new record to the sub. I want the duplicate to be created on the sub form for me to just edit the scores.
I don't know how to pass the sub form data to be duplicated I thought the append query which I used would update the tblSubMeasure table which created the subform frmSubMeasure.
In the sub the append query do update the form with the new MeasureID from the mainform and the form is available to enter new data. I want the subform data to be duplicated as well
In the query I included all the fields from the tblSubMeasure table and this is appended to the same table tblSubMeasure and I place a tag on the MeasureID using "[Forms]![frmManagers].[Tag]"
Code:
Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form
'Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone
[Code] .....
I have a subform with two fields whose record source is a Query ("subquery")
Code in the query is as follows:
"SELECT Sum(capital) AS cq1
FROM capital_act WHERE (((capital_act.month)='jan' Or (capital_act.month)='feb' Or (capital_act.month)='mar') And ((capital_act.projectid)=forms!frmplan!projectid)) ;"
This query updates the field cq1 in my subform. I want to add 3 other fields for the 2nd, 3rd and 4th quarters. For instance the second quarter field cq2 field would have the capital_act.month as 'apr' or 'may' or 'jun' and so on...
cq1 value gets updated fine. I dont know how to add the fields cq2, cq3, and cq4 on the same subform whose record source would be the same query ("subquery").
I added a second SQL statement in the same query ("subquery") for cq2 but access does not let me add it.
Can somone point me in the right direction please?
Thanks.
Hello,
I have a main form which has 2 subforms. The main form is the name of the Student. The first subform is the data entry form. It has 2 fields. One for the Trimester - a drop down which has "1, 2, 3" listed. And the second field is the benchmarks (again drop down). Now the second subform is a more elaborate way of viewing (only for viewing purposes) which standards have been chosen for which trimester. Now this form's control source is a Crossstab query, which updates the Trimester field for each of the benchmarks for the particular student.
Now my problem is I am trying to update this subform which is for viewing purposes only, automatically, so that the moment a benchmark is chosen on the other subform, the [trimester] field automatcially gets updated on this form. All the benchmarks are listed on this form (view form) and a field which is the trimester field. Data entry is not allowed in this form. So the information has to get updated automatically. It does when I close and open the form, but thats not how it should work.
I tried everything I thought possible (requery, refresh, a button to click) but couldn't get the form to update.
Please help! I would really appreciate if you could help me find a solution.
Thanks in advance
A
I have a query in a subform on the main form. I have a search box that updates the subform/query as you type something (using the On Change event). You then click on the record you want which transfers the information to the appropriate text boxes (one of these txt boxes is the clientID I talk about below) located next to the search box.
I have a Contacts subform/query much like the serarch box I created and I am using a txt box (on the main frm) clientID (which I get from the above process) to filter the contacts.Now when I pass the ID to the txt box on the main form I am having trouble getting a event to trigger and update the subform/query correctly.
I am using VBA to create a simulated Click action which seems to work but is not updating the Contact subform/query, it is just resetting the subform/query. If I manually click on the txtbox with a ID in there all works wonderfully. I have attached the database. I made the clientID and btn next to it visible(this would not be visible normally).I just realised I left a button on the main form next to the clientID txt box just ignore that and click on a client then the clientID txt box to see how it updates the contacts subform..
I'm using form with subform. Main form header contain a combobox. I need to check the combobox is null or not before update a subform filed. If combobox is null then its back to Main form combobox from subform. The code below that I'm trying to:
Code:
Private Sub AssignTo_BeforeUpdate(Cancel As Integer)
If IsNull(Forms!frmDepartmentReview!cboDepartment) Then
MsgBox "You must select Department first", vbInformation
Forms![frmDepartmentReview]![cboDepartment].SetFocus
End If
End Sub
Then I'm getting 2110 run time error
Access can't move the focus to the control cboDepartment.
I have a navigation form, a navigation subform, and then a subform. When I click the save button on the navigation subform, i need the data entered in two fields on the subform to save in a table. the subform is based on a query. This is what I currently have on the the button's onClick event:
Code:
Update data_tbl
Set [data_tbl].[Approved] = [Forms]![Navigationfrm]![Navigation subform].Form![Entry_subfrm].[Form]![CboAppd]
[data_tbl].[Comments] = [Forms]![Navigationfrm]![Navigation subform].Form![Entry_subfrm].[Form]![txtComments]
WHERE [data_tbl].[ID] = [Forms]![Navigationfrm]![Navigation subform].Form![Entry_subfrm].[Form]![ID]
I created a main form containing list boxes and a subform. What I want to do is to select items in the list boxes. Then, by clicking "Search" button, the data filtered by the selected items will be shown in the subform. But the subform cannot work and show the query results. I think I miss some VBA codes.
Complaint Database_William.zip
I'm trying to search a for string within a subform to find information stored on the mainform to which the particular subform belongs.
The problem is that the subform is generated from a query which uses a number from the main form to generate.
So the subform record is only generated when the correct mainform record associated with it is loaded.
Now to solve my problem I've made a new query that brings up ALL the results that could be generated by the main form and from that I can search to find my search term I'm after and read off the ID number to tie it back to the mainform.
But all of this is done manually, I want a way to do all this using VBA in a way that the user can't edit any records as they are doing it.