Form’s Date Value not able to bring it over to VBA

I have a query, called “RosterQC” that filters results based on the text object “Text35” in the Form “Quality”:

SELECT Record No FROM TableX WHERE Date = [Forms]![Quality]![Text35]

Running the query itself is fine, but when I try to run it on VBA. It gives me an error 3061 – too few parameters expected 1. My VBA code as of below,

Sub Duplicate() DoCmd.RunSQL “Delete * from old_table” CurrentDb.Execute “Insert Into old_table Select RosterQC.* From RosterQC” DoCmd.OpenReport “RosterQC_Report”, View:=acViewPreview End Sub

Appreciate if someone please give help me by providing me with some insights or solutions? Thanks.

submitted by /u/georgetown15
[link] [comments]

Posted on

"you are about to append 535920 row(s)". Only trying to append 870, WTF?

Having major issues importing 4 columns from Excel. 1 name, 2 with data (numbers) and 1 with date.

Been trying the following: – Importing to another table and appending. 535920 rows. Nope.

  • importing directly to the table. 535920 rows. No thx.

  • Copy pasting into another fresh table and then appending. No dice.

  • Copy and pasting as value to a fresh excel sheet and done above steps all over again. 535920 rows. W. T. FFF.

OK, fine. Append the shit. It works, but I get an error message saying that “0 rows couldn’t be added” OK, whatever. Now I just need to update a 5th column with IDs of the corresponding names. Nope, nothing happens. Access doesn’t seem to recognise these added lines. When searching by query, I only get matches of the old lines, not the newly append ones.

Wtf is up with this? I just want to add some lines to 4 stupid tables… Oh, and the type matches for all. And I have tried removing the key from the “main” table. Not working.

submitted by /u/Diamondsinmyheart
[link] [comments]

Posted on

Searchbox that that searchs multiple columns?

This is my first post here I hope I’m well understood.

I’m currently building a database with two tables(hopefully will expand more) one table for contacts info and one for evaluation. I have a data entry form and an evaluation form. I need to have a search box with following features for my evaluation form: 1. If the user entered a phone number, address or name etc. And hit enter. A second form should pop up showing relevant results. 2. That second form should also have a search box that basically filters the data based on what the user types (like a second level search) and dynamically shows the results. 3. The user them double clicks the desired contact And loads their ID to the Evaluation from for more data to be entered.

Are these ideas doable in Access If they how should I go about it ? Any help is very appreciated.

submitted by /u/seekingtruth2
[link] [comments]

Posted on

Trying to get Median value for a dataset in Access

This is my first post so hopefully I don’t mess anything up.

I’m using Microsoft Access to build a database of chemicals occurring in several different environmental sample types (water, sediment, fish species, etc). I ran a query that required some SQL to return the average Result Value sorted by Chemical Name and Sample Type, and it worked out just fine. My collaborators would also like to see the Median of Result Value sorted with the same criteria (i was able to add Min and Max values pretty easily, but median isn’t one of the choices in Total:). Is there any way to do this? I have the DB open so I can share screencaps or code if needed.


[link] [comments]

Posted on

Question about linking Customer names to Orders tables

So i have a Customers Table where each customer has a unique Primary Key number. I also have a Orders Table where i have to enter in the Customers name, but i have to choose a Primary Key number instead.

How can i effectively fill out a row without having to look up the Customer’s Primary Key.

Lets say:

I have John Smith’s information filled out in the Customers Table. His primary key is #843, but I do not have this memorized.

I enter in the order information in the Orders Table (or form). I can’t enter in John Smith’s name, i have to enter in his primary key to link the information. Is there a better way of doing this? I assume i would have to do the same thing to choose what product they want from the Orders Table. Each product would have its own Primary Key number that i would have to memorize to enter in order information

submitted by /u/Colbehz
[link] [comments]

Posted on

"you are about to append 535920 row(s)". Only trying to append 870, WTF?

Having major issues importing 4 columns from Excel. 1 name, 2 with data (numbers) and 1 with date.

Been trying the following: – Importing to another table and appending. 535920 rows. Nope.

  • importing directly to the table. 535920 rows. No thx.

  • Copy pasting into another fresh table and then appending. No dice.

  • Copy and pasting as value to a fresh excel sheet and done above steps all over again. 535920 rows. W. T. FFF.

OK, fine. Append the shit. It works, but I get an error message saying that “0 rows couldn’t be added” OK, whatever. Now I just need to update a 5th column with IDs of the corresponding names. Nope, nothing happens. Access doesn’t seem to recognise these added lines. When searching by query, I only get matches of the old lines, not the newly append ones.

Wtf is up with this? I just want to add some lines to 4 stupid tables… Oh, and the type matches for all. And I have tried removing the key from the “main” table. Not working.

submitted by /u/Diamondsinmyheart
[link] [comments]

Posted on

Storing a database online

I’m trying to help some old work colleagues with their database, I really don’t understand much so please bear with me.

The database is, it was made by somehow who is no longer with the company, I did my best to keep it running when I worked there, it’s still using MSAccess 2003.

We had database files stored on a server so that everyone could access it, however the company is downsizing and want to use the database on the move.
They’ve tried hosting the database files on something like onedrive, but the file cannot save if it’s in use.

Is there another online service they could use that would allow everyone to use and update the database at the same time?

submitted by /u/DEL_707
[link] [comments]

Posted on

Subreport cutting across pages at bad location

So, I’m not very good in Access and cannot figure out a solution to a very annoying issue… I have a report that has two levels of subreports. The problem I’m running into is that when the Main Report is opened, one of the grandchild subreports gets broken in between pages at a bad spot. Basically, the header of the subreport is split between pages (top half on current page and bottom half on next page). I’ve set the “KeepTogether” property on “Yes” for that particular header, but that doesn’t seem to work.

One work around I considered was creating a section in the parent report to contain only that problem subreport and setting that parent section “KeepTogether” property to “Yes”. However, the problem I run into there is that it pushes the subreport to a new page no matter what, leaving a good possibility of the page only having a bit of text at the top (leaving a good amount of wasted empty space for the remainder of the page).

So, after that, I attempted another idea. I created a field in the “Yes/No” field in the parent report with the intent that it will determine whether the parent “KeepTogether” property is “True” or “False”. But my VBA capabilities are limited and was unable to write a code that would work to successfully execute this concept…

Lastly, I attempted yet another attempt idea, which was similar to the last. This time, the “Yes/No” field would determine if the “KeepTogether” property of that particular GroupLevel of the section in the parent report. So, if the field was set to “Yes”, my intent was that the GroupLevel KeepTogether property would be set to ‘1’ (Keep whole group together). However, that didn’t work because the OnOpen event for a report happens before any of the data is called. As such, when I attempt to open the report, I get an error stating that it can find the specified field.

I’ve exhausted myself with all the research I’ve done on this and am just about to give up… I’m hoping somebody in this sub would be able to help me find a solution for this? Hopefully I’ve explained my issue clearly enough. Thanks in advance.

submitted by /u/demax182
[link] [comments]

Posted on