Is there a way to have Access enter multiple entries to a database based on a form where the user has selected a certain criteria? I am hoping to have my form enter multiple entries within a table but each entry will have different information.
What I have is an event log which tracks all the versions of software which is being installed within our servers. I have four servers which “should” mimic each other therefore, when an application is installed or updated on one, the others also get updated (with a different sever name of course). Therefore when I enter an event (within my form) I would like it to actually enter four events within my database for each server.
I believe I need to add code to the “On Click” control but from there I have no clue on how to proceed. I am thinking that a case statement may be needed, am I way off base here?
Any help would be greatly appreciated.
Thanks,
Birky
7 2092 Rabbit 12,516
Recognized Expert Moderator MVP
For clarification.
Is it something like a table with 2 fields.
Tbl_Apps
ServerName Application
And you have 4 servers: Server1, Server2, Server3, Server4.
When you add one application to a server, you want 4 entries.
Server1 App1
Server2 App1
Server3 App1
Server4 App1
NeoPa 32,579
Recognized Expert Moderator MVP
Birky, please do reply to Rabbit's reasonable question.
But in a general reply to a general question, you could add some code that runs an INSERT INTO (Append) query for records from a table containing all the four servers and your software ID from the form being addedd into another table (where you want it) using a WHERE clause that selects (using In()) just those records that match the servers you're interested in.
To fill out the flesh we need MetaData of all relevant tables (and an answer confirming Rabbit's supposition).
Sorry about that, I should have provided more info. To answer Rabbit’s question the answer is yes, exactly. The form I have is tied to only one table (called Event_Log). This table only has a few fields in an attempt to keep it simple.
Event_ID (auto generated number which serves as the primary key)
Application_Nam e
Version
Date_Installed
Date_Recorded (auto populated without any user intervention)
Comments
The form basically has all these fields contained within. I have four servers name TEST1, TEST2, TEST3, and TEST4. So in an effort to save time and keystrokes, if were to add an entry for any one of these server I actually need an entry within the Event_Log for each Server with the added application or updated version.
To throw a monkey wrench into the mix; I also have other Servers within the database that do not fall within this scenario and should be tracked individually. This is why I was thinking of a case statement… Am I way off base here? Please correct me if I’m wrong but is not a case statement basically a where clause within VBA? I may be barking up the wrong tree for I am learning that there are easier ways to skin a cat (key word here is learning).
I do appreciate your help on this one..
Birky
NeoPa 32,579
Recognized Expert Moderator MVP
Birky, I think you misunderstand me. There is no cause to apologise as you'd not done anything at that point. I was merely trying to ensure you didn't miss Rabbit's post simply because I'd posted after it ;)
I'll leave you with an idea for the moment. Let me know if you manage to run with it and if not I'll fill in some more details later.
Think of a Server table (tblServer) which contains a record for each server. It also has a field to indicate whether or not a server should be auto-populated with your data. - Table Name=tblServer
-
ServerName; Text; PK
-
PopFlag; Boolean (Yes/No)
If you link this in with the idea I laid out in post #3, you should have a workable solution.
NeoPa , I’m truly lost here. Not sure if it is due to me being a novice or if I just can’t get my brain cell around this one. I am truly willing to learn if you’re willing to teach me. If you could break this down for me so I can grasp it I would be extremely grateful.
I have purchased several books in hopes of learning Access and VBA but none of them cover this type of scenario. Your help is much appreciated!
Thanks for taking the time…
Birky
MMcCarthy 14,534
Recognized Expert Moderator MVP
Birky,
My suggestion would be to use a multiselect listbox of servers. Then for each selection in the list a new record could be added with the other data from the form. Example:
Event_Log table has a field to hold server name also On the form:
lstServer (Name of a multi select listbox) - Lists all available servers
cboApplication (Name of combobox with list of possible applications)
Version (datatype number assumed)
Date_Installed
Comments
cmdAddEvents (Command button on the form) Code behind command button cmdAddEvents: -
Private Sub cmdAddEvents_Click()
-
Dim valSelect As Variant
-
Dim strValue As String ' to hold the values for the update
-
-
For Each valSelect In Me.listboxName.ItemsSelected
-
strValue = "'" & Me.lstServer.ItemData(valSelect) & "', '" & Me.cboApplication & "', " & Me.Version & ", #" & Me.Date_Installed & "#, #" & Date() & "#, '" & Me.Comments & "'"
-
DoCmd.RunSQL "INSERT INTO Event_Log (ServerName, Application_Name, Version, Date_Installed, Date_Recorded, Comments) VALUES (" & strValue & ");"
-
Next valSelect
-
-
End Sub
-
NeoPa 32,579
Recognized Expert Moderator MVP
NeoPa , I’m truly lost here. Not sure if it is due to me being a novice or if I just can’t get my brain cell around this one. I am truly willing to learn if you’re willing to teach me. If you could break this down for me so I can grasp it I would be extremely grateful.
I have purchased several books in hopes of learning Access and VBA but none of them cover this type of scenario. Your help is much appreciated!
Thanks for taking the time…
Birky
This is not as easy as I'd anticipated with a quick look earlier. It is possible, with a subquery built up from data on your form. We'll use these three fields (Assuming the form controls have the same names listed here.) : - Application_Name; Text
-
Version; Numeric
-
Date_Installed; Date
as illustrations. First of all we need to understand the concept of a SQL SELECT query with just literal values (See Literal DateTimes and Their Delimiters (#).). This will have no FROM clause at all! Also, we need to build this SQL string up in VBA code. - Dim strSQL As String
-
-
strSQL = "SELECT '" & Me!Application_Name & "' AS Application_Name," & _
-
Me!Version & " AS [Version]," & _
-
Format(Me!Date_Installed,'\#m/d/yyyy\#') & " AS Date_Installed"
We will now use this query as a subquery datasource in our INSERT INTO (Append) query. This will use an OUTER JOIN (otherwise known as unjoined) to the tblServer table from post #5. - strSQL = "INSERT INTO [Event_Log] " & _
-
"(Application_Name,[Version],Date_Installed) " & _
-
"SELECT subQ.* " & _
-
"FROM (" & strSQL & ") AS subQ,tblServer "
-
"WHERE tblServer.PopFlag"
With tblServer data of : - TEST1; Yes
-
TEST2; Yes
-
TEST3; Yes
-
TEST4; Yes
-
TEST5; No
-
TEST6; No
-
TEST7; No
-
TEST8; No
you would get entries added for TEST1; TEST2; TEST3 & TEST4 only. Adding another auto=populating server would be a simple matter of adding a record as Yes for a new server or changing an existing server to Yes from No.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Joe |
last post by:
Hey,
I'm going to give some background on my situation in case anyone can
point out a way around my problem altogether... for the problem
itself, please skip to the bottom of the post. thanks.
I've been having some problems with database performance... Several
threads are constantly attempting INSERTs of new records into a large
table - that is hundreds of thousands of records -large by my account
:-) The table has a VARCHAR field...
|
by: svdh2 |
last post by:
I have been looking at a problem the entire week in access, I have
still not been able to find a solution. Hope that you could maybe tell
where to look
Concerns the link between Access and Word. I can not transfer a report
to word without losing out on the lay-out (RTF format). I understand
that there is no way out
ok, mail merge I thought. But here I have the problem that I need to
merge multiple tables and that I can just include...
|
by: Ralph2 |
last post by:
Some time ago with a lot of help from this group I made a reasonably
successful database to keep track of our shop drawings. However the
searching mechanism is too complicated for the occasional user and I
would like to change the whole process.
I would like to duplicate the layout of my form... but each field
becomes a simple "searching" ListBox. On completing any one search
field all the rest are filtered to this value. Subsequent...
|
by: .Net Newbie |
last post by:
Hello,
I am currently coding my ASP.Net pages in c# and have run into a question
concerning Emails. I have four objects on a page (six including 2 buttons).
The first is a subject line (textbox) , the next is the email body (a
Textbox with multiple rows), an email address (textbox), and a DropDownList
containing multiple email addresses (populated from a SQL Server table).
The way this page functions is that a user types in the email...
|
by: Gummy |
last post by:
Hello,
I created a user control that has a ListBox and a RadioButtonList (and other
stuff). The idea is that I put the user control on the ASPX page multiple
times and each user control will load with different data (locations,
departments, etc.).
| |
by: franzey |
last post by:
This is how the data is organized:
vID Answer
12 Satisfied
12 Marketing
12 Yes
15 Dissatisfied
15 Technology
15 No
|
by: rameshbhojan |
last post by:
Hi All,
Please let me know if there is way in which i we can make multiple selections from a drop down list.
The requirement is as follows:
I have a drop down list, whose entries are to be populated from a table in the database. The user should be able to select multiple items from the drop down list(Maximum 3), and on pressing the submit button, a single column in a row, in the table should be populated with all the selected values....
|
by: Iain Wilson |
last post by:
Hi Can anyone please advise if this is possible and if so how I would
go about it (a pointer to an example would be greatly appreciated).
Using C# - Web Application
I have a DropDownList with the following entries
Room A
Room B
All Rooms
|
by: bonneylake |
last post by:
Hey Everyone,
Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place.
what i am trying to display previously entered multiple fields. I am able to get my serial fields to display correctly, but i can not display my parts fields correctly.
Currently this is what it does
serial information 1
parts 1
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |