Creating new table in Access 2003

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - duplicating VBA objects Hi, You can add controls at run time with code like the following. Private Sub UserForm_Initialize() Dim cntTBox As MSForms.TextBox Set cntTBox = Me.Controls.Add("Forms.textbox.1") With cntTBox .Name = "textbox11" .Top = textbox10.top + textbox10.Height + 3 .Left = textbox10.left .Text = "hello World" End With End Sub Tag: Creating new table in Access 2003
  - 2
    - Copying and Deleting the duplicate records in an excel sheet and Copying the same Deleted records into another sheet Do you want to do this with Visual Basic Express or with VBA in excel If you want to use macros then you would probably get better help in the VBA forum Tag: Creating new table in Access 2003
  - 3
    - Using C# Events over COm-Interop in VBA Check out this page: http://huntjason.spaces.live.com/blog/cns!9D2E96F2AA6AE85F!119.entry I wanted to do exactly what you wanted to do. This solution worked fine for me. Cheers Craig Tag: Creating new table in Access 2003
  - 4
    - Excel VBA - Best way to SQL Insert if SQL Update fails Maybe a better question...does VBA have try/catch blocks I can't find any information and I'm getting errors when trying to use these. Tag: Creating new table in Access 2003
  - 5
    - Using DCount function in a Report group footer DCount expects 3 String arguments: * 1st String argument specifies which Field from the Domain you want to count. * 2nd String argument specifies the Domain / Source that DCount will look at. * 3rd String argument (which is optional) specifies additional criteria if you only want to select records / rows in the Domain that satisfy the criteria. From what you posted, I think there are some confusions on the Field name. The expression you posted have both "Task ID" (1st argument) and "TaskID" (twice in the 3rd argument). Suggest you check the spelling of the Field names in your Table and try again ... Tag: Creating new table in Access 2003
  - 6
    - Error 380: Invalid property value:Help Me Please " Row = Val(csupplierno) + 6" is this the line that VBA highlighted when u clicked on the 'debug' button is 'csupplierno' a textbox because i cant see any declaration for it i know it may sound stupid, but can u rename 'Row' to something else say 'row_no', im just worry that 'Row' might be some system variables. thanks. Tag: Creating new table in Access 2003
  - 7
    - Windows Error Reporting - handling unanticipated errors Jason or others will probably comment on the "legal" aspects of the question, but I'll throw in some comments on technical aspects, hoping that they make some sense to you. If you follow Microsoft's recommendations for handling crashes, an application is not expected to catch crashes itself; instead, it is supposed to delegate crash reporting to the OS. Also, the app can register itself for automatic restarts so that it can "restore" itself into some safe state before the crash. From a purely technical point of view, this makes a lot of sense since apps tend to become instable after a crash, so chances are you'll make things worse by trying to continue, instead of letting the app crash. From a usability point of view, however, other approaches might actually be preferrable sometimes, such as the one you're describing. Whether or not your approach is at least somewhat safe, depends on the nature of crashes, and the impact which each of the files you're processing has on the global state of the application. Let's assume a scenario where you read a file, parse it, build a per-file data structure in memory, and then produce some output on the disk using the parsed data structure. Only very few "global" data structures are changed in the process, and their integrity can be verified in case some unexpected crash occurs. In such a scenario, trying to catch, say, an access violation may actually be viable since your crash handler could remove the per-file data from memory or invalidate them, and check the integrity of the rest of your data before allowing the user to proceed. We had a similar scenario; in our app, we automatically roll back to a previous state of the internal model when we detect a crash. This reduces the probability of data corruption when allowing the user to proceed after the crash (but doesn't eliminate it completely, of course). Claus Tag: Creating new table in Access 2003
  - 8
    - Is there a better way to debug. As this is a VBA forum, I assume you mean VBA and not VB.net or VB.6, etc. The MS Visual Basic Editor (available from almost any MS Office App) has some inbuilt debugging functionality... found in the Debug menu. The simplest of which is to use "step into" or "run to cursor" - while in this debug mode, you can hover your pointer over any variable or many functions and properties, and the current value of that will be displayed. The "immediate window" might also be of use, depending upon what you're trying to debug. Tag: Creating new table in Access 2003
  - 9
    - Display a value in a looping status It is ok now. I found out the problem. I set the breakpoint at commSerial.Output = stringValue , Srx_Buffer = commSerial.Input and sRx_Msg = Conversion(sRx_Buffer) . To check where my problem lies and found out that my previous data was not cleared. I just want to know how I can clear the buffer values in commSerial.Output or commSerial.Input Thank you EDIT: Sorry if I made another account here. My main account is giving me problems yesterday, thus making me create another account to look for answers here. Tag: Creating new table in Access 2003
  - 10
    - Excel Macro Help needed for the code in excel Marro... I have a backend excel file in LAN server, which acts as main database... and n numbers of fronend excel file with n numbers of users...... I have written a code where data will be submitted from frontend to backend in a click of a button.... I want to check if the backend excel file is open somewhere/anywhere before executing the code further.... Tag: Creating new table in Access 2003
  - 11
    - Subscript out of range with a simple sql :-( [newbie to VBA] The worksheet you are writing the recordset to "Tabelle1". Are you sure it exists and have spelt the name correctly It would help if you could highlight the specific line of code where the error message occurs. Tag: Creating new table in Access 2003
  - 12
    - VB code in Access forms Move the thread from Visual Basic General forum. There are more exports in Visual Basic for Applications development. I believe you can get the satisfying answers in this forum. Thanks again for your question. Tag: Creating new table in Access 2003
  - 13
    - Run-time error '13': Type Mismatch Hi, You do not say exactly where the were the error occurs. But first guess would be that txtRow.text does not contain a valid value when used in the UpdateData routine. Tag: Creating new table in Access 2003
  - 14
    - ReportFault() vs. WerReportCreate/WerReportSubmit() on Vista Hi Claus, We can help you here with your WERAPI questions. Off hand, I think you would be interested in the restart and recovery APIs in addition to the WER Data Collection APIs. I envision that the new rich WERAPIs in Windows Vista will enable you to do everything you need. I'll talk to the Developer Lead of the APIs to see if there are any samples or guidance available and one of us will get back to you. In the meanwhile, you can explore these APIs at http://msdn2.microsoft.com/en-us/library/ms681656.aspx (Core WERAPIs) and http://msdn2.microsoft.com/en-us/library/aa373342.aspx (Application Restart and Recovery APIs). Kind Regards, -Jason Tag: Creating new table in Access 2003
  - 15
    - Access Data Project, form bound to Sproc, need to update, etc It's been a week now, with no response to your post. I lurk on this forum a fair bit, and it seems that sql and Access queries tend to go unanswered here. I suggest you repost in the transact-sql forum (link below) - certainly there will be more people who can help with the sql and stored procedures side of things. If it is a VBA solution you're seeking, then I suggest that you mention in your post that you tried here first and no-one could help - then they might be a bit more forgiving of a slightly off-topic post. http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=85&SiteID=1 Tag: Creating new table in Access 2003
  - 16
    - Conditional Formatting Problem/Bug? I don't think you're doing anything wrong. I was easily able to replicate the same (on Excel 2000, so assuming you're using something more recent this is not a new phenomenon). The VBA code assigns the conditional format formula and formats, but it doesn't seem to actually apply them until you interactively apply the conditional format. It appears to be related to the array formula in the conditional format. If you replace it with a non-array formula then it works fine. For example, the following non-array equivalent to your formula works OK: Formula1:="=AND(A1<>"""",COUNTIF($A$1 : $A$5,A1)>1)" That doesn't solve the underlying problem of how to apply array formulae in conditional formatting via VBA. I tried a few things to try to make it refresh or redraw, without success. Even applying a second conditional format applies the second format but doesn't "activate" the first. I think it's likely that this is a bug. Tag: Creating new table in Access 2003
- Next
  - 1
    - Justifying text in message box Hello Une VBA doesnot support to justify text. but you can make it with an API function, MemCopy() MemCopy() copies an address to the another address. I've just written some codes for you. '// API declaration must be in the modules Declare Sub MemCopy Lib "kernel32" Alias "RtlMoveMemory" (Dest As Any, Src As Any, ByVal cb&) Sub JustifyString() Const MAXLEN = 30 Dim arrAnimal(2) As String Dim arrNumber(2) As Long Dim strAnimal As String Dim bytAnimal(MAXLEN) As Byte Dim i As Long, j As Long, lb As Long arrAnimal(0) = "The number of lions =" arrAnimal(1) = "The number of tigers =" arrAnimal(2) = "The number of monkeys =" arrNumber(0) = 23 arrNumber(1) = 2 arrNumber(2) = 150 For j = 0 To 2 strAnimal = arrAnimal(j) lb = Len(strAnimal) MemCopy bytAnimal(0), ByVal strAnimal, lb For i = lb To UBound(bytAnimal) bytAnimal(i) = 32 Next strAnimal = String(UBound(bytAnimal) - LBound(bytAnimal) + 1, " ") MemCopy ByVal strAnimal, bytAnimal(0), MAXLEN strAnimal = strAnimal & Format(arrNumber(j), "@@@") Debug.Print strAnimal Next End Sub Tag: Creating new table in Access 2003
  - 2
    - Problem with events Anyone Either this is an obscure bug of some kind, or I have done something obviously wrong. Either way, someone must have the expertise enough to tell me either way Tag: Creating new table in Access 2003
  - 3
    - vlookup Hi, How about some like this Dim rngLookupValue As Range Dim rngtable As Range Dim lngColIndex As Long Dim blnRangeLookup As Boolean Set rngLookupValue = Range("O2") Set rngtable = Workbooks("Consolidated list of supplier.xls").Worksheets("Sheet3").Range("$A$5:$F$218") lngColIndex = 6 blnRangeLookup = False vntResult = Application.WorksheetFunction.VLookup(rngLookupValue, rngtable, lngColIndex, blnRangeLookup) Tag: Creating new table in Access 2003
  - 4
    - CRM 3.0 Error: Subreport could not be shown Per our support engineer: Can you please complete/answer the below items to help me get a better idea of whats going on in your environment. I see in your e-mail that you tried this from another machine and the error still happened. So this tells us that its not computer settings causing the issue. Here are some additional things to check. 1. Can another user try to run the report Preferably a user that has the System Administrator rights. This will tell us if its specific to certain security roles or if its something wrong with the report. 2. What report are you trying to run 3. Is this a custom report or a standard CRM report 4. Create Platform Trace. a.) Run the attached tool on the CRM Server. b.) Check Enable Platform Tracing, Click OK. c.) Recreate the error. d.) Uncheck Enable Platform Tracing, Click OK. e.) Zip and Attach the created trace log folder to the case. It will be created on the C:\ drive and named similar to CrmDiagPlatformTrace. 5. What are you doing when the error appears 6. If this is a custom report can you send the report .rdl file to [ budsup@microsoft.com so that we can] attach it to this case. I would like to try to recreate this issue in my environment. 7. When creating custom reports you want to make sure the filtered views are referenced and not the actual tables. Referencing tables in your statements will cause security issues. 8. Are you able to run the Report from http://SRSServer/reports -brenda (ISV Buddy Team) Tag: Creating new table in Access 2003
  - 5
    - sending emails from MS Word with variable subject line Hi, Something along the lines of the following. Although this may grab too much text depending on your document setup. .Subject = Activedocument.Paragraphs(1).Range.Text Tag: Creating new table in Access 2003
  - 6
    - Easy msgbox Query If Sheet1.Range("C11").Value < 0 OR Sheet1.Range("C12").Value < 0 Then Adamus Tag: Creating new table in Access 2003
  - 7
    - ThreadHijacker and WER report Hi Dhon, Threadhijacker can inject crashes at random IP addresses and it is possible that there are no modules loaded at that address where the crash was injected. It is not guranteed that Threadhijacker will always introduce a crash so that there is a valid module at that address. Thanks Tag: Creating new table in Access 2003
  - 8
    - How to turn OFF Simple File Sharing via Policy Editor in Server 2003 Hi, Here's some info from our support engineer that might help: The policy that you are looking for is Network access: Sharing and security model for local accounts Path to the policy: Computer Configuration> Windows Settings> Security Settings> Local Policies> Security options Guest Only �C Local users authenticate as guest - simple file sharing is enabled Classic - Local users authenticate as themselves - simple file sharing is disabled I hope this is what you were looking for! -brenda (ISV Buddy Team) Tag: Creating new table in Access 2003
  - 9
    - update cell value using loop Hi, You may want to try this in VBA ( Visual Basic for Applications ) in Excel. This forum is for VB.Net questions only. See.>> http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=478161&SiteID=1 Try this VBA forum.>> http://www.programmersheaven.com/mb/ vba/ Board.aspx S=B20000 Regards, S_DS Tag: Creating new table in Access 2003
  - 10
    - Automating PowerPoint Hello, Does anyone have any ideas on this It must be possible to do. thanks, Jenise Tag: Creating new table in Access 2003
  - 11
    - Using Autofilter in VBA with a range of criteria I just thought of something that I should add to clarify my request for help here. I'll give the snippet of code to help: week = InputBox("Enter the desired Week Number.", "Date Query") Selection.AutoFilter Selection.AutoFilter 8, "REBALE" Selection.AutoFilter Field:=9, Criteria1:="28" Selection.AutoFilter 13, week This currently works for sorting my data by the Week Number column. However, I want to have two seperate input boxes that give me the capability to use 2 different string criteria while using > or < operators. I know that you could simply put the week numbers in the following: .AutoFilter (Field, Criteria1:=">=3", xlAnd, Criteria2:="<=6") but I want it to be interactive, if possible. I hope that's a bit more clear than mud! Thanks. JS Tag: Creating new table in Access 2003
  - 12
    - Need Urgent Help from the good people Hi mate, I think you will find more help in the excellent VBA forum, there are a number of good people there who I believe can help with this problem. http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=74&SiteID=1 Tag: Creating new table in Access 2003
  - 13
    - Excel Checkbox - Multiple controls-only one may have value of true Hi, This routine placed in a code module. Public Sub SingleCheckbox(OnSheet As Worksheet, ThisControl As Object) ' ' Uncheck any other checkboxes with the ' Dim oleTemp As OLEObject If ThisControl.Object.Value Then ' only deal with control if ticked For Each oleTemp In OnSheet.OLEObjects ' loop through all controls If TypeName(oleTemp.Object.Type) = "CheckBox" Then ' only deal with checkboxes If oleTemp.Object.GroupName = ThisControl.Object.GroupName Then ' make sure control is in same Group If oleTemp.Name <> ThisControl.Name Then ' do not deal with control just ticked oleTemp.Object.Value = False End If End If End If Next End If End Sub And this code in the click event of each control. Modify the 2nd argument to be the correct control. Private Sub CheckBox1_Click() SingleCheckbox ActiveSheet, CheckBox1 End Sub Tag: Creating new table in Access 2003
  - 14
    - Reversing a lists order in excel for my macro Hi Will the list / selection always be the same size and cell locations If so a simple for next loop can do the job eg for x=1 to 5 worksheets("Sheet1").cells(6-x,2).value =worksheets("Sheet1").cells(x,1).value next Tag: Creating new table in Access 2003
  - 15
    - .mdmp files not being created David, on Vista, minidumps are transmitted only upon explicit demand from the server, at least that is how I understand it. Jason Hardester's post at http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1426737&SiteID=1 explains some of the rationale behind the server's decisions. You can force the system to always generate minidumps by enabling queuing mode: Set HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Windows Error Reporting\ForceQueue (DWORD) to 1. Crash report data are stored in c:\Users\someusername\AppData\Local\temp and C:\ProgramData\Microsoft\Windows\WER\ReportQueue . Hope this helps, Claus http://www.clausbrod.de/Blog Tag: Creating new table in Access 2003
  - 16
    - Bypassing query sitting behind an Access 2003 report to print report layout Is there a way to print the layout of a report in Access 2003 without the query for the data opening I'd like to do this using VBA and just print the report layouts. Most of the reports I am using use queries that require parameters which causes numerous dialogs requesting the different parameters to pop up before the report actually prints. Can these queries get ignored/bypassed somehow I have too many reports to print and it could take forever having to run through the params. Thanks in advance Tag: Creating new table in Access 2003

Tantani

Dear,

I would like to pick up few rows from existing table in Access 2003 and put them into new table via VBA.

Any solution
Thank you in advance!

Top

Re: Creating new table in Access 2003

ADG

Suggest that you use the Docmd.RunSQL command the below example takes data for one day from on table and puts it in another, Warnings are turned off so that you do not have to press OK to overwrite the table if it already exists.

Dim strSQL As String

strSQL = "SELECT Production.* INTO [tbl Temp] " & _
"FROM Production " & _
"WHERE (((Production.Date)=#5/15/2007#));"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

Top

Re: Creating new table in Access 2003

Tantani

Dear ADG, Hi,

You made a great help. Thank you indeed. In case of any questions, I will come back to you. (New Post!)

Top