Opening a Form with Query Specific Data

[Steed]

Evening all,

I'm very new to VBA and am struggling badly with this one. I'll explain what I need to do as the title is a little unclear!

I'm making an insurance calculator for classic cars, and on my welcome screen I have a button called lablled Edit Existing Quote. What I would like this button to do is, upon clicking, ask the user to enter the Reg Number (the PK in my client data table) of the client whos details they wish to edit. Once a reg number is entered and submit or something has been clicked I want that record to then appear in form view.

I've got as far as the:

DoCmd.OpenForm "frm_New_Quote", acNormal

(The form is named so because I use it to add new data as well, using the 'DoCmd.GoToRecord , , acNewRec' code, the form otherwise shows client data for the last record entered.)

I'm guessing it'll involve vbQuestion somewhere in there, or possibly some SQl...I'm really not sure. I only have a very basic knowledge of VBA and the two lines I've pasted there are about all I know! I'm staring at my VBA book but failing to find the relevant pages.

So, can anyone help me out with this one? It should run like :

Click 'Edit' button on welcome screen, then little box pops up asking for reg number, then that clients details is presented.

Please help!

P.S
my client data table is called 'Client Tbl' at the moment.

[Scott Price]

Well, a general overview would be:

Base your frm_New_Qoute on a query that includes as it's WHERE criteria the text/combo/list box that you are using to enter the Reg Number. In the query builder/design view just enter the qualified name of the text box. Qualified means like this: Forms![Form name].[Control name]

Then when you click OK and the form opens, it automatically queries it's underlying data source, which in this case is the query that you created with the WHERE criteria pointing to the text box. Obviously this pop-up form needs to be open for it's values to be registered, which means that you will include the command to close that form in a later event of your frm_New_Quote.

Anything you don't understand, just ask!

Regards,
Scott

[Steed]

Hey thanks for the reply! That stuff really really wants to make sense in my brain but it can't quite stick itsself into code for me.

I'm confused as to how you go about doing this, having the system ask for a variable which then calls up the relevant data. I can make the input dialogue box using code from the help files:

Dim Message, Title, Default, MyValue
Message = "Enter a value between 1 and 3"  ' Set prompt.
Title = "InputBox Demo"    ' Set title.
Default = "1"    ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)
 
' Use Helpfile and context.
' The Help button is added automatically.
MyValue = InputBox(Message, Title, , , , "DEMO.HLP", 10)
 
' Display dialog box at position 100, 100.
MyValue = InputBox(Message, Title, Default, 100, 100)
 
End Sub

Would I use that as part of a larger procedure to then use the data inputted and do something with it? Or would I add to that code itself?

Currently if you click OK on the produced dialogue box it just reopens itself, and I don't ahve a clue how to make it do something more useful.

Sorry to be so noobish, but a little more assistance would be greatly appriciated.

Thanks,

_Ed

[Scott Price]

Hi Ed,

No need to apologize for 'noobism' :-) On the Scripts we like to think of ourselves as 'Experts helping Experts', but the reality is more like experts helping noob's become experts.

As far as what you are attempting, it's probably going to be much much easier to create your own pop up form. Place a text box on it. Then in the query that I referred to in my first reply post, refer to this text box. The following is a query copied directly from one of my databases:

SELECT tblCrew.*, tblConvInformation.ConventionName
FROM tblConvInformation INNER JOIN tblCrew ON tblConvInformation.ConvID = tblCrew.ConvID
WHERE (((tblCrew.ConvID)=[Forms].[frmConvInformation].[txtConvID]))
ORDER BY tblCrew.CrewPosition DESC;

You will notice particularly the WHERE clause and it's syntax. Once you have your query and form created, we can take a look at what more you will need to do.

Regards,
Scott