I'm trying do a Select Case on a form control where the user is prompted for information depending on the value in the control.
Here's a generic example:
If field1 = "Vegatable" then
Select 1 for Brocolli
Select 2 for Lettuce
Select 3 for tomato
If field1 = "Fruit" then
select 1 for "Banana"
select 2 for "Apple"
select 3 for "Peach"
I'm using VBA to prompt users for two required fields. One I can do, but with more than one I get into issues like only 1 message box appearing at a time. If both fields are blank I want both messages to appear. Here's my code, it's in an On click function:
If IsNull(req_file_num) = True Then
MsgBox "Required field: File #. The appropriate file number must be entered before this PO's status can be changed to Filed."
GoTo Err_Command_158_Click2
End If
If Not [req_filed_date] Then
[req_process_status_rec_id] = 8
MsgBox "Your changes have been processed. This purchase order now has a status of Filed."
DoCmd.Close acForm, "frm_req_file"
GoTo Err_Command_158_Click2
Else: MsgBox "Required field: File Date. The appropriate date must be entered before this PO's status can be changed to Filed."
GoTo Err_Command_158_Click2
End If
Err_Command_158_Click2:
End Sub
I realize that it only shows one message box because I have the GoTo Err_Command_158_Click2 command, but I couldn't figure out how else to keep it from continuing through the code. If both "req_file_num" and "req_filed_date" are null, then I don't want the "req_process_status_rec_id" to change to 8 and have the form close.
Thanks for your help!
I have a database table to which I will be adding multiple records from various other contributors. I would like to build a simple, import-friendly excel table that I can mail out to others, so that they can fill it out and send it back to me. Then I will save import steps as a macro or something similar, so that I can easily import their data.
The hard part is that I would like a way for Access to compare the new records with the existing records, identify potential duplicate records by predetermined criteria (in other words, 'wild-carded' to some level of specificity, since duplicates could be off by just a few characters), and then prompt me to examine each record to be imported alongside however many existing records that have been identified as potential duplicates. Then I as the user could:
1. accept the import record as a new record
2. decline importing that record since it is a duplicate of an existing record, or
3. edit by hand an existing record that is a duplicate of the proposed import, but may require additional information, and then decline to import the new one.After each prompt, I would like the import process to continue to the next potential duplicate, and when there are no duplicates, to complete the import and display a message box that says "Import complete".
Access 2010:
The database I'm working on stores label images, or better stated, it stores file paths where the label images are stored. These paths are used in reports and forms to view the label images within the database. What I'd like to do is to create a "save" or "email" button that will allow the user to select a location to save just a copy of the image in jpg format. I realize that it is possible to imbed the jpg image into a report and export it as a pdf. What I'd really like to do is find a way to save the image (perhaps by copying it from the path in the database and simply relocating it) and preserve the jpg extension if that is possible.
I am also aware that there are third party applications that will convert pdf reports into jpg files however network restrictions keep me pretty limited in my ability to download third party software.
I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"
Code:
Private Sub Command3_Click()
Dim dbs As Database
Dim sqlstr As String
Set dbs = CurrentDb
Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "")
Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")
[Code] ....
I'm using VBA code to open a query, generate a report for a test and an answersheet. I'm using the same report, and don't want to make many reports. I have the below code which runs my query, and generates the reports.
Private Sub Command2_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "1", acViewNormal, acEdit
DoCmd.OpenReport "WrittenExam", acViewPreview, "", "", acNormal
DoCmd.OpenReport "WrittenExamAnswerSheet", acViewPreview, "", "", acNormal
End Sub
I have a need to be able to set the report title for both reports when running my code.
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
View 2 Replies View RelatedQuery
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
I have varcurve1 varcurve2 varcurve3....
I want to provide a mean (interface?) for user to choose varcurve1 varcurve2 varcurve3 to run above query.
for eample if user choose varcurve1
the query will run
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
if user choose varcurve2
the query will run
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve2 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
is there a way to do that?
Thanks
Hello,
I have a table which contains:
InvNo - Autonumber (invoice number)
AccNo - Clients account number
Amount - Amount of invoice
I also have this data (minus the InvNo) in a linked spreadsheet. I would like to keep the spreadsheet as it is all set up for the printing of multiple copies etc.
The idea is, I want to import the data from the spreadsheet into the database, which will then apply an invoice number (because it is an autonumber), and then get that invoice number back into excel.
I have done it all, but cannot get it to give me the invoice number assigned to it.
I thought I would have a simple query that looks for records in the table that are the same account number, and amount (plus a few others I havn't included here), and then it could return the InvNo.
But, whenever I run the query, it asks me to specify the parameter, it doesn't seem to be able to read from the linked spreadsheet (it is openable, I can open it in access just fine).
In the query I have put (under criteria): "[LinkedSpreadsheet]![AccNo]" (and similar for the other fields I want to match).
What is wrong?
Thank you!
Can I get my query to prompt for a specific date somehow?
Or....I am running a report off my query, can I get my report to prompt for a specific date?
I have a crosstab query and I want to prompt users for certain colunms is this possible. If so how, I want to prompt the users to enter month.
View 2 Replies View RelatedI want to be able to have multiple parameter queries, but the user needs to have the option of entering a parameter or not limiting the output at all. How do I let the user choose "all", or what do they type in so that nothing is excluded?
View 14 Replies View RelatedI have three check boxes in my form and i want the user to select any one of the check box, if not, i want to display an error msg? How to do it?
View 2 Replies View RelatedHi all
Im wanting to open a sales report, but I want to be prompted to enter dates FROM and TO so that I can get specific sales.
Example
"Enter start date" here i'd enter 1/8/05
"Enter end date" here i'd enter 31/8/05
that way i'd get all sales throughtout august. Then when september comes round I could enter new dates and save me having to go in and change the filter.
Can anyone help?
Thanks
When I run the below code I am getting the error "End Select without Select Case" I figured it might be because I have the "End Select" before the "End With" however when I move the "End Select" after the "End With" I get the error "Loop Without Do".
Code:
Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
[Code].....
Hi,
I would like to run a simple select query, where the CRITERIA is based on the user choice.
I have a form with a combo box, with a few choices, and a button that will trigger a macro with one query for now. I can store the choice in to a variable e.g. "town", using Microsoft VB code.
How can I transfer the variable to the CRITERIA field in the query, so whenever I chose a specific "town", my query will select the records for that specific town.
I want to use one query, and I don't want to have to "hard type the criteria in to the cells' query", but I would like to use a variable that can change and will be read it from the user choice through combo box.
If I am not clear enough, please let me know.
Thank you!
Hello everyone,
I've made a form where the user can fill in a field ID... and than by clicking on a button another form will be opened with the data of that person (with that ID that's filled in). But if the user fill in an ID that is not currently in the database, there will be also shown another form, but than with no data.. Now I want that if the ID is not known, to display a message or something (like "this ID is not known, try again") and not to open the form..
I've used the following code under the button:
Private Sub openenFormulier_Click()
On Error GoTo Err_openenFormulier_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Onderzoek"
stLinkCriteria = "[Qr_Ant_PG.ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_openenFormulier_Click:
Exit Sub
Err_openenFormulier_Click:
MsgBox Err.Description
Resume Exit_openenFormulier_Click
End Sub
Hopefully someone can help me!
Thanks,
Ankie
I have Access2003 and a problem creating a simple method of constructing a union query.
I am using a commercial paradox databse that has a habit of creating a new table every time a certain action is performed. There is currently a set of 160 tables with identical fields. The database allows me to view data in these tables individually. I would like to pull the information together, from between 20 and 30 specific tables at any one time, and view the data in one table.
I have linked the tables in a an access database and would go about viewing the data by creating a union query containing each of the tables of interest. Then appending the info in these tables to a new table.
Unfortunately manually adjusting the SQL expression is a bit cumbersome to say the least I am wondering if it possible to select the tables of interest from a List Box on a simple form and have those selected inserted into the union query ?
Alternatively, the table names are actually a field within a different table. Another field within this table actually determines whether they will be of interest. So ideally I could query that table to tell me which tables need to appear in the union query. Again I would like the union query generated automatically as a result of that query.
I am confident, perhaps without good reason , that this must be possible ....
any help much appreciated.
How to proceed and what is the "accepted" version of events.I have created a report, a dynamic method statement actually, and want my user to be able select some data to appear in the report. Report is rptMS01, it is fed from a query and has some fields to automatically populate [Company], [Site], [Postcode] etc. All of the static text is in CanGrow textboxes to sidestep the report height limit. The report is opened by a button on a form which uses a macro to open only for the current site. All OK so far.
My boss would like a section/text box/subreport to select the personnel involved in each method statement and this to appear on the report. So, on clicking the button on the form which would usually open the report directly, some kind of intermediary selection form opens instead with a list of all personnel. User can then select which personnel to involve and on clicking OK this appears in the designated box on the report. A CanGrow/Shrink textbox would be ideal!
Do I need to include this in the current query which feeds the report? I know a listbox can have multiple selections but how would I reference this? How do I keep the other fields as well? Should I create a table with the personnel list?...
I have a for form with a button to run a report. The report runs a query. The query reads data on the form to feed the report. The data that is from the table works fine but data from a text box on the form will not. It prompts for EXPR1.
In my query I have the following:
EXPR1: [Cnum]
[Forms]![FRM-Customer]![Cnum]
where FRM-Customer is the form with the button to run the report and Cnum in the text box data that I want to pass to the report. Every time I run it it prompts Cnum?
I have a report with a subreport. I have added a parameter to the query to prompt for [manager] or is null to get all managers. However, on the report when I enter a manager my count on the subreport still returns the count for all. So, I added the same prompt to the query for the subreport. It returns the right count, however, it prompts for the manager multiple times. How do I get it to prompt only once yet get the right total?
View 1 Replies View RelatedI have a form that allows the user to select a record in a combo box. When this selection is made I want a 2nd form to open and give the user a place to enter additional info about that item selected. I have the forms working correctly but now I would really like to add the 2nd form to the first form as a subform but when I try this I get the message"The expressing is typed incorrectly or is too complex to be evaluated..." There are no fields on the first form that link directly to the 2nd form - because the combo box in an unbound combo (the selection in the combo box is what the 2nd form opens base on) I tried to add this as a subform with no parent/child info and I get the message stated.
I was hoping to make the 2nd form not visible until the selection was made in the combo box then make it visible for the info to be entered. Would really like it to be on the same form not as a separate form opening.I believe I just found that the reason for the message. It seems not to be a subform problem but is because the selection in the combo box needs to be made BEFORE the form can open. So now the question is - how can I make this a subform and avoid this error when the main form first opens. I will test the visibility and see if not visible until the selection clears this up.
I have a combo box that needs to only show approved values for any current data input but also be able to show legacy values (that were approved at one time but are no longer approved).How can I only allow the user to select from current approved values but still be able to pull a legacy record and show the legacy value.
Table structure
tblRepairReason
ID (Nnumber)
Name (Text)
ApprovedValue (Yes/No)
The recordsource of the combo box:
Code:
SELECT tblRepairReason.ID, tblRepairReason.Name, tblRepairReason.ApprovedValue
FROM tblRepairReason
WHERE (((tblRepairReason.ApprovedValue)=True));
In my database i wanna use the DoCmd.CopyObject command with a button like as
DoCmd.CopyObject "Destination path can be browse and select file", "AcInformation", acTable, "AcInformation"
Image of the plane:
how can do this?
I have a few tables, one of which stores a record of changes made to user accounts, and some of those changes are linked to a "multichange" table for when I have made the same change to multiple accounts.
What I am trying to do is select the change_id from my changes table, with a limit of 1 as I only want one result (it will always be the same) but I am using this query and getting a syntax error telling me I have a missing operator.
Code:
SQL_SELECT = "SELECT change_id from dbo_user_change where username = '" & username & "' AND change_type = 'new account' LIMIT 1"
Set qresults = CurrentDb.OpenRecordset(SQL_SELECT)
Do While Not qresults.EOF
multichange_id = qresults.Fields(1)
qresults.MoveNext
Loop
qresults.Close
Hi all,
I Have a form bound to a table.
The form will be used by poor experienced users so to avoid problems i would like whenever we move to a new record(or changing the contents of a stored one) a save record message to appear refering to the one we are just leaving.
Generally i would like a solution which will restrict users from entering data by mistake using scroll mouse or ctr+page down.
If i could design it ,i would choose that nothing in the form is saved until a save all button is clicked.This means that the user will enter virtually as much data as he likes but they will be stored in the table only when the button is clicked,but i don't know if this is possible since my form is bound to table.
Thank you all in advance,
black ailouros