Because I am a beginner in Access, I need your help

[GPGeorge]

I see that all I get on this blog are topics such as expression class in school only, to no avail

We would love to help you create a usable, effective relational database application. Most of the advice so far is aimed at that goal.

The one thing professional, seasoned, committed developers are reluctant to do is enable inappropriately designed, inefficient, error-prone solutions.

You don't want to hear their advice.

Perhaps an analogy will help you understand. MajP previously mentioned things like running with scissors. Here's another one.

You have a pile of bricks that you want to use to build a house. You ask for help stacking them up without mortar to hold them in place. No responsible builder will do that because they know the inevitable catastrophe that will result. So all of the builders tell you, "Use mortar to build the house."

And you continue to insist that you do not want to do it that way; someone should just build the house out of bricks without mortar and let you worry about when it falls down.

Please, invest some time learning how relational database applications are supposed to be built and apply that knowledge to this problem. In the long run, you'll have a usable, stable, dependable solution, even if it's different from the original plan.

 

[MajP]

I looked at this, and as far as I can tell the code works perfectly. I am unsure what the issue is. The way it is designed works better than proposed solution in the GIF. I did modify it so that the currently selected values show up in the pop up when the popup opens. Maybe that is the fix you need.

IMO this current design is better than what you want to change to.
In the current design you have the benefits :
1. There is no editing in the textbox to add or delete.
2. The pop up loads with the current selections.
3. To delete a selection un click it
4. You have a choice to update the results or simply close the form

In your proposed solution you have the downfalls:
1. You have to edit in the textbox which is problematic
2. you have to removed previously selected values from the list.
3. You cannot do multiple changes at once

To show the selections

Public Sub PopulateListBox(FormName As Form, ctlName As Control)
  Dim strIn As String
  Dim aResults() As String
  Dim i As Integer
  Dim j As Integer
  Dim lst As Access.ListBox
 
  Set lst = Forms(ListBoxFormName).Controls("Resultlist")
  strIn = ctlName.Value
 
  If Forms(ListBoxFormName).Controls("Resultlist").MultiSelect = 1 Then
    aResults = Split(strIn, vbCrLf)
    For i = 0 To UBound(aResults)
       For j = 0 To lst.ListCount - 1
         If lst.Column(0, j) = aResults(i) Then lst.Selected(j) = True
       Next j
    Next i
  Else
 
  End If
End Sub

call this from OpenListboxForm
PopulateListBox FormName, FormName.ActiveControl

So why do you want to change the behavior?

 

[Safaa]

If you could apply to the file, I would be very grateful because I am exhausted and do not have time and my husband is coming from work now and I am preparing dinner for him.

 

[Safaa]

Public Sub PopulateListBox(FormName As Form, ctlName As Control) Dim strIn As String Dim aResults() As String Dim i As Integer Dim j As Integer Dim lst As Access.ListBox Set lst = Forms(ListBoxFormName).Controls("Resultlist") strIn = ctlName.Value If Forms(ListBoxFormName).Controls("Resultlist").MultiSelect = 1 Then aResults = Split(strIn, vbCrLf) For i = 0 To UBound(aResults) For j = 0 To lst.ListCount - 1 If lst.Column(0, j) = aResults(i) Then lst.Selected(j) = True Next j Next i Else End If End Sub

Please if it is possible to apply the code to the file

 

[Safaa]

There are two reasons: Firstly, I want the values to move directly while double-clicking on the chosen value without using the update button. Also, using the image method, I will be able to arrange the chosen values, whichever is first and which is second, and this does not happen in my method.

 

[Safaa]

Ok this allows you to click and select and order the values as selected.
I do not know how you plan to delete values because, you will run into trouble if you allow the user to edit the textbox. So I added a clear button but that clears the whole list.

Please, is it possible to file the file after modifying the code?

 

[Safaa]

Sir, I thank you

MajP​

very much and I appreciate your effort. You are a very cooperative, kind and patient person. Therefore, I hope to merge code for the following unbound fields: Reportname, fixeddefault, fixednormal.
With the module whose values come from the Fixed_tbl table in the following code



Private Sub UpdateFields(FieldName As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String

Set db = CurrentDb
sql = "SELECT * FROM Fixed_tbl WHERE fixedname = '" & FieldName & "'"
Set rs = db.OpenRecordset(sql)

If Not rs.EOF Then
rs.MoveFirst
Me.testnameN = FieldName & subName

If Not IsNull(rs! fixeddefault) Then
Me.default = rs!fixeddefault
Else
Me.default = ""
EndIf

If Not IsNull(rs! fixednormal) Then
Me.Normal = rs!fixednormal
Else
Me.Normal = ""
EndIf

If Not IsNull(rs!Reportname) Then
Me.Reportname = rs!Reportname
Else
Me.Reportname = ""
EndIf
Else
'If no record is found, set the fields to empty
Me.testnameN = FieldName & subName
Me.default = ""
Me.Normal = ""
Me.Reportname = ""
EndIf

rs. Close
Set rs = Nothing
Set db = Nothing
End Sub

I was previously putting the code in an event on got vocus but now found another code

 

[Safaa]

Was it possible to add it to this part of the code?


' Function to update fields based on the active control's name
Public Function UpdateFields(frm As Form, targetFieldName As String, fieldName As String)
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim fieldSuffix As String

' Determine the appropriate suffix based on the form name
If InStr(frm.Name, "Urine") > 0 Then
fieldSuffix = "(Urine)"
ElseIf InStr(frm.Name, "Stone") > 0 Then
fieldSuffix = "(Stone)"
ElseIf InStr(frm.Name, "CSF") > 0 Then
fieldSuffix = "(CSF)"
ElseIf InStr(frm.Name, "Stool") > 0 Then
fieldSuffix = "(Stool)"
Else
fieldSuffix = ""
End If

Set db = CurrentDb

' Construct the SQL query based on the suffix, if any
If fieldSuffix <> "" Then
sql = "SELECT * FROM Fixed_tbl WHERE fixedname = '" & fieldName & fieldSuffix & "'"
Else
sql = "SELECT * FROM Fixed_tbl WHERE fixedname = '" & fieldName & "'"
End If

Set rs = db.OpenRecordset(sql)

If Not rs.EOF Then
rs.MoveFirst
frm.Controls(targetFieldName).Value = rs!fixedname
End If

rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

ErrorHandler:
' Handle errors gracefully (optional)
End Function

 

[Safaa]

Do all forms calling this function have controls with these names? If so yes.

testnameN
default
Normal
Reportname

yes

 

[Safaa]

??

 

[Safaa]

Thanks

 

[Safaa]

After trying the file, I think that there is a simple error, as the value of the Reportname field was replaced with the value of the Fixedname WHICH equal testnamen field. Is it possible to modify it for me?

 

[Safaa]

Field values are replaced with each other

 

[Gasman]

After trying the file, I think that there is a simple error, as the value of the Reportname field was replaced with the value of the Fixedname WHICH equal testnamen field. Is it possible to modify it for me?

Little bit much asking for every little amendment to be made by someone else?
We are here to help, not write systems for people. A few people here will write the code, and then they get the O/P coming back saying 'that works great, now I would like to....' That appears to be your intention?

How about making a few changes yourself and learning a little?

This is what comes from just being handed code on a plate and not even bothering to try and understand it. :-(

 

[MajP]

I called the function incorrectly. You need to pass the controls in the correct order. I passed in the report and testname in wrong order.

=FillUnboundControls([Form],"(Stool)",[color],[TestnameN],[Reportname],[Default],[Normal])

You need to fix it so the [testN] comes first
This one is wrong see how report name is first

=FillUnboundControls([Form],"(Stool)",[odor],[Reportname],[TestnameN],[Default],[Normal])

Fix this in the gotfocus event of each control.

 

[Safaa]

Just for your information, the value of the TestnameN field = the value of the Fixedname field. I want that when I open the form that contains the listbox, the listbox is not filtered based on the value of the Testname field, but the filtering is done based on the fixedname field, whose value comes from the previous form, the TestnameN field.

 

[Safaa]

I called the function
=FillUnboundControls([Form],"(Stool)",[color],[TestnameN],[Reportname],[Default],[Normal])
give me error

 

[Safaa]

Little bit much asking for every little amendment to be made by someone else?
We are here to help, not write systems for people. A few people here will write the code, and then they get the O/P coming back saying 'that works great, now I would like to....' That appears to be your intention?

How about making a few changes yourself and learning a little?

This is what comes from just being handed code on a plate and not even bothering to try and understand it. :-(

I really want to learn, but now I am busy with my field in medical analysis. I am only making a system that enables me to work in my laboratory.

 

[Safaa]

I called the function
=FillUnboundControls([Form],"(Stool)",[color],[TestnameN],[Reportname],[Default],[Normal])
give me error

Sorry, it's my mistake

 

[MajP]

I cannot see the mistake, but the message says it is likely a simple typo. For example if one of those commas is a period or you forgot a " around a string. However, it looks correct to me.

If Every form that used this function was the same, you could make calling this a lot easier, but I followed what the original author did.

If the name was the same consistently then no need to pass in anything except the Suffix. The original author did not pass in the suffix but that forced the form names to be exact. You then simply could make the call
=FillUnboundControls("(Stool)")
You would then simply capture the unbound controls and know all the return fields by name.

My problem is I am only seeing part of the application, and if I do not follow what the author did I can break other things.