Hello,
* Access 2007
* Little Experience with VBA
I will try and make this as clear as I can. ISSUE
I have a database that has information for Clients and their Computers.
I am trying to make a subform linked to each Computer
The Subform shows me the entire list of software titles in one box.
Box 2 shows me which titles have been installed.
I would like to pick which title(s) that are installed on each machine listed in box 2.
For instance
Matts Computer has AVG installed, Office 2007 installed and Foxit.
when I go back to Matts Computer Record, I would like to see which titles have been installed and easily update on the fly.
Box 1 showing all software titles and filtering out
( AVG installed, Office 2007 installed and Foxit. )
Box 2 showing which titles are already installed [3]
( AVG installed, Office 2007 installed and Foxit. )
From Box 1 I can select, multiselect titles and update BOX 2.
Please help
thank you
Gcoaster
14 3649
I think I understand. - Function yourFunctionName()
-
Dim db As DAO.Database
-
Dim rs1 As DAO.Recordset
-
Dim rs2 As DAO.Recordset
-
-
Set db = CurrentDb()
-
Set rs1 = db.OpenRecordset("Query1")
-
Set rs2 = db.OpenRecordset("Query2")
-
-
If rs1.RecordCount=0 Then Exit Sub
-
-
rs1.MoveFirst
-
' loop through each record in the first recordset
-
Do Until rs1.EOF
-
' If matching record is found then update field in
-
' second recordset to value you determine
-
If rs2.RecordCount=0 Then Exit Sub
-
rs2.MoveFirst
-
Do Until rs2.EOF
-
If rs1![FieldName] = rs2!FieldName Then
-
rs2.Edit
-
rs2![FieldName] = 'Your Value'
-
rs2.Update
-
End If
-
rs2.MoveNext
-
Loop
-
rs1.MoveNext
-
Loop
-
-
rs1.Close
-
rs2.Close
-
Set rs1 = Nothing
-
Set rs2 = Nothing
-
Set db = Nothing
-
-
End Function
Hello Rabbit,
Have read some of your posts, you are a master! cool..
this is how far i have gotten. left to right is not working. please help
thank you -
Option Compare Database
-
Option Explicit
-
Private Sub btnRight_Click()
-
If Not IsNull(Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex)) Then
-
CurrentDb.Execute "UPDATE MACHINE_SOFTWARE SET installed=Yes WHERE machineSoftwareID=" & Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex) & ";"
-
Me.Refresh
-
Else
-
MsgBox "Please select an entry"
-
End If
-
End Sub
-
Private Sub btnRightAll_Click()
-
CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed=Yes;"
-
Me.Refresh
-
End Sub
-
Private Sub btnLeft_Click()
-
If Not IsNull(Me.lbxRight.Column(0, Me.lbxRight.ListIndex)) Then
-
CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed=Yes WHERE softwareID=" & Me.lbxRight.Column(0, Me.lbxRight.ListIndex) & ";"
-
Me.Refresh
-
Else
-
MsgBox "Please select an entry"
-
End If
-
End Sub
-
Private Sub btnLeftAll_Click()
-
CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed=No;"
-
Me.Refresh
-
End Sub
-
Private Sub Form_Current()
-
Dim dbs As DAO.Database
-
Dim sSQL As String
-
Set dbs = CurrentDb()
-
' 1) Delete and then update records
-
sSQL = "DELETE FROM TEMP_SOFTWARE"
-
dbs.Execute sSQL
-
sSQL = "INSERT INTO TEMP_SOFTWARE ( title )SELECT SOFTWARE.title FROM SOFTWARE"
-
dbs.Execute sSQL
-
Me.Refresh
-
End Sub
Rabbit 12,516
Recognized Expert Moderator MVP
I think I misunderstood your original intention. I originally thought you had multiple records for each computer and wanted to create a single string. However, that does not seem to be the case. What is the structure of your software table?
Rabbit 12,516
Recognized Expert Moderator MVP
If I understand correctly you have one record for each piece of software on each machine. And what you want to do is have 2 list boxes, one lists all software that the chosen machine does NOT have and the other lists the software it does have. Then the user can select one or more software from either listbox, click a button, and either create new records or delete records and requery the listboxes?
If I understand correctly you have one record for each piece of software on each machine. And what you want to do is have 2 list boxes, one lists all software that the chosen machine does NOT have and the other lists the software it does have. Then the user can select one or more software from either listbox, click a button, and either create new records or delete records and requery the listboxes?
Thank you Rabbit,
Yes EXACTLY!!!
by the way,
I am the user, Only I will be using this. I am trying to get away from outlook2007 +bcm.
Rabbit 12,516
Recognized Expert Moderator MVP
Displaying the items for the listboxes is easy. For the listbox with uninstalled software, you use an unmatched query, for the other, a regular select query will suffice. Just make sure you take into account the computer in question. You'll also want to rebuild the control source string for each listbox in the Current event of the form.
As for moving software back and forth, you'll have two command buttons, one to add and one to delete. If you have a multi-select listbox then you'll have to loop a DoCmd.RunSQL. Then you want to requery both listboxes
Hello Rabbit,
thank you for your on going support.
I took a different route this time, things I changed where some of the table and form names to make it esier, learned its important working with VBA NOT to name the form and table the same. it gets confusing! .
almost there now, one error is keeping me. and that is deleting software from right. learning about the DoCmd.RunSQL
Am I doing things right? thank you agian. - Private Sub Form_Current()
-
-
Me.lstInstalled.RowSource = "SELECT MACHINESOFTWARE.title FROM MACHINESOFTWARE " & _
-
"WHERE MACHINESOFTWARE.client = forms![MACHINE]!cboFullName"
-
-
-
Me.lstSoftware.RowSource = "SELECT tblSOFTWARE.title FROM tblSOFTWARE " & _
-
"WHERE tblSOFTWARE.title NOT IN " & _
-
"(SELECT MACHINESOFTWARE.title FROM MACHINESOFTWARE " & _
-
"WHERE MACHINESOFTWARE.client = forms![MACHINE]!cboFullName)"
-
-
End Sub
-
-
Private Sub lstInstall_Click()
-
If IsNull(Me.lstInstalled) And Not IsNull(Me.lstSoftware) Then
-
-
'Dim MyMessage
-
'MyMessage = MsgBox("This will add a new installation to this record. Continue?", vbYesNoCancel)
-
'If MyMessage = vbYes Then
-
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
-
Set rs = Me.RecordsetClone
-
-
With rs
-
-
.AddNew
-
-
!client = Forms!machine!cboFullName
-
!machineName = Forms!machine!machineName
-
!title = Me.lstSoftware
-
-
.Update
-
-
End With
-
-
Me.lstInstalled.Requery
-
Me.lstSoftware.Requery
-
-
Me.lstInstalled = Null
-
Me.lstSoftware = Null
-
-
Set rs = Nothing
-
-
End If
-
'Else: MsgBox "Please select only a software to install", vbOKOnly
-
'End If
-
-
End Sub
-
-
Private Sub lstRemove_Click()
-
-
If IsNull(Me.lstSoftware) And Not IsNull(Me.lstInstalled) Then
-
-
Dim MyMessage
-
MyMessage = MsgBox("This will delete the selected software's record from this machine. Continue?", vbYesNoCancel)
-
If MyMessage = vbYes Then
-
-
DoCmd.RunSQL "DELETE * FROM MACHINESOFTWARE WHERE MACHINESOFTWARE.client =Forms!machine!cboFullName AND " & _
-
" MACHINESOFTWARE.machineName =Forms!machine!machineName AND " & _
-
" MACHINESOFTWARE.title =Forms!MACHINEsoft!lstInstalled "
-
-
Me.lstInstalled.Requery
-
Me.lstSoftware.Requery
-
-
Me.lstInstalled = Null
-
Me.lstSoftware = Null
-
-
End If
-
Else: MsgBox "Please select only a software to Uninstall", vbOKOnly
-
End If
-
-
End Sub
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Sue Adams |
last post by:
I actually have two issues/questions:
I have an autonumber field in an access db table that I grab and later use to update a record in another table withing the same db.
The code I use to get it from the db table is:
''Retrieve the Registration Identification Number
strRegisterID = Rs("Register_ID")
Prior to testing my code and actually updating the db, I''m trying to write it to the page to make sure their isn''t a loop or massive...
|
by: jimserac |
last post by:
I had previously posted this in an Access forum
with negative results so will try here.
Although this question specifies an Access database,
I also wish to accomplish this with a large MS SQL Server
database that we have.
Question follows:
The following SQL statement, used in VBScript,
|
by: Steve Jorgensen |
last post by:
Hi all,
Over the years, I have had to keep dealing with the same Access restriction -
that you can't update a table in a statement that joins it to another
non-updateable query or employs a non-updateable subquery. I run across this
problem again and again, and yet I've never come up with a single, universal
work-around.
I'm wondering what other people here are doing.
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my predecessor, I hasten to add) so that each day it creates a
copy of the record for each company, changes the date to today's date,
and prompts the user for any changes of ratings on that day. The
resulting data table grows by approx 600 records per...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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: 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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |