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 3590
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
Rabbit 12,516
Recognized Expert Moderator MVP
I don't see anything explicitly wrong with the code. What's the problem exactly? What's not working the way it should or what is it doing that it shouldn't be doing?
The problem is here somewhere ( well at least i think! ) - DoCmd.RunSQL "DELETE * FROM MACHINESOFTWARE WHERE MACHINESOFTWARE.client =Forms!machine!cboFullName AND " & _
-
" MACHINESOFTWARE.machineName =Forms!machine!machineName AND " & _
-
" MACHINESOFTWARE.title =Forms!MACHINEsoft!lstInstalled "
The error is this when cmdRemove is clicked
Here is what pops up
| Enter Parameter Value |
[ Forms!MACHINEsoft!lstInstalled ]
Rabbit 12,516
Recognized Expert Moderator MVP
That error usually means you misspelled something or referenced the control incorrectly. If the control's on a subform, then the syntax is a bit different. Also, the form needs to be opened.
That error usually means you misspelled something or referenced the control incorrectly. If the control's on a subform, then the syntax is a bit different. Also, the form needs to be opened.
Fixed it Rabbit, Thank you! you where right..
Rabbit 12,516
Recognized Expert Moderator MVP
Not a problem, good luck.
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...
|
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...
|
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...
|
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...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
|
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,...
|
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...
|
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,...
|
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...
|
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...
|
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 ...
| |