Multiple table entries upon single selection

[Birky]

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

[Rabbit]

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]

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).

[Birky]

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_Name
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]

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.

[Birky]

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]

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]

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.