ok what follows is a kind of pseudocode of what I'm trying to do. It would really help if someone could tell me how to get a command button to do the following startin by the copying of fields into a new record in a table and then using that table to do the required actions. Any help would be greatly appreciated and yes it is urgent as the assignment is due in on Wednesday.
Copy fields into new record in table Transactions
If [Transaction.In or Out] = In Then
Hello all I am having a problem with my vb code. I am realatively new to vb and what I am trying to do is search through a table according to what is selected on my form and delete the values that equal the value selected int the table. What I have so far is this
Private Sub cmdDel_Click() Dim rsSource As DAO.Recordset Dim rsDestination As DAO.Recordset Dim theBug As String Dim SelItm As Control Set SelItm = Me.Users If Me.Schedule.ItemsSelected.Count = 0 Then Beep Exit Sub End If Set rsDestination = _ CurrentDb.OpenRecordset("User_Course_Room", _ dbOpenDynaset)
For Each Itm In Me.Schedule.ItemsSelected rsDestination.FindFirst "User_ID = " & Me.Users & "" And "Course_Room_ID = " & Me.Schedule.ItemData(Itm) & "" ### THIS LINE GIVES ME A ERROR 'rsDestination!Course_Room_ID = Me.Schedule.ItemData(Itm) 'rsDestination.FindFirst "Course_Room_ID = " & Me.Schedule.ItemData(Itm) & "" rsDestination.Edit rsDestination.Delete
Next Itm
Me.Sessions.Requery Me.Schedule.Requery
End Sub
In the line with the bold writing next to it I get an error. What I am trying to do is search for the record with the user id selected and the course room id seleceted and delete that record but I get an error. Any help would be highly appreciated.
I have Header form and subform subform in it with following fields
Subform fields are :
1. MQE_No : (Text field as it will be like MG-120, MG230), Duplicate OK) 2. RPO_No : (Number field – Double, Duplicate OK ) The are project no. 3. ForemanNo (Number field – Long Int – Duplicate OK)
Header form fields are ;
1.ForemanNo – Long Int – No Duplicate 2.Other fields…
The subform field have ForemanNo One-To-many relationship with Header form.
Following project MG-411 need to enter (assign) to foreman no. 641
Actual data to store in subform
MG-411 (MQE_NO) 5421654 (RPO_NO) 641 (FOREMANNO)
I started to enter data into subform selecting the ForemanNo at header so records to enter in specific foremans account. Everything is fine here.
What I need is:
I would like to select / add another Foreman into the header and start entering same above data for another Foreman. Bcz sometimes we need to do like this in case previous Foreman may go on to a week vacation. We can not stop project for a week. So need to assign project to other foreman.
When selected the Project (MG-411) thru a combo to assign it with other foreman, it should give a short msg to user that this project has already been assigned to “XYZ” forman. Need to assign again “ (Y/N box )
If Y then data entered otherwise cancelled.
To achieve this, I did the following but only 50% success. (Used Cmbo CboRPO2 select MQE_NO). Tried to satisfy both condition but no use.
Private Sub CboRPO_AfterUpdate() If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO = " & ENO) Then MsgBox "RPO ALREADY ASSIGNED TO SOMEONE / FOREMAN", vbOKCancel, "WARNING!!!" Me.Undo Exit Sub Else
Dim Msg, STYLE, TITLE, HELP, CTXT, Response, MYSTRING If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO <> " & ENO) Then Msg = "RPO ALREADY EXIST WITH OTHERFOREMAN, ASSIGN AGAIN TO ANOTHER ?" STYLE = vbYesNo + vbInformation + vbDefaultButton2 TITLE = "!! ATTENTION !!" HELP = "TEST FILE" CTXT = 1000 Response = MsgBox(Msg, STYLE, TITLE, HELP, CTXT)
Else Me.MQE_NO = Me.CboRPO.Column(0) Me.RPO_No = Me.CboRPO.Column(1) Me.WORKSHEET_NO = Me.CboRPO.Column(2) Me.WORKORDER_NO = Me.CboRPO.Column(3) Me.WORK_DESC = Me.CboRPO.Column(4) Me.PL = Me.CboRPO.Column(5) Me.PipeLineKM = Me.CboRPO.Column(6) Me.DiaMeter = Me.CboRPO.Column(7) Me.PipeLength = Me.CboRPO.Column(8) Me.PipeLineArea = Me.CboRPO.Column(9) Me.P = Me.CboRPO.Column(10) Me.RPO_AMOUNT = Me.CboRPO.Column(12) Me.INV_AMOUNT = Me.CboRPO.Column(13) Me.Status = "WIP" Me.StatusID = 2 Me.CboStatus.SetFocus End If End If End Sub
I tried with following code also
'Dim cdn As String 'cdn = "[MQE_NO] = '" & Nz(CboRPO, "") & "'" 'cdn = cdn & " And ENO = " & ENO
'If DCount("*", "T_RPO_Footer", cdn) > 0 Then 'MsgBox "THIS RPO ALREADY ASSIGNED TO THIS FOREMAN", vbOKOnly, "WARNING!!!" 'Me.Undo 'Exit Sub 'Else ‘do something ‘End if
i'm doing one of my school project, but i got a big problem with this.
from the photo, i've created a form like this. and i want to have a search system, searching data from table. so i've created 2 input box with 2 different searching methods.
both methods work the same. type in the Stuff ID and then click the search, datas will show like this.
but the problem is, i can't get the Search button working, whenever i press the search, it said that the Stuff ID can not be found. but i've that stuff id in my table!
Could anyone show me a solution of this. or is this method good? coz i'm not doing that with the VBA!
PS... sorry for chinese words. coz i'm from hong kong!
I have a bound form with a few fields. I would like to run a sub after some of these fields are modified, but not others. I would like it to run only once, after user finishes his work on the form.
When using the Unload event - the form on the form is not there anymore (or did I get something wrong?).
Is there a way to trap Data on form just before the form closes?
Hi I need code to open TeacherDetailFrm form Main form.
I have TeacherStudentSubFr*m on the Mainform, FirstNameTxt on the TeacherStudentSubFr*m, and TeacherDetailFrm. I need code to open the TeacherDetailFrm By double click on the FirstNameTxt.
I use this code but no data come and query dialog box come.
Private Sub FirstNameTxt_DblCli*ck(Cancel As Integer) Dim DocSubForm As String Dim DocText As String DocSubForm = "TeacherDetailFrm" 'DocText = "[Result]=" & "'" & Me![result] & "'" DoCmd.OpenForm DocSubForm, , , "[ FirstNameFld ]=forms![TeacherStudentSubFrm]![ FirstNameTxt]"
End Sub
I like to upload the file but no way in this forum
* This code works to search frmMember for criteria found in tblMember:
Private Sub cmdSearch_Click() If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then MsgBox "You must select a field to search." ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then MsgBox "You must enter a search string."
[Code] ....
* But this next code which I have copied and Pasted to frmEventInput, then renamed the frm and tbl, retrieves the same criteria as the above (tblMember) instead of the tblEventInfo...
Private Sub cmdSearch_Click() If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then MsgBox "You must select a field to search." ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then MsgBox "You must enter a search string."
hey can anyone please explain how to adjust the below code to open the specific order ID that has been double clicked? At the moment no matter which order is double clicked the same order is opened.
Private Sub OrderID_DblClick(Cancel As Integer) On Error GoTo Err_OrderID_DblClick
Dim stDocName As String stDocName = "Orders" DoCmd.OpenForm stDocName
I have a db that has only 1 table and 1 form (picker db). That is because we only use it to gather info from pickers and dont want them to be able to do anything but enter data. I have a master db that is used by Managers to import data from pickers db to master db to run reports.
Problem is we have someone that is an Access wannabe and sometimes messes with the code behind the form. The only way they can get behind the form is F11 key. I am afraid that if I take off that option then I cant get into the form myself...
Is there some other way that I can set this up in startup and have a way to get into the design view if I need to?
You can tell that I am a new user :) Thanks, Noreene
for example, if the [company name] field on the form is one thing, then the vba code should calculate 14 days after todays date, and output it in the [return date] field on the form but if the [company name] field is somthing else, the vba code should calculate 30 days after todays date and output it on the [return date] field
here is my code right now
Private Sub Return_Date BeforeUpdate(Cancel As Integer)
Select Case Form![Company Name] Case "CTI/Charlotte", "CTI/Chicago" Form![return date] = DateAdd("d", 14, [return date]) Case Else Form![return date] = DateAdd("d", 30, [return date]) End Select End Sub
im sure this is easy to do, but im new to vba. i have to be able to do this in vba.
On the form On load event I have code verifying if a condition is true or not. If the condition is true, it does some things, now I want also to rename a form if the condition is true. So, I have created a macro to do this. But if I open the DB for second, third.... time and the condition is still true the macro can't find the specifyed form and brings up an error message wich gives to user the possibility to Debug. This way he has access to the code , and I don't want this to hapen. Can I do this by using code and keeping the user away from code? Thanks, Attila
I have a post code field on my form. What I want to do is, if a user enters a wrong code ... i,.e., not Post code format it alerts the user of post-code not in use. Please if there is any example of a database of this functionality usage or a way of doing it will be much appreciated.
I live in UK and the post codes down here are like E12 5AH ....
Im novice to access db, started couple of days before on urgent requirement . the functionality part is , i need to create a form to interact with databse and get the necessary output .
Dim dbsExample As Database Dim rstExample As Recordset Dim fldExample As Field Dim str As String // Private Sub Command4_Click() Set dbsExample = OpenDatabase("db1.mdb") str = "type_ur_id.Text" rstExample = dbsExample.OpenRecordset("select * from xxx where ID=str") If (!rstExample.EOF) Then Text0.Text = rstExample(1) Recordset.Close End If End Sub // on running this form , error is INVALID USE OF PROPERTY >
rstExample(1) retreives the ID column value. .
could anyof u suggest where the problem could be,,and give me an alternate code..
Maybe this should be posted under VBA.... not sure. Havent seen answer that works yet. Its a form with an option group "PickWO" When you pick an option, different fieilds become visible-non visible. I created a field "PickWOvalue" to store the option value "1" or "2". That part works fine.(visible) The problem is when you reopen the form to view the records the option box value does not show or change the fields visible properties. And the on open cuauses an error. Anyone see the problem?
Private Sub Form_Open(Cancel As Integer) Me.PickWOvalue.Value = Me.PickWO.Value End Sub ````````````````````````````````````` Private Sub PickWO_AfterUpdate()
Call NotVisible
Select Case Me.PickWO
Case Is = 1 Me.ReInspectionDate.Visible = True Me.Price.Visible = False Me.WOPreview.Visible = True Me.PrtWO.Visible = True Me.WBMInvoice_.Visible = False
Case Is = 2 Me.ReInspectionDate.Visible = False Me.Price.Visible = True Me.cmdPreTag.Visible = True Me.cmdPrtTag.Visible = True Me.WBMInvoice_.Visible = True
I've got 2 sub forms that I intend on using via a toggle button from my primary form to access them. Is there anyone that can give me a heads up on creating vb code that when OnOpen it will automatically advance to a new record for data entry? I want the form to keep anyone from accidentally changing existing info in that regard. I'm still really new with VB.
i have a subform within a form that i want to create and automated procedure. the subform has a couple of combo boxes.
upon input into the subform i want to do something like if the value of combobox1 starts out with the letter B then the value of combobox3 equals combobox2 * 1/2 i am just having a hard time putting it in code.
as of now i have a on current event on the subform that reads
if me.[combobox1] like "b*" then me.[combobox3] = me.[combobox2]*.05 end if
i have a subform within a form that i want to create and automated procedure. the subform has a couple of combo boxes.
upon input into the subform i want to do something like if the value of combobox1 starts out with the letter B then the value of combobox3 equals combobox2 * 1/2 i am just having a hard time putting it in code.
as of now i have a on current event on the subform that reads
if me.[combobox1] like "b*" then me.[combobox3] = me.[combobox2]*.05 end if
i have a subform within a form that i want to create and automated procedure. the subform has a couple of combo boxes.
upon input into the subform i want to do something like if the value of combobox1 starts out with the letter B then the value of combobox3 equals combobox2 * 1/2 i am just having a hard time putting it in code.
as of now i have a on current event on the subform that reads
if me.[combobox1] like "b*" then me.[combobox3] = me.[combobox2]*.05 end if
I found some code that lets me Filter a Report from a Popup Form. It uses 6 combo boxes with each box filtering my VehicleRecords table to the record I am looking for. The code for this can be found here: http://support.microsoft.com/kb/q208529 I modified just a few lines so that it would point to my VehicleRecords table.
When I enter information into any one of the combo boxes I get a Run Time Error 13 - Type Mismatch, and then I am pointed to this section of code.
How do I load a form from another form? I would prefer to use VB (in fact, its probably the only way you can do it!). I have tried varius commands that would have worked in VB - but dont work in Access, like...
The above code does not work AT ALL. It gets an error at the Unload Me line, an error at the Load Form_tblClients line, and the action 'Show' does not even exist under Form_tblClients, so that wont work either!
I am aware of Switchborads, but i want to know how to load a form, and make it pop up in a new window from within another form.
The scenario: I have a command button on one form (cmdPatinient on frmData) . This command button opens a form (frmAssessment). On this form I have a check box option (yes/no). (chkAnswer) If the checkbox is ticked (to indicate data entry is complete) then I want the command button on the other form to be disabled.
So, do I put the code on the On Click event of the form with the checkbox and then how do I reference the other form?
I have a form in access database. On click of a button I want to perform update process for records in a table "abc" in the same database. I have written the following code in the onclick function,
Code: Dim rs As New ADODB.Recordset Dim rsCount As New ADODB.Recordset Dim CntVal As Integer Dim con As New ADODB.Connection con.Provider = "microsoft.jet.oledb.4.0" con.Mode = adModeShareExclusive con.Open ("C: est.mdb") rs.Open "Select * from abc" Do While Not rs.EOF rsCount.Open "select count(test) from abc where test = '" & rs.Fields("test") & "'" If Not rsCount.EOF Then CntVal = rsCount.Fields(0).Value Else CntVal = 0 End If rsCount.Close con.Execute "Update abc set testcount = '" & CntVal & "' where ID1 = '" & rs.Fields("ID1").value & "'" Loop
In the above code, I am not able to use the command, con.Open (App.Path & " est.mdb"). It gives "Object Required Error". And while opening the con object I get the error "You attempted to open a database that is already opened exclusively by user 'Admin' on machine ''. Try again when the database is available."
I have a form that is created in Access but uses some VB code. Here is my problem..... I have one form that the users do money transactions on. Then I have created a form that will allow them to Void a transaction as well as Refund a transaction. On the Void/Refund forms it will query the user for the Receipt No. once that information is pulled in, there is a command button that says "Void Ticket Sale" on the Refund form it says "Refund Ticket Sale". What I'm trying to do is, once the user enters the receipt no and hit that Void/Refund Ticket Sale button - if it has already been voided/refunded, a message box will come up and say "This Ticket has already been voided/refunded". If it has not, the "Void Ticket Sale" command will carry on. Here is a snippet of my code, I'm not sure what I'm doing wrong.
Private Sub cmdRefund_Click() On Error GoTo Err_cmdRefund_Click 'this is a AddRec button, caption was changed to read Void
Dim Answer As Integer Dim Result Dim sqlstmt As String
sqlstmt = "Select Count( * ) from tbl_transactions where PaymentType = Void And VoidRefundID = Me.TransNumID"
Answer = sqlstmt
If Answer > 0 Then MsgBox "This Receipt No. has already been voided." Exit Sub Else
Result = MsgBox("Are you sure you want to Refund Receipt No " + Str$(Me.TransNumID) + "?", vbYesNo, "Refund Receipt")
If Result = VbMsgBoxResult.vbNo Then DoCmd.Close Exit Sub End If End If
I currently have a parameter form named "Fm_Commander_Parameter" and a report named "Rpt_Bldgs_by_Commander". On the parameter form I have a unbound drop down. On the After Update have I this code that opens the report.
Private Sub Cmb_Commander_AfterUpdate() DoCmd.OpenReport "Rpt_Bldgs_by_Commander", acViewPreview DoCmd.Close acForm, "Fm_Commander_Parameter" End Sub
In the Query for the report for the Commander field I have the parameter as [Forms]![Fm_Commander_Parameter]![Cmb_Commander]..how to write code very well and am unsure this can be done, but what I would like to do is remove the 'After Update' on the form and on 'Open' of the report I would like to write a code to go to the form, allow them to select the commander, then go back to the report query with the selection. This way I could use the Parameter Form with other reports. Below is the code I've come up with that will be place in 'Open' on the report.
Private Sub Report_Open(Cancel As Integer) DoCmd.OpenForm "Fm_Commander_Parameter", acNormal (I don't know what to enter to keep the form open to enter the data. Without the below command it will run the report but the parameter form stays open.) DoCmd.Close acForm, "Fm_Commander_Parameter" End Sub
The access import wizard has a convenient form when a fixed-width file is to be imported. The form allows us to click the data to insert an arrow at a position where the next field should begin.
For my project, the wizard has some serious shortcomings, so I am trying to build my own import form. I can capture everything else needed to build an import routine, but I can't figure out, nor find examples of, allowing the user to place column breaks graphically.how to do this without using the wizard? Or rather, like in the wizard?
