Modules with a Query (1 Viewer)

[kacey8]

Hi Guys,

Hopefully this will work.

We have a button running a SQL query via VBA, how can I make it so the results doesn't show up in a table/preview?

Also, I know it's for the SQL forum but how can I make a text box to show the results of a query to display it on screen?

 

[pr2-eugin]

All you need is a DLookup or some Domain function rather than using DoCmd.OpenQuery.

 

[smig]

A query will normaly output m as ny records, with few fields for each record.
How do you expact to put all of this into a text box ?

 

[Minty]

? Could you post your button code?
At the moment I/we have no idea what or where you might be displaying the results.

And in the first question you don't want it displayed, but in the second you do ? Am I missing something here?

 

[kacey8]

Sorry my mistake for posting so obscurely and via Mobile while rushing around.

So I have a Combo Box, You select a postcode in the combo box and hit a button,

The button runs a query

Private Sub Command12_Click()
DoCmd.OpenQuery "SELECT Query"
End Sub

Obviously as it just runs the query a datasheet pops up with the results of the query (I need to hide this datasheet popping up)


The query is this

SELECT Postcode
FROM Postcodes
WHERE Postcode = [Forms]![Havering Lettings_RM1]![Text27];

(Text27) is the Combo box.

So basically this runs a query which then returns the item selected from the drop down box.

I then need the result of the Query (the postcode) to appear in a text box (I assume this will be Dlookup) this will be on another page.

I hope this makes sense.

 

[pr2-eugin]

As I said before.... Use a Domain function instead.

Private Sub Command12_Click()
    Me.yourTextBoxName = Nz(DLookUp("Postcode", "Postcodes", "Postocde = '" & Me.Text27 & "'"), "Nothing found")
End Sub

Not being Funny. But you are using a ComboBox, so you select a postcode BH5 1HA, then you look in the postcode table to return the same? This assumption is based on the SELECT query you have given us.

 

[Minty]

You should call your controls something more meaningful - Text27 won't mean a thing to you in a months time when you come back to look at this...

I think you could achieve what you want in one combo box with an after update event and a dlookup, you don't probably need the other form.

Can you explain your whole task you are trying to achieve?

 

[kacey8]

Yes.. it seems funny but I'll explain it to you.

I have a table with four columns, it has

ID (not used)
Postcode
Drop Number
Last Drop Date

The postcodes contained in the table is for an area covered by 15 offices and has drop numbers set to each one for the numbers of drops and will then (when I work out how) have the date of the last drop


On the form, the user can choose his office, choose a postcode for his office eg XX1 from the combo box and then hit the select button. This will run the query which pulls the information of the postcode and then a pop up will appear displaying the information.

We didn't use the table as a source for the record box as the table contains 200 plus postcodes but his office list may only display 15 of them on the drop down list.

I hope this makes sense?

We've not tried adding drop number and last drop date yet, we want to get it working with just postcode.


So the text box where the postcode will appear will be on another form which is set to a pop up.

 

[kacey8]

You should call your controls something more meaningful - Text27 won't mean a thing to you in a months time when you come back to look at this...

I agree, This is just a test I am working through as a proof of concept before we work on the life database