Modules & VBA :: Code For Specific Lookup Selected

Jul 17, 2013

An if statement which will disable out a field depending on which type of field is selected from a separate lookup.I know that is confusing, so let me explain with an example. If I have a lookup field connected to "Vegetables", "Fruits" and "Nuts". If the user selects a "Vegetable" from the lookup field, I need another field disabled, for example sake, "What fruit did you buy?". If the user selected a "Fruit" from the lookup, "What fruit did you buy?" would be enabled.

I know how to enable and disable fields, but it is the If Statement. How would you specify an If statement to specifically look at which category of a lookup is selected?The lookup is not static. So going with our example, the user can add more fruits, vegetables, and nuts as desired, so simply specifying the IDs for the available options will not work. The If statement needs to encompass the category.

View Replies


ADVERTISEMENT

Modules & VBA :: Writing Code To Open Query After User Selected Fields To Include From ListBox

Jan 14, 2014

Access 2007-10
Listbox created: List62 (I know I need to rename it, but for now)
Multi-select: Extended
Row Source Type: Field List
Row Source: qryFieldList
Open Query: qrySelectedFields (I added primary key to first column, just to have at least one destination field).

Goal: to select multiple fields within listbox, click on command button to open query "qrySelectedFields" with selected fields from list box.This is the code I have on the command_click:

Dim varItem As Variant
Dim strSQL As String
If Me.List62.ItemsSelected.Count = 0 Then
MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT"
Me.List62.SetFocus
End If

[code]....

It does absolutely nothing - doesn't add the fields to "qrySelectedFields", doesn't open the query, notta.

View 8 Replies View Related

Modules & VBA :: Code To Open A Form To A Specific Record

Sep 4, 2014

I am using a datasheet view with dbl click code to open a form to a selected record. I was able to use pbaldy's code and it worked perfectly.

DoCmd.OpenForm "Asset Status", , , "[Project Number] = '" & Me.Project_Number & " ' "

Then I realized I really want to be able to go to other records after I have gone to this form and tried this:

Dim rs As Object
Dim lngBookmark As Long
'set variable to the current record
lngBookmark = Me.Project_Number
'open new form

[code]....

But to no avail. Project_Number is a text field.

View 12 Replies View Related

Modules & VBA :: Create Some Code For Button In A Report That Will Follow A Hyperlink To Specific File

Sep 9, 2013

I am trying to create some code for a button in a report that will follow a hyperlink to a specific file. The problem I'm having is that the files that are at the end of the hyperlink can have various extensions (*.doc, *.docx, *.pdf, etc.) I'd like to be able to put a wildcard in the code to allow the opening of the file regardless of the extension.

Code so far:

Private Sub Command6_Click()
Application.FollowHyperlink ("C:UsersjbeggDocumentsAccessTestFolder" & [FileName] & ".*")
End Sub

View 11 Replies View Related

Forms :: Copy Specific Fields From Selected Record To Specific Fields In Subform?

Jul 9, 2015

I am new to access i have a problem which is i have made a form which contains a subform and a read only subreport, what i want is the ability to select a record in read only subreport as in the picture attached and make a button that when i press on it, it should copy the values of the itemsID field, Packing field, ContainerNo field and origin field from the selected record and then paste them in the subform below.

Also i want to add more then one item, so the when i press on another record it should paste the values below the first record.

View 12 Replies View Related

Lookup Associated Fields With CourseNumber Selected From Combobox

Sep 15, 2011

I am new to access. I have the following code to lookup associated fields with a CourseNumber that is selected from a combobox. The information is then stored in a CourseEnrollment table linked to student info.

The CourseNumber and associated fields are located in a LU_CoursesOffered table. The LU_Courses Offered table has been populated by choosing associated data from combo boxes based on other LU tables.

I am getting the primary keys returned for the associated data rather than the text stored with that key.

Private Sub Combo103_AfterUpdate()
CourseName = DLookup("CourseName", "LU_CoursesOffered", "CollegCourseNum = " & Nz([CollegeCourseNum], 0))
LevelSection = DLookup("LevelSection", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))
AcadGrad = DLookup("AcadGrad", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))
Instructor = DLookup("Instructor", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))
End Sub

View 3 Replies View Related

Record Lookup In Form Flashes One Or More Records Before Displaying The One Selected

Dec 3, 2014

I have a form (Student Details) that has an unbound lookup box using a query to select a student. The lookup has the hidden Student ID autonumber key field plus Class Number, Last Name, First Name, Status, sorted by Class Number (descending), Last Name, First Name.

The form record source is a similar but unsorted query.

There are nearly 1000 student records. The form has pages (tabs). The lookup box is in the form header. There are dozens of fields in the 1st Page (General) including an picture (attachment, not hyperlink) field holding a photo of the student, the other 6 pages have less.

When you select a student via the look up, at least one sometimes more students "flash" before it displays the one you want, which seems to display twice before it stops.

I deleted the picture field to see what difference that made, because it seemed to do the most flashing, and indeed, without that field you go right to the record. I looked at the Student table indexes and it seems that field type is not index-able, which is understandable.

Is there any way to improve this with still leaving the photos as attachments? Some thoughts:

It improves if it is displayed first as an Icon or Paper Clip, and probably would if a hyperlink field, but we like to have the picture displayed and not have to open the link to see it nor worry about the location changing invalidating a link. It seems that if the record was displayed with the picture field only filled after all the other fields were filled it would make for a better impression.

Can an On Load form event VBA or Macro be written that either hold the picture back until the rest of the fields are displayed or if an icon or changed to a hyperlink to open. Would it work if the pictures were attached to a separate joined table?

View 11 Replies View Related

Lookup Value/Defalut Value Code

Sep 22, 2004

Can anyone tell me how I should modify this code to make it functional for pulling up the previous record's value in the current record? i realize this is wrong, but it's as close as I could get. Do you know how I can fix it so that it will work? I want to put this in the default value field. I think there's something wrong with the Previous Record part. Thanks!


=DLookup ([ERName],Employee Records, [Forms]![ERName] = Previous Record)

KellyJo

View 9 Replies View Related

Lookup Value Code Broken... HELP!!

Sep 27, 2004

The following code is being used to pull in the Name from the previous record into the current record. It works GREAT for 28 records, and then it starts just pulling random names from the source table. Can you tell me what I'm doing wrong? Thank you!Private Sub Form_Current()

Dim s As String
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set db = CurrentProject.Connection

s = "SELECT Last(ERName) AS ERName FROM [Staffing]"

Set rs = New ADODB.Recordset
rs.Open s, db, adOpenDynamic, adLockOptimistic
'add to the table

With rs
Me![ERName] = .Fields("ERName")
End With
Set rs = Nothing
Set db = Nothing

End Sub

View 8 Replies View Related

Enter A Code To Retrieve All Information From Selected Row

Feb 5, 2015

I'm not an advanced user of Microsoft Access 2010, I've been working on a database for a company that does Non Destructive Testing, so my task is to simplify how the technicians process the information and make their jobs easier.

Firstly, I have assigned a Unique Identification to a code in this case - X8120 This code is assigned to a row of information.

What i want to do is enter the code X8120 into a text-box and once the code is enter, i want the database to retrieve all information that's assigned to that code from the row (which is 17 fields) in a table.

When the information is retrieve, i want those 17 fields to automatically populate into their own text-boxes, so there would be 17. This would all be on one form. So the technician only has to enter a code which displays the information relating to the testing the technicians have done.

What i have done so far is made the table into a form and added a select button command, so a technician would click on a button that opens a form and select the row of information they want and populates those 17 fields.

So my aim is to simply enter a code or an event and the database will retrieve the information instead of going through a form to select the information. So i am simplifying the process even more.

View 14 Replies View Related

Can Limit A Specific Value From Lookup Field To Only ONE Record At A Time?

Aug 2, 2012

Any way I can set a table up with an intrinsic limit, specifically that only ONE record at a time (this will vary) can possibly have value X selected from a lookup field containing assigned values X, Y and Z. Is this possible? If so, how do I do this?

View 2 Replies View Related

Modules & VBA :: Search CSV To Find Specific Statement / Text On Specific Line?

Jul 8, 2015

how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.

If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).

The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).

View 3 Replies View Related

Forms :: Code To Pick A Field From A Record Selected Via List

Dec 11, 2014

I have a query with the following structure;

aDate(pkey) cost1 cost2 cost3 calc1 calc2 calc3
01/012014 ,,,,,,,,,1,,,,,, 2,,,,,,, 3,,,,,, 1,,,,,,, 2,,,,, 3
01/02/2014 ,,,,,,,,1,,,,,, 2,,,,,,,, 3,,,,,, 2,,,,,, 4,,,,,, 6
01/03/2014,,,,,,, 1 ,,,,, 2,,,,,,, 3,,,,,,, 3,,,,,, 6 ,,,,, 9
01/04/2014 ,,,,,, 1,,,,,,,, 2,,,,,,, 3 ,,,,, 4,,,,,, 8,,,,,, 12

fields calc1,2,3 are running totals of cost1,2,3

I expect/hope to first calculate the sum of a cost field and then minus the value of its corresponding calc field from a specific record.

result = sum(cost1) - calc1 selected record value

I want to select the calc1 record from a drop down list of the primary key. Which cost field is in the equation will static/defined as I intend to make a textbox for each field.i need to know the code to pick a field(and retrieve it's value) from a record selected via dropdown list.

View 4 Replies View Related

Queries :: Drawing Register Database - Lookup Specific Criteria Based On Date

Aug 11, 2014

I have a drawing register database which notes the revisions for all drawings issued. One drawing can be revised several times so I have a details table which notes the revision letter, date issued and the notes for each revision. Using this table I created a max date query to show the latest issue date and revision for each drawing. This query is used for a transmittal form for issuing drawings. Both the form and report is working perfectly.

The TBLTransmittal consists of the following fields
ContractName
IssueDate
SubSup
DwgNo (multi-value checkbox)

Using the TBLDwgRegisterDtls or the query QRYDtldDwgReg (which includes the drawing titles) I would like to create a query that would look up the latest revision at the date of issue and show the revision letter.

I started a query combining TBLTransmittal and TBLDwgRegisterDtls and in the criteria for DateIssued of the TBLSDwgRegisterDtls (which is when the drawing was issued by the architect to the contractor) I put "Not > [IssueDate]". This is filtering the information to show revisions issued up to the date we (the contractor) issued the drawing to our sub-contractor but I need now to pull the max revision only from this information.

Before I go I will give you an example of what I want from the end result

Drawing BK2-02 rev. D was issued by the architect to the contractor on July 17th. This drawing was in-turn issued to our sub-contractor (Sub "A") this Saturday, August 9th. The same drawing was revised this morning and rev. E was issued to Sub "A" today, August 11th.

I want the end report to show that Sub A received Rev. D on 09-08-14 and Rev. E on 11-08-14.

I'm wondering if another max date query on this new query would work? I'll try and see what happens.

View 2 Replies View Related

Forms :: Lookup With Values From Another Table - Populate County Once Zip Code Entered

Mar 22, 2013

I want my form frmintake to lookup with values from another table and populare county once the zip codes has been entered.

I have a table called tblintake and another tblcounty.

tblintake has zip codes entered manually

tblcounty has zip codes and counties for all regions

how does this work out in dlookup.

=DLOOKUP[Zip Code],[tblCounty],[zip Code]=[County])

View 1 Replies View Related

Report - Page Break On A Specific Code

Jul 7, 2006

I have a report with grouping on a code - I would like to do a page break on the second code - I don't know where to start on this.

View 1 Replies View Related

Code To Link Tables - Overwrite Specific Links

Oct 13, 2006

I searched and couldn't find anything on this specific issue.

I'm on the verge of designing a pretty big database and I've run into what may become a bit of a problem. There will probably be several backends in different locations. Should another backend ever be created or if the location for one changes, the user can use a form to refresh the links to the tables on that specific database.

The way I've done this so far is by having a piece of code that checks the tables for a connection string. If the table has a connection string, its a linked table, so the link gets deleted and replaced with the new link. Well this will no long work since there will be several backends and only 1 of them may need its tables relinked and using that code will delete all the table links, including the good ones.

Is there a way to make 'DoCmd.TransferDatabase' overwrite table names instead of giving the duplicate tables a number suffix? I think that doing this would be easier than retrieving the table names from the new backend and comparing them with the linked table names in the frontend so the old links could then be deleted.

View 1 Replies View Related

Queries :: VBA Code On Subform To Lookup Email Field On Table And Launch Email?

Jun 26, 2015

VBA Code to go in the on double_click event of a name field in a Subform bound to a table. The subform is just a copy of a data table and within the subform view, When the field "employee name" which contains e.g. John is double clicked, I would like access to Lookup and get John's email in the employee table under field "Email" and launch outlook application and insert it into the To field.

I assume hyperlinking the field can also achieve this similar to what excel does but I am fast learning that what is standard functionality in excel is a whole another story in Access..

View 3 Replies View Related

Modules & VBA :: Sum Of Items Selected In A List Box

Sep 10, 2013

how to get the sum of column 2 of a list box total bags is in the second column, i only want the total of bags of the ones selected

I can get the sum of all the boxes but only want highlighted ones

Public Function SumListBox(sForm As String, _
sCtrl As String, iColumn As Integer) As Variant
Dim frm As Form

[Code]....

View 1 Replies View Related

Modules & VBA :: Expand Only One Selected Row In The Tree

Jul 30, 2014

OFC without "+" Now I have something like this in double click event in one of my field

Code:
If Me.SubdatasheetExpanded = False Then
Me.SubdatasheetExpanded = True
Else
Me.SubdatasheetExpanded = False
End If

But this code expand all of my items:/

View 1 Replies View Related

Modules & VBA :: Requery Once A Combo Box Selected

Jul 15, 2013

I have a form which contains two sub forms. In the first subform I have 4 fields of Cut#, Size, Quantity and Style. Once the cut# is selected, a vba code runs and fill in the rest of fields. In second sub form I have a field of Fabric# (Combo Box) which should be restricted to the Style value on the first sub form. I have a query which contains the Fabric# and Corresponding style and I try to write a vba code to requery once Style value changes.

View 14 Replies View Related

Modules & VBA :: How To Validate That At Least One Checkbox Is Selected

Apr 13, 2014

I am trying to validate that the user selects at least one checkbox out of three on the form in Access 2010. They can select one, two or even all three if they wish, but at least one must be checked. I am using the following code attached to the click event of a command button. It was fine but seems to have stopped working. The control names are generic to illustrate my code. The form is unbound:

if (chk1 + chk2 + chk3) = 0 then
Msgbox "Please select at least one checkbox"
exit sub
end if

View 14 Replies View Related

Modules & VBA :: Export Selected Columns As CSV?

Dec 30, 2014

Is there any way to 'Export' the selected column (user selected columns) as CSV. I have attached a mockup (screenshot) that gives a clear thought about the scenario. The 'User' will be able to select the 'Columns' for the export of 'tblInventory' and by clicking 'Export as CSV' button will export the 'Selected' columns as CSV.

View 1 Replies View Related

Modules & VBA :: Update Selected Records

Mar 5, 2015

I have a datasheet subform where i need to select some records by clicking a check box and update a certain field/s of only those selected records.

View 1 Replies View Related

Modules & VBA :: How Many Full Records Have Been Selected

Sep 25, 2013

In a form's VBA code, I'd like to know whether a full record has been selected, like when the user selects one or more rows before deleting with the Del key.

Is this possible?

According to [URL]......, Me.SelHeight returns the number of rows that have been selected within the selection rectangle. If I select a rectangle of cells in Datasheet view, for example, Me.SelHeight will be > 0 even though full records have not been selected. I want to know how many full records have been selected. Is this possible?

View 2 Replies View Related

Hard Code TIME To Selected Date On Form (to Make It Date&time) For My Query Criteria

Aug 17, 2006

Hello buddies :D, do you have any idea how to make this work?

To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.

This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved