newbie needs help making queries from code

[njcalugar]

I am trying to modify an existing Access database/application to mak
HTTP call to a PHP script. I have successfully created a button on
form that causes the script to be called and the result displayed in
Microsoft Web Browser control.

Now I need to make the query string of the HTTP call more interesting
i.e. pass some variables to the PHP script. I have figured out how t
get the value out of a text box. That was easy. There is a Pulldow
control that is linked to a table in the database that allows you t
select a person by last name. If I get the value of the pulldow
control, I get the value of the ID field for that row in the database
So now that I know the ID of the selected person, how can I get othe
values from that row in the database. I'm assuming I need to make
query of some sort but I'm not a VB programmer and cannot figure ou
the syntax/sematics to query the database.

Thanks,
Nicholas Caluga

 

[bud2148]

Hope I am understanding your question. You have a dropdown box whic
displays Customer Names. Since the names are tied to the CustID in th
Customer table, you want to see the value for the other columns.
Meaning, the Name, Address, phone No etc.. right?

If so, you can create a Query using Customer table and filte
(criteria section) by the ID that you are collecting from the drop-dow
box.

Bud214

 

[njcalugar]

Thanks for your reply, Bud.

Unfortunately, you are dealing with a PHP-MySQL guy on this end.

Are you saying to create a query with one of the wizards?

How can I run this query from the _click function of the button?

Can I create a query from the VB code without creating it in
wizards?

You might have to spell this out for me because I'm very unfamilia
with the correct programming practices in Access. I'm just trying t
add a feature to an existing form and don't plan on becoming an Acces
expert.

Thanks for your patience,
Nicholas Caluga

 

[bud2148]

Yes, you can have the "Button" run a query through code. Also, th
code will modify the existing query based on what you have selected o
the drop down box. for instance,:

CustID Name
1 John
2 MIke
3 Robert
4 Michael


I got a better idead: Let's go ahead and use the Find and See
function.


In the click event, do the following:

Dim rs as recorset
dim strCustomerName as string
Dim intCustomerId as integer

intCustomerID = Me.(DropdownName)

Set rs=currentdb.openrecordset("Name of table")

With rs

.index = PrimaryKey
.seek "=" intCustomerID

If .Nomatch = true then

msgBox("The customer is not in the database")

Else

strCustomerName = rs("fieldName")

End if

End wit

 

[njcalugar]

Bud,

Dim rs As Recordset
Dim strCustomerName As String
Dim intCustomerId As Integer

'I had to change this:
intCustomerId = combobox.Value

'This line gives me a "Type mismatch" error:
Set rs = CurrentDb.OpenRecordset("Customers")


Thanks,
Nicholas Caluga

 

[bud2148]

I just did it and it works. See sample below.

Private Sub Button_Click()

Dim rs As Recordset
Dim intId As Integer
Dim strname As String

intId = combobox.value ' Which is equal for to 1 for this example

Set rs = CurrentDb.OpenRecordset("Customers")

With rs
.Index = "primarykey"
.Seek "=", intId
If .NoMatch Then

Else

strname = .Fields(1)

MsgBox ("This is the name for ID 1:" & strname)

End If
End With


Is the "Customer" the name of your table

 

[njcalugar]

In my last reply I changed the table name to match your example...her
is my code:

Dim rs As Recordset
Dim strCustomerName As String
Dim int_ContactID As Integer

int_ContactID = salesrep1.Value

'With the following uncommented I get "type mismatch"
Set rs = CurrentDb.OpenRecordset("Contacts")

'With rs

'.Index = PrimaryKey
'.Seek "=" int_ContactID

'If .Nomatch = True Then

'MsgBox ("The customer is not in the database")

'Else

'strCustomerName = rs("MobilePhone")

'End If

'End Wit

 

[bud2148]

When you enter the following:

Set rs = CurrentDb.

Do you get a dropdown box with a list of fields that you can use? I
not, then you will need to set your references

 

[njcalugar]

Yes, the code hinting is working properly. I can select OpenRecordse
after typing "Set rs = CurrentDb.". It also hints the parameter list o
a String and other optional arguments.

Thanks,
Nic

 

[bud2148]

Open the Contact table, what is the field type for "salesrep1" field

 

[njcalugar]

salesrep1 is the name of a pulldown (combo box?)

 

[bud2148]

Good. Open the Contracts table and what is the field type for the firs
field

 

[njcalugar]

AutoNumber->Long Intege

 

[bud2148]

Good. Now, let change the followng line:

Dim int_ContactID As Integer

to

Dim int_ContactID as Long


Also, move the following line:

int_ContactID = salesrep1.Value

after, Set rs = CurrentDb.OpenRecordset("Contacts")



Then try again

 

[njcalugar]

Dim rs As Recordset
Dim strCustomerName As String
Dim int_ContactID As Long

Set rs = CurrentDb.OpenRecordset("Contacts")

int_ContactID = salesrep1.Value


Same results. If I comment out the "Set rs..." line the code execute
to completion. I feel like I'm missing something so obvious that i
isn't obvious. or maybe I don't have Access set up properly. Like
said, this isn't my area of expertise

 

[njcalugar]

although, I have tried silly stuff like getting the version of th
CurrentDb and that worked fine..

 

[bud2148]

Can you please copy the full procedure from start to finish includin
the end sub or end funtion.

I will need to look at it before we go to the next debug stage

 

[njcalugar]

Private Sub btn_sendSMS_Click()
On Error GoTo Err_btn_sendSMS_Click

Dim sendSMSURL As String


'****************************************************
Dim rs As Recordset
Dim strCustomerName As String
Dim int_ContactID As Long

Set rs = CurrentDb.OpenRecordset("Contacts")

int_ContactID = salesrep1.Value


'With rs

'.Index = PrimaryKey
'.Seek "=" int_ContactID

'If .Nomatch = True Then

'MsgBox ("The customer is not in the database")

'Else

'strCustomerName = rs("MobilePhone")

'End If

'End With

'****************************************************

'Setup the Query String
sendSMSURL = sendSMSURL
"http://www.2chatter.com/fidelity/sendSMS.php"
sendSMSURL = sendSMSURL & "?numberSR1=" & URLEncode(int_ContactID)


'Run the PHP Script
WebBrowser1.Navigate sendSMSURL

Exit_btn_sendSMS_Click:
Exit Sub

Err_btn_sendSMS_Click:
MsgBox Err.Description
Resume Exit_btn_sendSMS_Click
End Su

 

[bud2148]

Let's go to Tools\References and see what we have there. Please list.

If that does not work then we can try another method. Like I sai
there's several ways in getting that data

 

[njcalugar]

[image: http://www.2chatter.com/fidelity/references.jpg