sqL or Query?!! (1 Viewer)

Sonya_G

New member
Local time
Today, 05:19
Joined
Oct 2, 2019
Messages
9
Okay programmers this Is what’s up:

I am biuldiing a diabetic program, and the main area of topic is the Combo box. I have it coded so that the (list) insulin_nation is either Canada, USA, from there somethings hide and others show. On the click event of insulin_nation it pulls all the names from patient ad loads them in name_Combo (full name). What I want it to is select all the names from patient based on the choice from the list box (insulin_nation), who’s country in the patients table must match. I tried putting a filter on, it works but not eiffecently. So my two other choices would be SQL, or query. I am thinking SQL. Here is what I have so far:
Private Sub nation_Combo_AfterUpdate()
Dim nation As Variant
nation = nation_Combo
If nation = "Canada" Then
Me.name_Combo
SELECT name_Combo, country
from patient
where country = "Canada"


insulin_Combo.Visible = False
CAN_insulin_Combo.Visible = True
Zcell.Visible = True
ratio.Visible = False
corr_ratio.Visible = False

Else

mysql>SELECT full_name, country
from patient
where country = "USA"

insulin_Combo.Visible = True
CAN_insulin_Combo.Visible = False
Zcell.Visible = False
ratio.Visible = True
corr_ratio.Visible = True

End If
End Sub
View attachment diabetic.zip
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:19
Joined
Oct 29, 2018
Messages
21,357
Hi. Is there any way you can post a small copy of your db with test data and only the form you're having problems with?
 

Micron

AWF VIP
Local time
Today, 08:19
Joined
Oct 20, 2018
Messages
3,476
I'm confused by your post.
- At one point it seems like you have a combo for nation but then you describe it as a list box.
- Then you mention a click event but post code for after update event.
- A query is based on SQL language so they are one and the same. Maybe you mean a sql statement in vba?
- you cannot use > in this context
"mysql>SELECT full_name, country"; even if you could it would make no sense.
- you would not set the value of a combo or list box as in
"Me.name_Combo SELECT name_Combo" you would set its rowsource property.
- sql statements in code must be enclosed in quotes. You have none, save for quotes around the criteria. You must enclose the entire statements in double quotes and the text type criteria has to be delimited too. Typically this is done via singles ' or doubling up on double quotes. I find using singles to be far simpler.

EDIT - forgot to ask you to post code within code tags (# on forum toolbar) with indentation to make it easier to read.
 
Last edited:

Sonya_G

New member
Local time
Today, 05:19
Joined
Oct 2, 2019
Messages
9
here you go, I just started. The form I am working with is USA calc1.
 

Attachments

  • diabetic.zip
    89 KB · Views: 250

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:19
Joined
May 7, 2009
Messages
19,169
instead of hiding/unhiding textbox, which may make your form have gap like teeth, you make them Enable or Disabled.

also you can make separate form for each country, put it on Tab control and switch Page when the country combo changes, see this sample.

I remove the Tab from the Pages.
 

Attachments

  • diabetes.zip
    46.2 KB · Views: 248

Sonya_G

New member
Local time
Today, 05:19
Joined
Oct 2, 2019
Messages
9
I went back in and followed your direction, rebuilding and recoding but I have some problems. On he main calc my combo and button do not how up in form view, only design view. Next I am having troubles figuring out the tab control # so that Ii can code it. Help!?:mad:
 

Attachments

  • dia-doc1.zip
    89.1 KB · Views: 247

June7

AWF VIP
Local time
Today, 04:19
Joined
Mar 9, 2014
Messages
5,423
Need to move combobox and button into Form Header or Detail section, not Page Header. Page Header/Footer only displays when printing, not screen display.

There is no Tab control on [main calc]. Subforms are just sitting on top of each other, not on Pages of Tab control. With this arrangement, code would be:
Code:
Private Sub nation_Combo_AfterUpdate()
Me.CANpatients.Visible = Nz(Me.nation_Combo, "") = "Canada"
Me.USApatients.Visible = Nz(Me.nation_Combo, "") = "USA"
End Sub
Set both subform containers as Visible No in [main calc] design view. Remove [blanc] subform.
 
Last edited:

Sonya_G

New member
Local time
Today, 05:19
Joined
Oct 2, 2019
Messages
9
Thanx for the tip, it makes sense why it doesn't work.:D
 

Users who are viewing this thread

Top Bottom