I am trying to take input for my reports based on a query result dynamically for which i dont want to display the Datasheet after the execution of Query ,inturn i am displaying results in Report. Any help on this would be appreciated.
View RepliesI have a query, it joins a local table to a remote table (Oracle Database Table) which makes it slow(ish).
When I am in design mode and i click the "View Datalist" button, which visually shows me the results, the query executes in about 12 seconds.
When I use the "!" Button to physically Execute the Make-Table Query, it takes hours.
What things should i look for that might cause this problem? Where do I need to optimize.
To me it seems rather odd that the query would execute so fast when i view the data than when it physically executes the data.
Same speed problems when i just run the query as a Selection Query as opposed to the Make-Table Query, so it's not an issue of writing to disk/etc.
whenever I run/execute a query in Access it is modifying the first record on the table in which it is calling the data from.For example if the first record might contain the following:
Record A: John Doe, Oct, 2014, Account is Active
And lets say I am running a query to pulling records from Nov 2014.The Month and Year Fields in the above example for Record A gets modified to the query search parameters for Nov.Is there some of of record lock or controls that I need to adjust to prevent this from occurring?
Hi
We have recently upgraded our computers and therefore reinstalled Office on the new machines. The opreating system is Win XP + patch 2. The version of MS Access is 2002
For all our databases I have chosen to hide the database window, so other user don't accedentally change tables and queries etc. Now the problem is, with the new computers, whenever I minize the database and then open it, the database weindow pops up right on top of every thing else automatically.
I have no idea why this happens and how to stop it. Could any one please shed some light. Your help would be apprecialted.
Regards
Airdesign
Hi
I am a new user to Access. I created couple of forms using wizard, but when I view them, they show me all the available data by default. I want to stop this, if anyone can help me plz.
RMA
I have the following tables:
Supplier
Supplier# [Other columns]
Material
Supplier# Material# [Other columns]
DeliveryHeader
Delivery# Supplier# [Other columns]
DeliveryDetail
Delivery# Supplier# Material# [Other columns]
I've created a form based on table DeliveryHeader with a subform based on table DeliveryDetail. The two are linked by Delivery# Supplier#.
On the form, Supplier# is a combobox that lists all suppliers from table Supplier.
On the subform, Material# is a combobox that lists only the materials supplied by the supplier selected with form's Supplier#.
The problem is that this second list always shows the materials of the first listed supplier. For instance, when the form is loaded, it shows delivery #1 with all its details on the subform. Suppose supplier loaded to form is #1.
1) If I use subform combobox, it shows correctly supplier's #1 materials. But then, whatever delivery I navigate, it always shows supplier's #1 materials.
2) If I navigate to another delivery without using subform combobox, suppose I stop on a delivery where the supplier is #4, then subform combobox shows correctly supplier's #4 materials. But then, once again, whatever delivery I navigate, it always shows supplier's #4 materials.
I've spread Me.Requery here and there but without any success. What trick must I apply to force the execution of the query of the subform combobox Material# each time I navigate to a new record on the form?
i have created a new utility DB that has only one form..when it opens I set the startup form "Main" and on the OnOpen event of this form I have some code to open another database and close this one...
this means that whenever i open this database, it opens, opens up the other one and this utility DB closes...and i cannot access it coz it always opens and closes automatically...how can I stop it from executing to be able to edit its VB code?!
Thanks, this is driving me mad !
Hi Folks,
I am wondering if it is possible to execute a DTS package via Microsoft Access. I have done a bit of research and cannot seem to really find anything that claims this is possible or how to go about to get it to work. I am running SQL Server 2000 which has quite a few DTS packages ... it would be really neat if I could just create a small interface in Access whereby a form has a few buttons on it that can be clicked and the DTS packages can be executed on the server, this way folks won't need to log on to the server and run the DTS packages via SQL Server Enterprise Manager. What would REALLY be awesome would be if values can be passed between SQL and Access but I think if that is possible it would be quite an animal to achieve (I know it is possible via Visual Basic programming but not really worth all the work to create an independent program just to run a DTS package or pass a value). If anyone can provide me with any information or where to look I would appreciate it.
Thanks,
Joe
Hi everyone,
This is my problem. Almost all the time(90%), I open the main form of my application and a large part of the form is blank(white). If I open it from the Database window it often work well and all the form is visible.
When the bug occur, If I just switch from Access to another application like Internet Explorer and I come agian to Access then all the form is visible.
Is someone ever seen that kind of bug. What that can cause that problem. How can I solve it?
N.B. There is a lot of controls on my form and I guess that bug can be linked with it. Seems like Access is trying to open my form too rapidely.
Hi,
how can i create a comand button that open a form at predifined date?
example: if current date=2005/14/12(yyyy-dd-mm) then open form "example"
thnks,
Kopas
I have a problem about running SQL statement in VBA code. I will appreciate a looot if you can help me solve the problem ASAP.
I tried to run the following statement in my VBA code to update a table in my database:
DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"
It turns out that the table will not be updated if I run the above SQL statement with my other VBA codes. (ACCESS doesn't give any error messages even though the database was not updated.) But if I run the SQL statement by itself rather than within the other codes, the database will be updated successfully. (When I set a breakpoint at the above SQL statement and run it manually within the other codes, the database are updated successfully too.)
Apparently, the SQL statement and the other codes don't have any syntax errors. I wonder why ACCESS doesn't execute the SQL statement when the SQL statement is put within the other VBA codes. Is it because the table it tried to manipulate was locked by the other codes?
The following are all the VBA codes including the SQL statement I'm talking about. When I run the following codes together, the database is not modified by the SQL statement (the last sentence). But if I run the last sentence and the other codes separately, the database was modified successfully.
Dim rstDataLoad As New ADODB.Recordset
With rstDataLoad
Set .ActiveConnection = cnnSHELL
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT TBL_GRANTBASEDATA.* FROM TBL_GRANTBASEDATA;"
.Open
End With
'Calculate Retire_Date
rstDataLoad.MoveFirst
Do While Not rstDataLoad.EOF
Calculation omitted.
rstDataLoad.MoveNext
Loop
rstDataLoad.UpdateBatch
rstDataLoad.Close
Set rstDataLoad = Nothing
DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"
I'm using hte Shell() function but this doesn't wait for the executable file to finish so I'm looking for a command line function that would wait until the executable file is finished executing...
I saw this function WaitFor but it is not supported in MS-Access? So, what do I do?
Thanks...
I have this segment of code that adds data to fields FName and FPath in the table Files. It works fine but the syntax is a nightmare with all the double quotes and symbols.
Code:
strSQL = "INSERT INTO Files " _
& " (FName, FPath) " _
& " SELECT """ & strTemp & """" _
& ", """ & strFolder & """;"
CurrentDb.Execute strSQL
I have another field in the table Files called CompletedBy that shows who updated the table (they type their name into a textbox Text1 before clicking a command button to run the code). I would like to add this data to the table during the execution of strSQL. All the records added to the table Files will have that value in the CompletedBy field (say, Bob Jones).I tried the below changes but the code doesnt work.
Code:
strSQL = "INSERT INTO Files " _
& " (FName, FPath, CompletedBy) " _
& " SELECT """ & strTemp & """" _
& ", """ & strFolder & """ & Me.Text1 & """;"
CurrentDb.Execute strSQL
How would I add a value in field CompletedBy, from the textbox Text1, to all records added to table Files when the code is run?
I have a form that loads when the database is open, and the form has buttons etc. that leads to queries and reports in the database. (Queries open in a form). Since most queries take a while to load, I was wondering if there is a way to Pop up a message box stating "Please Wait, Query is running..." (or something like that), and once the form opens the MsgBox should close automatically. (The user should not have to hit Ok button.)
Most users of this database would have restricted access such that they would not have navigation pane and toolbar visible - hence I want to make it a touch more interactive when a process is running...also if someone is new to access they might not notice that taskbar status and I don't want them thinking the program is stuck etc..
I'me running on Win7, Office 2010, Acc2003 format (.mdb), writing data to Excel 2010 (.xlsx)I have code to create an Excel file, send record set data to it, and then add some formulas and formatting. I was trying to tune up the formulas, when I got a pull back:Access VBA code suddenly stops running, and the file gets displayed in Excel
Code:
'ApXl is Application.Excel
'xlWSh is the Excel WorkSheet
'rst is my DAO.Recorset
[code]...
The same happens when I have ApXl.Visible = True
I have a database called Tables.mdb with a Make Table Query called Qmak_Tables that I want to run nightly.
I have set up a Scheduled Task to run Tables.mdb every night. I have created a Form that loads on start-up. I have created a Macro that runs the Make Table query when the form loads. I have also de-selected the confirm record changes/document deletions/action selections in Tools > Options > Edit/Find menu so the query won’t be stopped by a prompt that needs manual input. Here’s the catch:
After the query runs, it displays the prompt, “The existing table will be deleted. Do you want to continue?” How do I disable this prompt so my task can continue to run?
Hi,
There is a requirement for
1)Populating a datasheet in a subform with a querystring which is dynamically built in VBA.This querystring is constructed based on the search criteria fields selected by the user in the main form.How can this be immplemented?
2_To click/double click a record in the datasheet,extract data and populate textfields,comboboxes with it which are in the main form.How is this achieved?Also,I can't find click events in a datasheet.
Pls let me know.
Thanks,
Savita
I'm told (via the non-helpful help system packaged with MsAccess) that the way to 'turn off' the helpful confirmation message related to an action query is under tools>Options>Edit/Find tab, and then uncheck the appropriate boxes.
I've done this. In fact, right now I have all three boxes unchecked which relate to confirmation messages (record changes, document changes, and action queries) - and I'm still having that same popup message every time I run the query, or have a report pull the query for me.
this wouldn't be more than just a hassle, except that one of the end users for this system isn't the most computer-savvy person in the whole world. In fact, I desperately need to "steve-proof" this system! I have this nightmare of being woken up at midnight because of this little glitch. any ideas how to disable that popup confirmation message for good??
I'm new to this.
Is there a way to make a Form Datasheet (including calculated cells) update the corresponding Table Datasheet?
Is there a way in access, through vba or any other means, which would allow me to either enable or disable criteria in a query based on the value of textbox??
Like i have a query that displays bookings customer has made between two dates, so i made a query and in the starting and ending date fields i get the value from form. In case if the use does not enter ending date, i would like to run the query with starting date only that displays booking made after the starting date.
Currently the starting date criteria is set to a textbox of form, and so is ending date. So if customer selects 1st April 2015 as starting date and 30th April 2015 as ending date the query should display the bookings between 1st and 30th of April. And if the user enters only 1st April in starting date it should display bookings starting from 1st April onward.
Hello All,
I have a database with multiple tables, each table with a subset of common fields I need to work with. The way the database was set up was a series of append queries that created a common table to combine the subset of fields. The problem with this is that I had to work with the common table but update the data in the parent tables and regenerate common table to keep everything up to date. To get around this I have created a union query to replace the common table but the data is not updatable. I have had a quick scan of the net and it seems union queries are not updateable by default. I understand the problem I have is a result of the database not being structured correctly however due to circumstances beyond my control, I am stuck with it. I have come across a couple of INSTEAD OF trigger examples but not sure quite how they work and if they can help me. Below is the SQL code for my union query so any help in making it updatable would be greatly appreciated.
Regards
Daz
SELECT [Belt Filter Press].Table, [Belt Filter Press].Plant, [Belt Filter Press].[New Tag], [Belt Filter Press].[New Comment],
[Belt Filter Press].[P&ID number], [Belt Filter Press].Purpose, [Belt Filter Press].[Motor Size], [Belt Filter Press].Starting, [Belt
Filter Press].New, [Belt Filter Press].Comment, [Belt Filter Press].Switchboard, [Belt Filter Press].PLC, [Belt Filter
Press].[PLC Interface], [Belt Filter Press].ACH, [Belt Filter Press].IL, [Belt Filter Press].ZSO, [Belt Filter Press].WIO, [Belt
Filter Press].ZSP, [Belt Filter Press].PSL, [Belt Filter Press].FSL, [Belt Filter Press].TSH, [Belt Filter Press].MSX, [Belt Filter
Press].SSL
FROM [Belt Filter Press];
UNION SELECT Bins.Table, Bins.Plant, Bins.NewTag, Bins.[New Comment], Bins.[P&ID number], Bins.Purpose, Bins.[Motor
Size], Bins.Starting, Bins.New, Bins.Comment, Bins.Switchboard, Bins.PLC, Bins.[PLC Interface], Bins.ACH, Bins.IL,
Bins.ZSO, Bins.WIO, Bins.ZSP, Bins.PSL, Bins.FSL, Bins.TSH, Bins.MSX, Bins.SSL
FROM Bins;
UNION SELECT [Chemical Dosing].Table, [Chemical Dosing].Plant, [Chemical Dosing].[New Tag], [Chemical Dosing].[New
Comment], [Chemical Dosing].[P&ID number], [Chemical Dosing].Purpose, [Chemical Dosing].[Motor Size], [Chemical
Dosing].Starting, [Chemical Dosing].New, [Chemical Dosing].Comment, [Chemical Dosing].Switchboard, [Chemical
Dosing].PLC, [Chemical Dosing].[PLC Interface], [Chemical Dosing].ACH, [Chemical Dosing].IL, [Chemical Dosing].ZSO,
[Chemical Dosing].WIO, [Chemical Dosing].ZSP, [Chemical Dosing].PSL, [Chemical Dosing].FSL, [Chemical Dosing].TSH,
[Chemical Dosing].MSX, [Chemical Dosing].SSL
FROM [Chemical Dosing];
UNION SELECT Clarifiers.Table, Clarifiers.Plant, Clarifiers.[New Tag], Clarifiers.[New Comment], Clarifiers.[P&ID number],
Clarifiers.Purpose, Clarifiers.[Motor Size], Clarifiers.Starting, Clarifiers.New, Clarifiers.Comment, Clarifiers.Switchboard,
Clarifiers.PLC, Clarifiers.[PLC Interface], Clarifiers.ACH, Clarifiers.IL, Clarifiers.ZSO, Clarifiers.WIO, Clarifiers.ZSP,
Clarifiers.PSL, Clarifiers.FSL, Clarifiers.TSH, Clarifiers.MSX, Clarifiers.SSL
FROM Clarifiers;
UNION SELECT Filters.Table, Filters.Plant, Filters.[New Tag], Filters.[New Comment], Filters.[P&ID number], Filters.Purpose,
Filters.[Motor Size], Filters.Starting, Filters.New, Filters.Comment, Filters.Switchboard, Filters.PLC, Filters.[PLC Interface],
Filters.ACH, Filters.IL, Filters.ZSO, Filters.WIO, Filters.ZSP, Filters.PSL, Filters.FSL, Filters.TSH, Filters.MSX, Filters.SSL
FROM Filters;
UNION SELECT [Gravity Drainage Deck].Table, [Gravity Drainage Deck].Plant, [Gravity Drainage Deck].[New Tag], [Gravity
Drainage Deck].[New Comment], [Gravity Drainage Deck].[P&ID number], [Gravity Drainage Deck].Purpose, [Gravity Drainage
Deck].[Motor Size], [Gravity Drainage Deck].Starting, [Gravity Drainage Deck].New, [Gravity Drainage Deck].Comment,
[Gravity Drainage Deck].Switchboard, [Gravity Drainage Deck].PLC, [Gravity Drainage Deck].[PLC Interface], [Gravity Drainage
Deck].ACH, [Gravity Drainage Deck].IL, [Gravity Drainage Deck].ZSO, [Gravity Drainage Deck].WIO, [Gravity Drainage
Deck].ZSP, [Gravity Drainage Deck].PSL, [Gravity Drainage Deck].FSL, [Gravity Drainage Deck].TSH, [Gravity Drainage
Deck].MSX, [Gravity Drainage Deck].SSL
FROM [Gravity Drainage Deck];
UNION SELECT [Grit Classifiers].Table, [Grit Classifiers].Plant, [Grit Classifiers].[New Tag], [Grit Classifiers].[New Comment],
[Grit Classifiers].[P&ID number], [Grit Classifiers].Purpose, [Grit Classifiers].[Motor Size], [Grit Classifiers].Starting, [Grit
Classifiers].New, [Grit Classifiers].Comment, [Grit Classifiers].Switchboard, [Grit Classifiers].PLC, [Grit Classifiers].[PLC
Interface], [Grit Classifiers].ACH, [Grit Classifiers].IL, [Grit Classifiers].ZSO, [Grit Classifiers].WIO, [Grit Classifiers].ZSP, [Grit
Classifiers].PSL, [Grit Classifiers].FSL, [Grit Classifiers].TSH, [Grit Classifiers].MSX, [Grit Classifiers].SSL
FROM [Grit Classifiers];
UNION SELECT Instruments.Table, Instruments.Plant, Instruments.[New Tag], Instruments.[New Comment],
Instruments.[P&ID Number], Instruments.Purpose, Instruments.[Motor Size], Instruments.Starting, Instruments.New,
Instruments.Comment, Instruments.Switchboard, Instruments.PLC, Instruments.[PLC Interface], Instruments.ACH,
Instruments.IL, Instruments.ZSO, Instruments.WIO, Instruments.ZSP, Instruments.PSL, Instruments.FSL, Instruments.TSH,
Instruments.MSX, Instruments.SSL
FROM Instruments;
UNION SELECT Mixer.Table, Mixer.Plant, Mixer.[New Tag], Mixer.[New Comment], Mixer.[P&ID number], Mixer.Purpose,
Mixer.[Motor Size], Mixer.Starting, Mixer.New, Mixer.Comment, Mixer.Switchboard, Mixer.PLC, Mixer.[PLC Interface],
Mixer.ACH, Mixer.IL, Mixer.ZSO, Mixer.WIO, Mixer.ZSP, Mixer.PSL, Mixer.FSL, Mixer.TSH, Mixer.MSX, Mixer.SSL
FROM Mixer;
UNION SELECT [Odour Control].Table, [Odour Control].Plant, [Odour Control].[New Tag], [Odour Control].[New Comment],
[Odour Control].[P&ID number], [Odour Control].Purpose, [Odour Control].[Motor Size], [Odour Control].Starting, [Odour
Control].New, [Odour Control].Comment, [Odour Control].Switchboard, [Odour Control].PLC, [Odour Control].[PLC Interface],
[Odour Control].ACH, [Odour Control].IL, [Odour Control].ZSO, [Odour Control].WIO, [Odour Control].ZSP, [Odour
Control].PSL, [Odour Control].FSL, [Odour Control].TSH, [Odour Control].MSX, [Odour Control].SSL
FROM [Odour Control];
UNION SELECT [Pump List].Table, [Pump List].Plant, [Pump List].[New Tag], [Pump List].[New Comment], [Pump List].[P&ID
number], [Pump List].Purpose, [Pump List].[Motor Size], [Pump List].Starting, [Pump List].New, [Pump List].Comment,
[Pump List].Switchboard, [Pump List].PLC, [Pump List].[PLC Interface], [Pump List].ACH, [Pump List].IL, [Pump List].ZSO,
[Pump List].WIO, [Pump List].ZSP, [Pump List].PSL, [Pump List].FSL, [Pump List].TSH, [Pump List].MSX, [Pump List].SSL
FROM [Pump List];
UNION SELECT [Safety Shower].Table, [Safety Shower].Plant, [Safety Shower].[New Tag], [Safety Shower].[New Comment],
[Safety Shower].[P&ID number], [Safety Shower].Purpose, [Safety Shower].[Motor Size], [Safety Shower].Starting, [Safety
Shower].New, [Safety Shower].Comment, [Safety Shower].Switchboard, [Safety Shower].PLC, [Safety Shower].[PLC
Interface], [Safety Shower].ACH, [Safety Shower].IL, [Safety Shower].ZSO, [Safety Shower].WIO, [Safety Shower].ZSP,
[Safety Shower].PSL, [Safety Shower].FSL, [Safety Shower].TSH, [Safety Shower].MSX, [Safety Shower].SSL
FROM [Safety Shower];
UNION SELECT [Screenings Equipment].Table, [Screenings Equipment].Plant, [Screenings Equipment].[New Tag],
[Screenings Equipment].[New Comment], [Screenings Equipment].[P&ID number], [Screenings Equipment].Purpose,
[Screenings Equipment].[Motor Size], [Screenings Equipment].Starting, [Screenings Equipment].New, [Screenings
Equipment].Comment, [Screenings Equipment].Switchboard, [Screenings Equipment].PLC, [Screenings Equipment].[PLC
Interface], [Screenings Equipment].ACH, [Screenings Equipment].IL, [Screenings Equipment].ZSO, [Screenings
Equipment].WIO, [Screenings Equipment].ZSP, [Screenings Equipment].PSL, [Screenings Equipment].FSL, [Screenings
Equipment].TSH, [Screenings Equipment].MSX, [Screenings Equipment].SSL
FROM [Screenings Equipment];
UNION SELECT Screens.Table, Screens.Plant, Screens.[New Tag], Screens.[New Comment], Screens.[P&ID number],
Screens.Purpose, Screens.[Motor SIze], Screens.Starting, Screens.New, Screens.Comment, Screens.Switchboard,
Screens.PLC, Screens.[PLC Interface], Screens.ACH, Screens.IL, Screens.ZSO, Screens.WIO, Screens.ZSP, Screens.PSL,
Screens.FSL, Screens.TSH, Screens.MSX, Screens.SSL
FROM Screens;
UNION SELECT [Surface Aerator].Table, [Surface Aerator].Plant, [Surface Aerator].[New Tag], [Surface Aerator].[New
Comment], [Surface Aerator].[P&ID number], [Surface Aerator].Purpose, [Surface Aerator].[Motor Size], [Surface
Aerator].Starting, [Surface Aerator].New, [Surface Aerator].Comment, [Surface Aerator].Switchboard, [Surface Aerator].PLC,
[Surface Aerator].[PLC Interface], [Surface Aerator].ACH, [Surface Aerator].IL, [Surface Aerator].ZSO, [Surface Aerator].WIO,
[Surface Aerator].ZSP, [Surface Aerator].PSL, [Surface Aerator].FSL, [Surface Aerator].TSH, [Surface Aerator].MSX, [Surface
Aerator].SSL
FROM [Surface Aerator];
UNION SELECT Tanks.Table, Tanks.Plant, Tanks.[New Tag], Tanks.[New Comment], Tanks.[P&ID number], Tanks.Purpose,
Tanks.[Motor Size], Tanks.Starting, Tanks.New, Tanks.Comment, Tanks.Switchboard, Tanks.PLC, Tanks.[PLC Interface],
Tanks.ACH, Tanks.IL, Tanks.ZSO, Tanks.WIO, Tanks.ZSP, Tanks.PSL, Tanks.FSL, Tanks.TSH, Tanks.MSX, Tanks.SSL
FROM Tanks;
UNION SELECT [UV System].Table, [UV System].Plant, [UV System].[New Tag], [UV System].[New Comment], [UV
System].[P&ID number], [UV System].Purpose, [UV System].[Motor Size], [UV System].Starting, [UV System].New, [UV
System].Comment, [UV System].Switchboard, [UV System].PLC, [UV System].[PLC Interface], [UV System].ACH, [UV
System].IL, [UV System].ZSO, [UV System].WIO, [UV System].ZSP, [UV System].PSL, [UV System].FSL, [UV
System].TSH, [UV System].MSX, [UV System].SSL
FROM [UV System];
UNION SELECT Valves.Table, Valves.Plant, Valves.[New Tag], Valves.[New Comment], Valves.[P&ID number],
Valves.Purpose, Valves.[Motor Size], Valves.Starting, Valves.New, Valves.Comment, Valves.Switchboard, Valves.PLC,
Valves.[PLC Interface], Valves.ACH, Valves.IL, Valves.ZSO, Valves.WIO, Valves.ZSP, Valves.PSL, Valves.FSL, Valves.TSH,
Valves.MSX, Valves.SSL
FROM Valves;
I'm trying to develop a werehouse database for me.and I'm in a terrible dead end.Formely I have a table Articles where I have all the articles on the market with the description, EAN, price. and a table Movement where with a code (related with the article's table) I can store all the item I fisically have in my werehouse and items I sell. (with a boolen field that set if the item is an income or an outcome).
I have a form linked to the Movement Table.I'd like to write an EAN in a textbox query that EAN to the article table and output the results in a datasheet.
The datasheet is populated in a subform, based upon a table.
Code:
SELECT DEV_List.*
FROM DEV_List;
Within this datasheet, I have several combo boxes.It is easy to give them a query to show all values possible.
Code:
SELECT tbl_PPV_RPM.Createdby
FROM tbl_PPV_RPM
GROUP BY tbl_PPV_RPM.Createdby;
This gives me a list of all possible values in "tbl_PPV_RPM.Createdby".What I want, is to be able to select only the relevant values in this combobox, based upon a materialnumber in the same row. "tbl_PPV_RPM" contains a row named "Material". The datasheet also contains a row named "Material". These need to be linked. When I try to link them through the query builder, I get this:
Code:
SELECT tbl_PPV_RPM.Createdby
FROM tbl_PPV_RPM
WHERE (((tbl_PPV_RPM.Material)=[Forms]![frmSub_TD_List_Edit].[Material]))
GROUP BY tbl_PPV_RPM.Createdby;
But then I get a popup box, requesting for the Material number in "frmSub_TD_List_Edit".So, it does not recognise the Materialnumber in the row I'm trying to select a value.
I have a form that produces a datasheet that derives its information from a query. The query works fine giving the desired information.
In this datasheet on "PurchaseOrderID" i have a On Dbl Click event that states this.
Private Sub PurchaseOrderID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FrmPO_Received"
stLinkCriteria = "[ItemID]=" & Me.ItemID
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
Now it works sometimes, but sometimes it pulls up a different "PurchaseOrderID". Is there a way to fix this. I thought maybe there would be a way to put two criteria, "ItemID" and "PurchaseOrderID".
tell me if it is possible to calculate a value in a column in a datasheet view of a query. i.e. I want to take a value in 'Column A' and subtract the value in 'Column B' to create a value in 'Column C'?
View 2 Replies View RelatedI have a simple database with a query that "SUM"s an amount.
I created a form with a sub form that is linked by Account number. I want to be able to open the form in datasheet format and it to display the SUM value from the query.
When I open the form it responds with #Type until I click the + symbol to open the linked query then it updates the #type field with the data from the query.