Modules & VBA :: Stop Objects Moving When Others Grow
Jul 18, 2014
I was wondering if there's a way to set objects so that they don't get pushed down if an object above is set as Yes in the CanGrow property?can a report be split into columns?
I have been asked to replicate a form (previously written up on Word) which has two columns of information. As luck would have it all the fixed fields are on the Left hand side, so that part doesn't need (or want) to grow, However, the Right hand side has several sub-reports which can display 1-5 records each.
So if any of the sub reports show more than 1 record, they push down everything below it, but this includes the Left hand objects, creating gaps and often pushing the report onto 2 pages.
I want to keep the textbox small unless a user mouses over it, and they can then see all of the textbox contents. I've got code that will change it back to it's normal size after leaving the textbox as well.
Can I make the textbox "draw over" other nearby objects, so that it is in the foreground? Currently, the box grows, but it hidden by other nearby textboxes.
Edit: Well it looks like I just wasn't setting the Height large enough. I set it at .25 thinking it was in inches like it is in the property window, but I guess it's not. Seems to be working for now.
Edit 2: Not working afterall. It looks like it's working sometimes then the next time other objects will show through it. If I take a screenshot it looks fine in the screenshot no matter what it looks like on screen. Basically the check box and text box below the textbox I am making larger can be seen through said textbox.
I am building a form where I would like to group fields into 3 groups, A, B & C. I know I could put all groups on their own subforms but I'd like to see if there is a way to simplify it all on one form since there aren't much fields for each group.
I would also like to hide each group when the user clicks 'hide group A' and shift Group B & C up. Then move them back to their original position when 'Display Group A' is clicked.
Same for each group. If Group B - Hide is clicked, Group C is moved up.
I've got each group tagged appropriately and I can hide/display them all properly by a For Each Control loop. Now, how to I move them all up/down a specific amount in addition to that? I would think it would be just as easy but my objects are moving by using the Grid Y property
I am writing a database with a number of tables with forms attached to each table. I have placed navigation buttons on each form to move to first, last, next, previous. I have set up the code associated with the buttons as "public sub" so it is available for each form to use.
This was working OK except that when I used the NEXT button at the last record it would open a new blank record & keep opening a new blank record on each click.
To over overcome this I put in an If statement to check if it had moved to a new record. To do this I used the following:
If Me!NewRecord Then ..... etc
Now I get an error "Invalid use of Me key word" I suspect it is because I am using ME in a public sub
How do I overcome the problem of moving passed the last record and still have the coding available to all forms?
The Code is below.
Thanks for any help.
Public Sub Next_Record_Click() On Error GoTo Err_Next_Record_Click
DoCmd.GoToRecord , , acNext
If Me!NewRecord Then ' If new record move back to previous DoCmd.GoToRecord , , acNext ' Send message MsgBox "This is the last record", , "No More Records"
I have a form in which user will enter few values but I need users to enter the comment in relevant text box too only if they have any non zero value.
I have attached an example for reference....where red encircled portion is for values and blue is for relevant comments. If value is non zero then user should be enforced to enter the comment too......system should not allow user to move forward or backward until there is some comment.
I'm in the middle of writing a multi-language procedure, and in order to populate the label database I would like to cycle all forms (regardless of whether they're open or closed) and cycle all controls within the forms.
using VBA in Access and Excel. I have an Excel form that I am using to collect data as the front end and an Access database to house the data as the back end. In my Access database I want to create a module that will open the excel files and retrieve data from Active X combo boxes, option buttons and checkboxes that are on the Excel form. I have been searching for the VBA code to get data from these objects but have not been able to find any examples. How to reference these objects and get the values from the excel form
I have a simple date stamp that works great in a private sub within a form. (error handling removed for clarity)
Code:
Private Sub btnDateStamp_Click() ' UserInit is global variable Me!Notes.SetFocus Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & UserInit & _ " -" & vbCrLf & Me!Notes
[code]...
I am rewriting it as Module function that is Called from various forms, to save space. The function receives the parameters varFormName and varControlName. I wish to write the results of the function back to a memo field on the form.I am stumped at the get go by the need to refer to the Forms controls with a full reference instead of the Me command.
Code:
Function DateStamp(varFormName As String, varMemoName As String) 'varInitials, varFormName, varMemoName are global variables Forms!varFormName.Controls!varMemoName.SetFocus ' Error here 'Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & varInitials & _
[code]...
how to refer to the forms control's with their full reference, from within the Module's function, the rest will fall into place.
I want to export different objects (i.e. modules, reports,tables, queries...) from another ms access application. How can I list the objects so I can pick from them and then import them. I know ms access has a wizard and I am looking to do the same but a little different because I have an application that I want to filter my objects by a criteria.
I'd like to know if it is possible to copy an object (typically forms, reports and queries) by code from the acces file I'm woriking in to another one that is not opened.
I tried this but dont know how to stop the timer...I want to run the code that changes the Form color but want to be able to stop the Timer and reset it to start over..Will this timer work? Almost like an auction timer. But want to be able to reset the code at any stage...
Code: Public Sub ExcelTimer() Dim PauseTime, Start, Finish
I finally got tired of having 100 macros managing my different custom shortcut menus, and decided to figure out how to generate the shortcut menus programatically (because lets be honest, Macros are the devil.)
I found a good tutorial here : [URL] .....
and was able to create some basic shortcut menus like the following:
Code: Sub CreateCopyShortcutMenu() Dim cmbshortcutmenu As Office.CommandBar Set cmbshortcutmenu = CommandBars.Add("CopyShortcutMenu", _ msoBarPopup, False, False) 'ID 19 adds copy command cmbshortcutmenu.Controls.Add Type:=msoControlButton, Id:=19 End Sub
Nice and simple, now I have a copy command. But the problem is that I also need some custom commands. Most (possibly all?) of these would be function calls.
Code: Private Sub Form_Timer() Dim LocalTime, StartWindow, EndWindow As Date LocalTime = Format(TimeValue(Now()), "hh:mm") StartWindow = #11:46:00 AM# EndWindow = #11:48:00 AM# Dim Response
[Code] ....
The intent is to run automated back end maintenance activities while giving the user the option to delay it if they're in the middle of something.
My logic is as follows:
If it's between these two times, ask the user if the maintenance can run. If user says yes, run maintenance. If user says no shift the two times to the right and ask again in a few minutes.
As it is written, when I chose no I get an hourglass icon and if I try to do anything else Windows tells me Access isn't responding. I'm guessing that Access is hanging since I've initiated the Do Until and then stalled it by telling it to wait 3 minutes before it goes to the next step. I suspect that if I waited it out, when the new Startwindow hit I would be asked about running the maintenance again.
My issue is that I need the database to remain responsive to whatever the user needs to do while I wait to ask about running maintenance again. Therefore, I don't think Do Until is my answer - at least not the way I've implemented it.
The will ultimately be set on a 10 minute timer interval and start/end window will be 01:00 and 01:15 AM respectively. The times you see above are there for testing so I can see what happens.
This code puts pdf reports into a folder . I am prompted for a number via the query I run because I have more than one report the query asks me 3 times. Could this be modified so I only get prompted once for all the reports in the code
On Error GoTo Err_Handler Const FOLDER_EXISTS = 75 Const MESSAGE_TEXT1 = "No current invoice." Const MESSAGE_TEXT2 = "No folder set for storing PDF files." Dim strFullPath As String
I have a database that runs a macro/report every 2 hours, however our network has been up and down lately. This causes the macro to fail when trying to log into the system because the table that is storing your username is no longer in the database. When I check, all tables, linked tables, forms, reports, macros and modules are all gone. I find the only way to get them back is to close the database and reopen.
What I want is a way to reconnect the objects without closing and reopening.
I'm using an append query that needs to add the records from another database into a table. I can get it to get the data and add the records. There are other columns in the database that are not in the one the data is pulled from. If I would run the append query again, it would add the same records again at the end. How can I avoid this? I only need to add new records that are not yet in the database I want to pull the records to.
I need to stop already ran procedure or function with vba code . So I use END command ans everything stops.. in some cases that is useful, but some times need I to do something else after I use END command, but after END everything stops...
LIKE THIS :
SUB () END ME.CTL.SETFOCUS 'OR CALL function EXIT SUB
Here is the code I have to Update a table tblExternalIncomeSchedule
It works fine and updates the table for the first record.
How do I move to the next record StartYear 6 thru EndYear 10 and write that data to the same table without overwriting the data from the first record?
It should add 5 more records to the table
Yr 6 2000 Yr 7 2000 (since inflation = 0) Yr 8 2000 (since inflation = 0) Yr 9 2000 (since inflation = 0) Yr 10 2000 (since inflation = 0)
Code: Function FillExternalIncomeSchedule(nClientID As Long, nProposalId As Integer, nProposalYears As Integer, nRetireAge As Integer) Dim rst As Recordset Dim qdf As QueryDef Set qdf = CurrentDb.CreateQueryDef("", "DELETE * FROM [tblExternalIncomeSchedule] WHERE [ProposalID] = " & nProposalId) qdf.Execute
I have a Combo box which contains a set of numbers divided by "."
e.g.: 1.342567.24433
When looking for a certain code, I would like to use the number pad to enter the first few e.g. 1.34 to move to the Position. In Previous Versions of Access I had a function on the key
If KeyAscii = 44 Then DoCmd.CancelEvent 'stop the comma keypress being updated SendKeys ".", True 'put a full stop in the field End If
This worked fine, but in Access 7 and up, it turns off the number lock.
Is there an alternative to sendkeys to achieve the above or a way of directly turning the number lock on again?
I have two listbox (SearchResults5 has two columns) and list_asset_add (one column) both have Extended multi-selection active. I have this code that automatically moves the items between the two listbox (it runs after pushing a button):
Code: Sub CopiTo_Click() Dim Msg As String Dim i As Variant If SearchResults5.ListIndex = -1 Then Msg = "Nothing"
[Code] ....
This works quite well if I manually select the items that I want to move. Most of the times SearchResults5 has lots of elements so I have decided to create a "Select All" button to speed up the process, here the code:
Private Sub Command271_Click() Dim n As Integer With Me.SearchResults5 For n = 0 To .ListCount - 1 .Selected(n) = True Next n End With End Sub
When I use the "Select All" button and I try to move the items between the two listbox, the function does NOT work.
BUT if I manually select one or more items in the first listbox, then I clear the selection and finally I use the subroutine to move the items between the two listbox, then the it works well again. How to make it work properly.
i am running some code from vba to add a linked table and the do some lookups, then delete the linked tables.
When the linked table gets added, the navigation pane gets displayed. I have set it to do not display in the settings and it does not when it opens but when this code runs it opens and then stays open until the db is closed and reopened.
I am trying to use my db as a software and I really do not want this to show.
I have also noticed this in a database I have which has update queries and was fine in access 2000 but does what i described above when using in access 2010.
