Include a table field within the message of msgbox for confirmation

[janbrown56]

Hello,

I would like to pop up a message box that includes a value from a table record within the message so the user can confirm that the information is correct.

Example - user is going to create a new document and they provide a lot number. The association part number is queried and appended to a table. Table LotPart has 2 fields: Lot and Part. Let's say the part number for the lot they enter is "25CHENS". I run a Make Table Query to store that lot and part number in. Before continuing I would like a msgbox to say "Is 25CHENS the correct part number?" to which they answer Yes/No.



I know how to program the msgbox command to display Yes/No and to either stop the macro or continue based on their answer. What I would like to know is how can I display the Part within the message of the msgbox?

I thought it would be something like MsgBox("Is " &[LotPart].[Part]& " the correct part?",4)=6 Stop Macro.

I'm willing to do this in the VB code but would appreciate some assistance as I'm not sure how to do this.

Does anyone know how to do this? Thanks for your help.

Jan

[uncletreetrunk]

I think you should have a form that the user can add to your table LotPart.

Then you could use VBA to confirm their entry before appending your table.

[TheShabz]

Where does the user enter in the part? If this is on a form, you do
If MsgBox ("Is " &Me.controlname & " the correct part?") = vbNo Then
ExitSub
Else
Code
End If

[uncletreetrunk]

In an add button in your form have a message box similar to this:

If MsgBox ("Message Here",vbOKCancel,"Message Title") = vbOK Then
'Add to Table
Else
Exit Sub
End If

[janbrown56]

I have a form with a button that is "Create New". It runs a macro that Prompts the user for the Lot number via a Make Table query that joins a table with the Lot data to the user's entered response (Criteria = [Enter Lot Number]) and makes the table LotPart that contains the Lot Number and the Part Number. I then want to confirm with the user that the Part Number is correct. That's when I wanted to pull the Part number associated with the Lot number and display it within the message box.

If it cannot be done this way then I will change my process and use a form. I'm just not very experienced with forms...

[uncletreetrunk]

Hmmm... and I'm not very experienced with macros. You could convert the macro you have built into your button to VBA.

Under "Database tools" Tab and "Macro" section there's a button for converting macros to VBA

Then you could put similar code to what I or TheShabz posted.

You're going to need a DLookup call and a unique table ID to verify the users input

see:http://www.techonthenet.com/access/f...in/dlookup.php

[uncletreetrunk]

For using forms create a form under Create>Form Design

Add text boxes. Give them useful names like "Lot" and "Part"

From there you could have essentially the same setup with your button performing the same actions.

The advantage is that it's easier for the user to read and you can add input checks in vba
I know learning vba is not the most fun task but I would highly advise it because it gives you a lot of control and potential for your database