Check to see if number already exists & output

[RCheesley]

Hi all,

I'm coming on leaps and bounds and am actually getting to grips with VBA now (woohoo!) but wondered if someone could point me towards best practice for accomplishing the following task:

I have a DB which contains analysis forms for patients with a particular condition - and they may well have this more than once, and hence have several forms within the Database.

When I enter in a new record, the first field is NHS Number.

What I need to accomplish:

• When the NHS number is entered, I would like to somehow query the table to see whether there is an existing record by that number
• I would like to output the data if it's there, as a list of Form ID's or perhaps a slightly more swanky way of having links to open those forms in read-only view (I already have a read only form established).
  • Could this be done with a subform? If so can it be hidden when there are no existing forms, and only shown if there are?
• I have been using AfterUpdate quite a bit to enable/disable or make visible/hide, but don't know if this could be used? Would I need to write a query?

Thanks in advance

Ruth

 

[georgedwilkinson]

Hi Ruth,

Could this be done with a subform?

Yes. Just link the master and sub forms on the NHS number.

If so can it be hidden when there are no existing forms, and only shown if there are?

Probably, but the process escapes me at the moment (I just woke up).

I have been using AfterUpdate quite a bit to enable/disable or make visible/hide, but don't know if this could be used?

Sounds like it would work, if you get the coding right. You'll probably need to refresh or requery.

Would I need to write a query?

Not necessarily. You could potentially base both forms off of the table. But best practices would require you to write two queries and it could potentially be the exact same query.

 

[RCheesley]

George,

Thanks for the advice, I had thought about the subform but the important part is not having it there if there is no other record found.

Perhaps it might be possible to have it "pop up" as a window with the information in - would that be possible? That way I wouldn't have to worry about having blank bits on the form if there are no other matches.

Ruth

 

[georgedwilkinson]

Yes, it is possible to do it as a popup but remember that is more code. Code that you have to get working and code that could break. I'm not saying it's a deal killer, just another thing you have to take into account.

It's a moderately trivial thing to make a subform visible/invisible. Just set the subform's container's .Visible property.

 

[RCheesley]

Thanks George, good point well made!

If I use the .Visible property, is it possible to use this with something like, if there is a match then make this visible, if there isn't a match make it not visible?

Thus far I have only used it to show/hide depending on a dropdown combo box.

Ruth

 

[georgedwilkinson]

Why sure it's possible. Just code that in whatever event is gonna kick it off.

Yell (when you decide that) if you need help.

 

[RCheesley]

Hi George,

Thanks for your advice.

I've created the subform and linked via NHS number as you suggested.

I've called the subform other_RCAs, from the other AfterUpdate I've used I guess it needs to look something like this but don't know what goes in the ? bit!

Private Sub nhs_no_AfterUpdate()
If nhs_no = present?
  other_RCAs.Visible = True
ElseIf nhs_no = notpresent?
  other_RCAs.Visible = False
End If
End Sub

Ruth

 

[RCheesley]

PS when I say present/notpresent I mean, exists already in the table and hence I want the subform to display. Notpresent means there is no existing RCA and hence the subform is to be hidden.

 

[georgedwilkinson]

Ok, here we go.

You need to find the name of the sub-form's container in order to do this. The container name is different (maybe) than the form name in the database window. You can find it by selecting the subform (the little black box in the upper left hand is visible), selecting properties, and, this is the tricky part, move your cursor just a little outside of the boundary (or maybe right on the outer edge) of the subform and click. If you're lucky, you'll select the subform container. You'll know you're successful when there is a bounding box around the subform and the black box on the subform is gone. Another way of selecting the subform container is to select the form, open the properties, and select every control in the drop down list until the bounding box shows up, as described.

Once the container is selected, the name in the drop down list is the name you want to work with. Or look in the "Name" property.

Now, the simplest answer (not the best, per-se):

Private Sub nhs_no_AfterUpdate()
Me.NameYouGotForTheContainer.Visible = (DCount("nhs_no", "YourTableName", "[nhs_no] = " Me.[nhs_no]) > 1)
End Sub

Comparing the DCount to 1 will return a boolean with which you can set the visible property. My assumption is that the current record has already been inserted. If not, change it to 0 (zero).

Please notice the difficulty with the names. You should rename all your form's controls with a prefix that differentiates them from columns in the database.

 

[RCheesley]

Hi George,

Thanks for the help so far, if I understand you correctly I should have the following code

Private Sub nhs_no_AfterUpdate()
Me.Other_RCAs.Visible = (DCount("nhs_no", "proforma_tab", "[nhs_no] = " Me.[nhs_no]) > 1)
End Sub

Where Other_RCAs is the name of the subform container, nhs_no is the NHS number field in the table called proforma_tab

When I run this, I get the following error:

Compile Error: Syntax Error

Am I doing something blonde here??!!??

Ruth

 

[georgedwilkinson]

Try this:

Private Sub nhs_no_AfterUpdate()
Me.Other_RCAs.Visible = (DCount("nhs_no", "proforma_tab", "[nhs_no] = "  & Me.[nhs_no]) > 1)
End Sub

and if that doesn't work, try this:

Private Sub nhs_no_AfterUpdate()
Me.Other_RCAs.Visible = (DCount("nhs_no", "proforma_tab", "[nhs_no] = '"  & Me.[nhs_no] & "'") > 1)
End Sub