Solved New to vba code

[HelpNewtoAccess]

Hi Micron,
Hopefully this will be enough to give you an overview of what is going on.

 

[Deleted member 147267]

Doesn't help with what's going on behind those pics.
The comments made by me and others are pretty basic: normally if you can, your form controls are bound to either a table or query. I suspect you know what a bound form is, so really, that's all there is to it. If you can't do that for some reason and are happy with the way the db is performing you might as well carry on with what you've got because it sounds to me like posting a db copy isn't a viable option.

 

[Isaac]

Have you tried pbaldy's suggestion? You don't write code to open the form to a hardcoded value, like 148, you code it to open to whatever ID value is on the main form. Changing your hard coded approach, to:

DoCmd.OpenForm "frmEquipInfo", acNormal, "", "[PointID]=" & ME.[id], , acDialog

 

[HelpNewtoAccess]

I would post a copy of the db but it has confidential information in it besides this part of it.

I have a bound form (which I bound to a query)…….
Field names for query...I am working with on my form are PointID and Notes
PointID - records are 1 to 20
Notes - have a note or no note for records 1 to 20

Form has pictures of pumps 1 to 20 each having two command buttons (This is what I would like instead of the three I have now)
-one to click and bring up another form to a specific record for that pump only
-the other command button is beside the pump with no code changing from visible to not visible (indicating a note or not)
If you enter a note on the second form it would cause the flag to be visible on the first

I added the existing fields for Notes and PointID to the form and it only works for one record and one note... The first record in the query..
This is probably very easy but can someone dumb this down for me and show me how to do this with code. I want to do something like this

Micron Code

Private Sub Form_Current()
On Error GoTo Form_Current_Err

Dim i As Integer

For i = 1 to 5
Me.Controls("cmd" & i & "n").Visible = not Nz(Me.Controls("txt" & i),0) = 0
Next

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Form_Current_Exit

End Sub

or

Private Sub Form_Current()

If IsNull(Notes) Then
Me.cmd1n.Visible = False
Else
Me.cmd1n.Visible = True
End If

End Sub

 

[Deleted member 147267]

I added the existing fields for Notes and PointID to the form and it only works for one record and one note... The first record in the query..

What only works for the 'first' record, the button visibility thing? That makes no sense unless maybe the field is not Null but contains an empty string. If that is the case I think the Nz expression will not do what you want. You can either check if there are zls in your data by querying this table field with "" as criteria (if you get any results, then they are empty strings). You could either remove them or alter the expression. The question would then be, how did they get there? Probably from imported data.
Altered expression:
Me.Controls("cmd" & i & "n").Visible = not Nz(Me.Controls("txt" & i),"") = ""

Or is it the DLookup that only works for one record? From what I read in your thread, the only example of that is where the criteria is hard coded (148?) so don't expect a different lookup for different records. I believe that was already covered here.

 

[HelpNewtoAccess]

So originally how I had everything was a bound form with dlookups. I had 20 text boxes on the form with the dlookups for each pump. That means 20 lookups each looking at the Notes field in the query for a single record for each pump and bringing the note into that small textbox on the form. Then based on that data if it was null or not would cause the flag to show for that pump...………………………..

I then tried having one text box bound to the notes field from the bound query to the form. This only shows the first note in the recordset it then switches all the note flags invisible or visible based on one record instead of one record for one flag. So how do I go about this? Do I still need a text box for all the records. I need the note flag for each pump just to look at the individual record and decide if the field is null or not then show the flag.
Hopefully I'm explaining this properly..
The code you sent works great with the dlookups but I don't want to have to use the dlookups...

 

[Deleted member 147267]

I still don't grasp the situation.
Try to imagine you understanding the workings of a db that supports a complex function which you may know very little about and trying to design this from strictly verbal input from us. That is what we have here. You have said that you cannot post a db copy and we respect that, but unfortunately that seems to be a deal breaker as I seem to be the only one left responding. If you can't randomize your data to 'protect' it (I think we have a post here on something you can do for that if you're interested) and still make the db work, then I think you'll have to be happy with your lookups, assuming they still work.

 

[HelpNewtoAccess]

I've have attached a stripped down version of the db so you can see what I am talking about. Thanks

 

[Deleted member 147267]

Sorry, been busy and will be for the next few days but can try to look at this if it rains or I quit woodworking early enough at night. TBH, when I saw that late in the day and that there were 2 downloads already I figured I was way behind 2 others so just waited to see what they came up with ( I hope they don't think I own this thread or something!). I might post small contributions in the forum during a coffee break, but I have put off anything that requires a bit of study. If you're no longer interested in making changes, please mark your thread as solved so that we don't spend time evaluating something you don't need.
Thanks.

 

[cheekybuddha]

I couldn't open it with A2k7 :-(

 

[HelpNewtoAccess]

Not zipped.. still trying to get a solution for using the dlookups or any other ideas. I checked it and it seems to work fine when downloading it. Thanks

 

[cheekybuddha]

I could d/l it, just can't open db's created in most recent versions of Access.

I *think* you can try and save it as Access 2007 compatible or something like that.

 

[HelpNewtoAccess]

Sorry but won't let me save as a previous version. Thanks for trying

 

[MajP]

I looked at this and you need to STIOP everything and fix your table structure. It makes absolutely no sense to me. You need to learn about primary and foreign keys. You have some mixed up version of both. If you do not fix this then you will create a disaster in the long run.

For example your Notes table has a field called AutoNumberID which should be the PK and it should be called NoteID. Then it should have a foreign key relating back to a piece of equipment. Instead it has what is called a NoteID which is in fact a foreign key identified as a PK to an equipment ID which in the equipment table is not a primary key. This problem goes through all the tables. This is not how you use keys. This is a disaster in the making.

The primary key in a table always gets listed first. Unless there is a very specific reason use an autonumber. The foreign key relates back to the primary key. You should not be linking primary to primary except in very specific cases (splitting a table for security). I recommend for you that you follow the foreign key with the letters _FK so you know which is a foreign key. Example EquipmentID_FK.

In your table I would then expect to see the PK with a name similar to the table name
NoteID ' autonumber field
EquipmentID_FK ' foreign key relating back to the equipment table.

TblEquipment
EquipmentID ' primary Key listed first and should be an autonumber unless there is a specific reason to use a natural key.

 

[MajP]

Here is one

Creating Primary Keys and Foreign Keys in Microsoft Access | MS Office User

Creating Primary Keys and Foreign Keys in Microsoft Access

msofficeuser.com

I would recommend that you do not name the foreign key the same name as the primary key without adding the subscript. This will make it a lot clearer to you. So the foreign key should be EquipmentID_FK and not simply EquipmentID.

 

[HelpNewtoAccess]

I just threw it together quick but thanks for all the input. Thanks for explaining things in detail much appreciated. I posted a cleaned up version hopefully this looks ok (missed _FK on PointID). I don't know if you have any ideas about changing how I get the note flag to appear instead of having to use all the dlookups?

 

[MajP]

This demo will work with your tables as they currently are, but the tables are not correct. The demo proposes a manageable solution for hundreds of items. Each of the sets of command buttons refer to a piece of equipment. Therefore use the tag property in the buttons. Example Cmd1l and Cmd1n belong to ID 610.
To check if a note exists.

Public Function HasNote(NoteID As Long) As Boolean
  HasNote = DCount("*", "tblNotes", "NoteID = " & NoteID & " AND trim(Notes & ' ') <> '' ") > 0
End Function

To get the proper record you need a single function and no event procedures

Public Function OpenDetails()
  DoCmd.OpenForm "frmSearchAfc", , , "PointID = " & CLng(Me.ActiveControl.Tag), , acDialog
  ShowHideButtons
End Function

To show and hide the buttons

Private Sub ShowHideButtons()
  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
    If ctrl.ControlType = acCommandButton And Right(ctrl.Name, 1) = "n" And ctrl.Tag <> "" Then
      ctrl.Visible = HasNote(ctrl.Tag)
    End If
  Next ctrl
End Sub

So for each set of command buttons just tag with the equipmentID. No additional code is needed.
Understand that this is just a conceptual demo of how you would do it, but this is only a band-aid on a sucking chest wound. I would not waste any time on this until you completely fixed the table structure.

 

[HelpNewtoAccess]

Thanks a million that's awesome... You saved me a ton of work with the dlookups. How everything is set up is great and such a time saver..
So I adjusted the table in the relationship like the diagram below. Would this be good or do you still see some issues with it? I made sure to have all the primary keys as autonumbers except the natural key with the building abbreviations. Thanks again for your time..

 

[MajP]

I am still having a hard time understanding this, but I do not know your Process.

1. I would Think a Building has several different graphics related to it. So a one to many is fine like you have it.
in TblBuildings the building abbreviation is fine as a PK if that is something used routinely. As long as the abb never changes, and is unique. I would still use the name ID with it to make it clear.
I would still call it BldgAbb_ID so you know this is the ID and PK. You have a field called PropertyID. If that is a foreign key to something else (A property can have many buildings) that is OK (I still like PropertyID_FK in your case to make this very clear).
2. In the Graphic table I would use the name for the bldg FK to be
BldgAbb_ID_FK (just to be super clear). In graphic table you are not currently using an autonumber. Using your own number is OK, but only do it if there is a really good reason. Most people would make an autonumber for the key and a seperate field like GraphicNumber (1300) if you need to store this information. Autos are just easier than rolling your own.
3.TblBasPoint I do not understand, but a Single Graphic can have many bas points.
Maybe you can explain this

4. TblNotes
This is where I am lost. What is a note related to and can you have only one note or multiple notes for an item.
Originally this was set up in a one to one related to a basPoint. But in reality it looked like you want to add a note to a piece of equipment.
If it is one note for a piece of equipment, then the note field simply goes in the the equipment table. If you want to be able to add many notes for a piece of equipment then you need a notes table.
If a note is related to a bas point then I think you need to break the link to equipment.
My guess a note is related to a bas point or equipment, but not both like you have it.

5.My guess is you are missing a junction table to do a many to many relationship. I think a piece of equipment could be related to many graphics.

6. Is an asset tag an identification of a specific piece of equipment? In other words Equipment describes a generic pump of type A. Where asset 123 is a Pump A with asset number 567 and asset 124 is also a Pump A with asset number 888. The pictures are then a specific asset Pump A and not just a generic Pump A

 

[HelpNewtoAccess]

So thanks again for all this, basically to answer some of your questions.

Answer 1 and 2....Each building has a PropertyID number and each building has many graphics. I used the building abbreviation as the PK as none are ever the same and it is easier to use the abbreviation.

Answer 3 and 4....A graphic has many BasPoints which is the label on the graphic (some for equip. and some are just software points). Each BasPoint has only one note which can be for a software point or a piece of equipment.

Answer 5....Yes the BasPoint could be on two or more graphics in the same building. I've just handled this by linking multiple graphics to one record to add or delete note. Then I create a new GraphicID with all the graphics that the record is on (eg. Steam/DHW). I did this so you can see all the graphics it is on with the one record on the form. Probably could of done something better here.

Answer 6....The equipment could have up to three asset tags one for the equip., starter, air handling unit (some could have none like a software point). Each Asset Tag has separate fields because you want to see them all when you bring up the form with one record.

With the picture table there are one to three pictures of the equipment (each have separate fields equip, tag, nameplate) I have a form that only shows the equip. picture when it loads but if you click a button for tag or button for nameplate it shows the picture tied to that one record.

My original thinking was one to one from basically BasPoint...….
one to many tblBuilding to tblGraphic
one to many tblGraphic to tblBasPoint
one to one tblBasPoint to tblNotes (one Note for tblBasPoint)
one to one tblBasPoint to tblEquipment (one Point one piece of equipment.)
one to one tblEquipment to tblAssetTag (one piece equip three asset tags but all same record)
one to one tblAssetTag to tblPictures (one asset tag -three pictures on one record)

Thoughts,

Thanks