Need Help:
Table has several fields, two that pertain to this question are:
[Time] and [AM/PM] User inputs both, values such as
11:00 am
11:30 am
12:10 pm
I have to change the pm to am if the time is between 12:00 and 12:59.
I have an update query that works but I want to place code in a report that does the same thing without using
DoCmd.Open Query
I would rather use an If statement.
Please help.
I placed this question in Microsoft and reply did not answer the question. Rather told me ampm would automatically show up.
I knew that, but want to change the automatic pm for sorting purposes.
The coding below works fine. It presents a form with a list box of counties. Allows the user to select ALL, one or several counties and returns a query containing the clients from those counties.
The fields showing in the query are First, Last, Add1, FLAGToMap, City, Prov and Sector_Name.
I want to add in there a choice to select only the records that have are TRUE (-1) in the FLAGToMap field - just like the ALL button, this would be an ALL Selected Button let's say.
I would not know where to begin as I copied and adapted the coding below from a sample database and don't understand - at all - how the query is generated. The only coding in the form is the one below.
Private Sub cmdOpenQuery_Click() On Error GoTo Err_cmdOpenQuery_Click Dim MyDB As DAO.Database Dim qdef As DAO.QueryDef Dim i As Integer Dim strSQL As String
I have 2 tables, (will just call them tbl1 and tbl2 for this example) each with a column AppNumber. The AppNumber may appear in both tables or individually in either one.
I want to be able to show, or flag, on a continuous form which Appnumber's appear on tbl1, tbl2 and on both. This is so the user can click a button and bring up another form with more data relevant to the AppNumber.
I have written a union query to show all results from both tbl1 and tbl2 which contains 4 fields AppNumber, DateApp, Surname, Forename but I can't tell from the query results what AppNumber's appear on each table. Here's what I would like to show on the continuous form:
AppNumber DateApp Surname Forename tbl1 tbl2 Both 12345 11/12/2012 Simpson Homer Yes No No 56565 01/04/2013 Stallone Sly No Yes No 89788 03/03/2012 Rambo John Yes Yes Yes 89745
I am trying to hardcode in the field name. This is what I want to hardcode "TEXT(ROW(A17),"-0")" but the parenthesis and quotation marks are causing a syntax error. It appears like this
I have a Form that currently contains a checkbox that has the following OnClick event... SELECT tblPlayerRegister.Surname,tblPlayerRegister.[Club],tblPlayerRegister.Age FROM tblPlayerRegister WHERE (((tblPlayerRegister.Age) < 11) And ((tblPlayerRegister.Club) = "Beaconsfield")) ORDER BY tblPlayerRegister.Surname;
The aim of this is to select the Surname, Club & Age from the Table PlayerRegister where the Age is less than 11 and the Club is 'Beaconsfield' and then Sort in Ascending Order by Surname. I get an error when I write this code and I don't know what I am doing wrong. I am not very knowledgable with Coding but I have given it a go. There will eventually be many checkboxes with different criteria and I don't want to do a query for every one. Could somebody please advise what I have done wrong? Thank-you for any assistance given.
Okay here is my code, I had thread open awhile back. In regards of exporting main form and subform to word. Here is the code, I am still having problem with it. I can't seen to take export nothing or if I switch around dbs.close and rs.close, I end up exporting whole subform (meaning all the selected text from all the records to on one word doc. I don't know what I am missing, and it has gone beyond the limits and I have turn my brain upside down, but still no answer :( It has to be something with coding. Because, I know my subform is working fine, if I print out a report on access. Help me out plz.
TIA
Private Sub Command4_Click()
'Declare the follwing Dim dbs As Database Dim objDocs As Object Dim objWord As Word.Application Dim prps As Object Dim rst As Recordset Dim blnSaveNameFail As Boolean Dim BorrowerID As String Dim InformationID As Long Dim intcount As Integer
'Set word as an application and make it invisible Set objWord = CreateObject("Word.Application") objWord.Visible = True 'True is visible
'path and name of the template your are using. objWord.Documents.Add ("C:Temp ermsheet3.dot")
'This is for the bookmark that you created in the template objWord.ActiveDocument.Bookmarks("bmCusadd").Select
'This is the field in access that containts the data that has to be entered at the 'bookmark objWord.Selection.Text = Forms![menu]![txtCusDetails]
If intcount < 1 Then MsgBox "No detail items for invoice; canceling" Exit Sub End If Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tmakInvoice", dbOpenDynaset) With rst .MoveFirst Do While Not .EOF BorrowerID = Nz(![Borrower ID]) Debug.Print "[Borrower ID]:" & BorrowerID With objWord.Selection .TypeText Text:=BorrowerID .MoveDown Unit:=wdLine, Count:=2 '.MoveRight Unit:=wdCell End With .MoveNext Loop .Close End With
With objWord.Selection .GoTo what:=wdGoToTable, which:=wdGoToFirst, Count:=3, Name:="" .MoveDown Unit:=wdLine, Count:=1 End With dbs.Close objWord.ActiveDocument.Fields.Update
'Word (or the document that you created with the template, will now open) objWord.Visible = True
Set objWord = Nothing 'rst.Close Exit Sub 'End With
Hey, i need help with a code. It shouldn't be too hard but i don't know where to start as i am unskilled on this program.
I am looking for an input box saying 'enter password here' to pop-up. If it is the same as say 'customer' then i want it to let the user into another form.
I just created a database for work, its got two fields on the form, item and Price.
What i want it to do is when I enter the amount of item, I want it to automatically work out (price = £3.99)the price per item and display it on the Price text box on access.
I done VB but cant remember much, i Know u might have to do a IF statement.
trying to make it so that when you open the report, it'll pop a dialog box for users to choose the dates (from date to to date. ie. 04/15/05 to 07/17/05). When I click on the report, dialog shows up fine but it finds every record... can someone please help me with this.
here's the coding for the report
Option Compare Database Option Explicit
Public Sub cmdCancel_Click() ' Method in all forms to allow clean close DoCmd.Close acForm, Me.Name End Sub
Private Sub cmdPrint_Click() ' Validate the dates If Not IsDate(Me.txtFromDate) Then Me.txtFromDate.SetFocus MsgBox "You must enter a valid From date.", vbCritical, gstrAppTitle Exit Sub End If If Not IsDate(Me.txtToDate) Then Me.txtToDate.SetFocus MsgBox "You must enter a valid To date.", vbCritical, gstrAppTitle Exit Sub End If If Me.txtFromDate > Me.txtToDate Then Me.txtFromDate.SetFocus MsgBox "The From date must be less than or equal to the To date.", _ vbCritical, gstrAppTitle Exit Sub End If ' Hide me so the calling report can run Me.Visible = False
End Sub
Private Sub cmdToDateCal_Click() Dim varReturn As Variant ' Clicked the calendar icon asking for graphical help ' Put the focus on the control to be updated Me.txtToDate.SetFocus ' Call the get a date function - date only varReturn = GetDate(Me.txtToDate, True) End Sub
Private Sub Form_Open(Cancel As Integer) ' Set up the form caption Me.Caption = Me.OpenArgs ' Set up the label Me.lblTitle.Caption = "Select Dates for " & Me.OpenArgs End Sub
and coding for the form i designed.
Option Compare Database Option Explicit
Public Sub cmdCancel_Click() ' Method in all forms to allow clean close DoCmd.Close acForm, Me.Name End Sub
Private Sub cmdFromDateCal_Click() Dim varReturn As Variant ' Clicked the calendar icon asking for graphical help ' Put the focus on the control to be updated Me.txtFromDate.SetFocus ' Call the get a date function - date only varReturn = GetDate(Me.txtFromDate, True) End Sub
Private Sub cmdPrint_Click() ' Validate the dates If Not IsDate(Me.txtFromDate) Then Me.txtFromDate.SetFocus MsgBox "You must enter a valid From date.", vbCritical, gstrAppTitle Exit Sub End If If Not IsDate(Me.txtToDate) Then Me.txtToDate.SetFocus MsgBox "You must enter a valid To date.", vbCritical, gstrAppTitle Exit Sub End If If Me.txtFromDate > Me.txtToDate Then Me.txtFromDate.SetFocus MsgBox "The From date must be less than or equal to the To date.", _ vbCritical, gstrAppTitle Exit Sub End If ' Hide me so the calling report can run Me.Visible = False
End Sub
Private Sub Form_Open(Cancel As Integer) ' Set up the form caption Me.Caption = Me.OpenArgs ' Set up the label Me.lblTitle.Caption = "Select Dates for " & Me.OpenArgs End Sub
codings from Microsoft "Building Access Applications" viescas
Yesterday I was advised by 'Smart' on placing a certain code, as follows:
In the after update trigger of the combo box you can do the following
If YourCombobox = "-" Then yourtextfield.ForeColor = vbRed Else: yourtextfield.ForeColor = 0
End If __________________
Please forgive my ignorance but where do I put this code... on the line for 'after update' or in the code builder. If it is the latter, how do I begin the code because it has Private Sub and End Sub. I am obviously no expert but I'm just trying to plod along and learn as I go. Your help is gratefully accepted.
I want to track the shipping costs for my products (no commercial value) which I mail world wide. My fields are:
Product Name Product Weight Quantity Mailing Zone Unit mailing cost Total mailing cost
On my input form Product Name and Product Weight are input using a combo box. Quantity is entered manually.
I have a table listing my products and the unit cost (best price) to send them to various world mailing zones.
I want to create a combo box on the Mailing Zone field and code it so that when I select a Mail Zone, the Unit mailing cost field is updated with the correct value for the product.
I have a quick question. I have a main form with a label and a button on it. When the button is pressed a secondary form pops up. The second form has a button on it also. I am trying to code the button on the second from so that when it is pressed the label changes its value on the first form. In the past it was quite easy as there was no second form so in the Onclick VB coding of the button on the first form had something like:
With Me.mainHeading .caption = "Recontacts" End With
But, with the secondary form, the "Me." does not work. Is there a way i can replace the "Me." with some sort of link back to the first form?
I have a combo box (cmboType) and the options are (let's say) Cat1, Cat3, Dog4, Mouse5 (but there are about 30). If I select cat (any type)I then (manually) tick a Yes/No box.(chkYes)
How can I make the box picked ticked automatically whenever I select a cat in the combo box.
I am trying (on the afterupdate event of the combo box) something like
If cmboType is like "Ca*" then chkYes = true
Obviously this doen't work - can anyone help me with the correct code?
I have the following code that will import OLE objects into access, and it works quite well...but, how do I get it to check to see if a reocrd already exists and not to duplicate a reocrd? I am not a programmer, I have a couple of ideals but I don't know where to start. Any help would be appreciated.
If I were to put a new file into the folder, I would like the code to only import the new file and all files again. Which currently it will just create new reocrds for the existing document. I hope I have explained this... Any help would be appreciated. Thanks. vb Code: Original - vb Code Private Sub cmdLoadOLE_Click() Dim MyFolder As String Dim MyExt As String Dim MyPath As String Dim MyFile As String Dim strCriteria As String MyFolder = Me!SearchFolder ' Get the search path. MyPath = MyFolder & "" & "*." & [SearchExtension] ' Get the first file in the path containing the file extension. MyFile = Dir(MyPath, vbNormal) Do While Len(MyFile) <> 0 [OLEpath] = MyFolder & "" & MyFile [OLEFile].Class = [OLEClass] [OLEFile].OLETypeAllowed = acOLEEmbedded [OLEFile].SourceDoc = [OLEpath] [OLEFile].Action = acOLECreateEmbed ' Check for next OLE file in the folder. MyFile = Dir DoCmd.RunCommand acCmdRecordsGoToNew LoopEnd Sub Private Sub cmdLoadOLE_Click() Dim MyFolder As String Dim MyExt As String Dim MyPath As String Dim MyFile As String Dim strCriteria As String MyFolder = Me!SearchFolder ' Get the search path. MyPath = MyFolder & "" & "*." & [SearchExtension] ' Get the first file in the path containing the file extension. MyFile = Dir(MyPath, vbNormal) Do While Len(MyFile) <> 0 [OLEpath] = MyFolder & "" & MyFile [OLEFile].Class = [OLEClass] [OLEFile].OLETypeAllowed = acOLEEmbedded [OLEFile].SourceDoc = [OLEpath] [OLEFile].Action = acOLECreateEmbed ' Check for next OLE file in the folder. MyFile = Dir DoCmd.RunCommand acCmdRecordsGoToNew Loop End Sub
Yesterday I posted a question and rec'd good advice but as usual I don't think I explained myself very well. As the attached picture of my Form shows, I have a checkbox for each Age Group under each Team. I originally had a query that would come up for each Team's particular Age Group and then the required data copuld be input. This leads to too many querys clogging things up. I tried to copy the SQL code of one of the query's to the On_Click event of the checkbox but as I have been informed SQL is different to VB. All I would like to know is how to convert the SQL to VB coding so that I can continue to do the coding for each Checkbox. The SQL code for the first checkbox is:
SELECT tblPlayerRegister.Surname, tblPlayerRegister.[First Name], tblPlayerRegister.Age, tblPlayerRegister.[D'n], tblPlayerRegister.G1, tblPlayerRegister.SP, tblPlayerRegister.Age2, tblPlayerRegister.G1A FROM tblPlayerRegister WHERE (((tblPlayerRegister.Age)<11) AND ((tblPlayerRegister.Club)="Beaconsfield")) ORDER BY tblPlayerRegister.Surname, tblPlayerRegister.[First Name];
If there is an easier way, by all means let me know. Any assistance would be greatfully appreciated.
hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.
Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.
What I am trying to do is create 3 (or more) parameters for a query from a single table. Lets use this for example:
Table Name= "tblExample" Field Name "A" with Perameter "1" Field Name "B" with Perameter "2" Field Name "C" with Perameter "3" (All from Table= "tblExample")
My intentions are that when the query is run, the user is asked to include 3 subjects (1 subject per perameter; 3 perameters total that pop up). But I am having trouble making it so that if a random person using this query doesn't know or can't remember 1 or 2 of the subjects they are looking for, the query will just (in a sense) ignore the two blank parameters the user has left alone, and just clicked the "ok" button without entering anything, and use the 1 parameter that it was given a subject for, to filter/query out a result.
If you beleive you will have trouble explaining this to me, I'll use this as an example:
Table Name: "tblExample" Field Name: "A" with Parameter "1" Field Name: "B" with Parameter "2" Field Name: "C" with Parameter "3" (All from Table: "tblExample")
The user uses the query and is asked by the first parameter for input. The user isn't sure, and clicks ok without entering anything, and parameter 2 pops up. The user then enters a subject of which he/she knows to look for and clicks "ok". Then the final parameter asks the user for input, and the user again doesn't know, or can't remember so he/she just presses the "ok" button. What would be the coding for this kind of parameter that if nothing is entered, the parameter is ignored?
In desperate need of assistance. Thanks in advnace
&HFHEFEF, and &HFFEFFE and the like relating to certain colors: where is this magical "chart/table" of equivalents? Need soon..thanks..teball20 (teddy)
I am a newbie in ms access. I am ask to do some coding to help me with massive duplicate datas in my database in access. But i do not know where to start from? Im very confuse. As i do not know if ms access itsself can be program using codes? or am i suppose to use SQL? or VB? or VBA?
I am setting up a database to help me prepare codes for employees timesheets in order to upload them into our payroll software. The table structure below is just my preliminary thoughts and current ideas and I guess I'm looking for ideas on how to work with my codes.
EMPLOYEE TABLE EmpID (PK) - Employee ID # [Autonumber] Surname - Employee's surname [Text] Firstname - Employee's first name [Text]
ATTENDANCE TABLE ShiftID (PK) - Shift ID# [Autonumber] EmpID (SK) - Employee ID# [Foreign Key] Date - Date of shift [Date/Time] Start - Start time of shift [Time] Finish - Finish time of shift [Time] CostCtr - Cost centre being billed for shift. [Integer]
When employees work they are entitled to the following: * Ordinary hours (code 001) for all hours worked. * 10% penalty (code 006) for all hours worked when shift finishes after 18:00 * 12% penalty (code 007) for all hours when shift crosses midnight * 50% penalty (code 008) for hours worked on a saturday * 100% penalty (code 009) for hours worked on a sunday
The following shows data that in my Attendance table for an employee who worked shifts on the 16th (Mon), 17th (Tue), 20th (Fri), and 22nd (Sun). ShiftIDEmpIDDateStartFinishCostCtr 18443416/10/0610:0019:00 28443417/10/0610:0019:003002 38443420/10/0622:0006:003001 48443422/10/0614:0022:00 From the above data I believe I will need to make another table that contains the entitlement codes generate from each shift.
For the first shift on Monday 16/10/06 I need to collect the following codes for the total calculation: CodeHoursCostCtr 0019.00 0069.00
For the second shift on Tuesday 17/10/06 I need to collect the following codes for the total calculation: CodeHoursCostCtr 0019.003002 0069.003002
For the third shift on Friday 20/10/06 I need to collect the following codes for the total calculation: CodeHoursCostCtr 0018.003001 'Ordinary hours worked 0078.003001 '12% penalty as shift crossed midnight hour 0086.003001 'Only worked 6 actual hours on the Saturday as 2 hours were on Friday night.
For the fourth shift on Sunday 22/10/06 I need to collect the following codes for the total calculation: CodeHoursCostCtr 0018.00'Ordinary hours worked 0098.00'Hours worked on the Sunday
From that information the only data I really need to store in a table would be the totals grouped by code and cost centre. Eg., CodeHoursCostCtr 00117.00 001 8.003001 001 9.003002 006 9.00 006 9.003002 007 8.003001 008 6.003001 009 8.00 Does anyone know the best way to go about this? Should I generate a new table that links these codes to an employee? Should I make a function to calculate the codes for each day and store them in a table or make the function only sum the code totals for the week and store them in a table?
I have a form that I use to both add new records and edit existing ones. I do this thru a combobox and two command buttons. One button opens form in add records mode with combobox visible property set to no. The other button sets the visible property to true. I am getting an error message that "the field combo60 can not be found" Here is my code: Private Sub Label63_Click() DoCmd.OpenForm "frm_Client Information" Me![Combo60].Visible = True
End Sub The name of the combox is right. Can anyone help?
below is the code I am using to update multiple records on a sub form so that QtyReceived=QtyOrdered when you click the ReceiveButton, now my understanding of VBA is a little limited and the code I'm using won't work in my main form "frmReceiving" only in the subform "frmReceivingSubform" can anyone see what I'm doing wrong?
Private Sub ReceiveButton_Click() Dim rs As DAO.Recordset
Set rs = Me.frmReceivingSubform.RecordsetClone
With rs
.MoveFirst
Do While Not .EOF If rs("QtyReceived") = 0 Then .Edit rs("QtyReceived") = [QtyOrdered] .Update End If .MoveNext Loop
I have several list boxes, and each is populated using a query source. I want to make it so that when you double click a given entry it pops up either a form or query that says more information on the item.
Each list box is populated using two fields from the query a name and a number.
Example
Item A | Value A Item B | Value B Item C | Value C
I want it so if you were to double click Item A a form opens with the rest of the information so a text box or whatever with the following
Item A Value A AnotherValue A Etc A ....
Basically I want to be able to pop up a single line of the query based on double clicking of the list box entry.
This is what I have so far in code:
Code: Private Sub prospectLW_DblClick(Blah As Integer) Dim varItem As VariantDim strPlayer As String strPlayer = Me.prospectLW.ItemsSelected.Item(varItem) MsgBox strPlayer DoCmd.OpenForm ("Popup1") End Sub
The trouble I am having is getting the value from the listbox if I can get say Item A when you double click it ignoring the other info I want just the name of "Item A" so that when I open the form I can use that variable to filter the form I made that is basically just the query within a form. I hope that is enough information if not let me know I can add more.
I am not new to Access, however I am just starting to get to the point where I need visual basic to complete some things I want to try. My question is, how do I code a button (or modify an existing one) in my form that will email the same output I get when I use a Print Report button. Here is what I have from my standard buttons:
Private Sub Save_Svc_Record_Click() On Error GoTo Err_Save_Svc_Record_Click
I have a form (see attached) and every time someone goes to a different field I would like to change the border to red and when they leave that field to change the border back to the default color. I know I can put the code in for each fields got focus and lost focus section but would rather be able to put the code in once and for it to work for all the fields.
