Question about Forms and Text boxes 1

[freefour]

Hey gurus, I am rather new to Access.

I have a form with 2 combo boxes where a person picks a customer and then one vehicle that customer owns. I then pass both the customer and vehicle key to an Invoice form.

I now want to take those keys in the new form and look up the customer and vehicle information from the Customer and Vehicle tables. I was able to do this using list boxes and a sql statement in the Row Source column. However, this is not the way it should be done (imho). I want to use text boxes to display the output? Any help?

Also, after that is accomplished, I want to take the looked up values and insert them into the Invoice table.

Thanks for all your help!!

 

[willir]

freefour

It sounds like both your issues reside on your fields being bound to the table, and data structure.

form with 2 combo boxes where a person picks a customer and then one vehicle that customer owns

Is this data bound to a table? Is there a table, beside the Invoice table that captures this information, or are these combo boxes unbound?

...pass both the customer and vehicle key to an Invoice form

Okay, sounds like your invoice is capturing the data, but why is one form passing the required data to the Invoice form?

A reasonable explanation would be if your were a garage and you are doing work on the car. You would have two tables capturing the "work" data - "tblWorkOrder" and "tblWorkdetail", and two tables capturig the invoice information, "tblInvoice" and "tblInvoiceDetail".

Using this approach simplifies the invoicing process since the invoice grabs the information from the WorkOrder.

Is your situation similar?

I was able to do this using list boxes and a sql statement in the Row Source column. However, this is not the way it should be done (imho).

(Sorry, I am not sure what imho is.)

Is this part of the same process with the two combo boxes, and you have a list box on the invoice form? Would a combo box work instead? If you have to use a text box, then either
- the data has come from the table in question and the text box is bound to the field

... or,
-you use ControlSource uses something like...
DLookup (...

... or,
- you use VBA code, perhaps for the OnCurrent event procedure

Me.YourTextBox = ....

...But in oder for us to fill in the blanks, you need to provide more information.

Richard

 

[hkaing79]

If textbox is called txt1, then can't you just

txt1 = some value?

This is what I use:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = [your sql statement]
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

txt1 = rs.Fields("Field")

rs.Close
db.Close

For insert, use sql...

http://www.w3schools.com/sql/sql_insert.asp

To run it, DoCmd.RunSQL

Good luck.

 

[freefour]

Ok let me clarify a bit:

I am writing a little database for a local garage to print invoices.

I got a form called frminvoiceacquire that first has a combo box with all customers in it. From that they select the customer that needs an invoice created for.

Then it pops up another combo box that has all vehicles that customer owns. They then select the vehicle they did the work on.

That form simply passes the customerID and vehicleID to my frminvoice table.

Now I want to take the key customerID, and select all the customer information (first name, last name, ect) and vehicle information to frminvoice in text boxes and then store it into the Invoice table so I can generate some kind of report that will be a receipt for the customer or so it could be viewed later.

I know this is got to be pretty simple. Hope that helps!

Thanks again!

 

[freefour]

Richard:

imho = in my honest opinoin

sorry for the chat shortcuts. Thanks!

 

[willir]

Oh, I am just an ignorant guy imho. ;-)

Okay, you really should create more than just the invoice table -- otherwise, the garage people will be doing a lot of work, there will be little control, and you will loosing out on using the power of the PC...

tblPart
PartCode - primary key, short text description
PartName
Cost - currency, default amount per unit of usage
RevisionDate

Discussion:
PartCode would be something simiple and intuitive way to remember a cost. CHGTIRE, CHGOIL, FILTER, etc and LABOUR

Cost is unit rate

tblWorkOrder
WorkOrderID - primary key
CustomerID - foreign key to customer table
Vehicle
ServiceDate

tblWorkDetail
WorkDetailID - primary key
WorkOrderID - foreign key to WorkOrder
PartCode - foreign key to Part table
PartUnits - number of units
PartCost - Cost of part / labour
ExtendedCost - Cost * Units
DetailText - capture details for line item

You can then use these two tables to generate your invoice.


Presentation:
WorOrder table used as the main form
Combo box selects owner
combo box selects vehicle

Tab form
One page of tab captures WorkDetail in a subform
One page used to generate the invoice based on the WorkOrder / WorkDetail.

Since the main form is based on the WorkOrder, a field required for both the Workdetail and the Invoice, data integrity is easily maintained.

Richard

 

[freefour]

Richard,

No you're not (imho)!

Thanks, and yes I have other tables set up like you describe. Here's my stupid delima. I have this little form that the end user (who is 99% computer illerate) will pick the name, followed by another combo box that becomes visible and filters out all the vehicles that person owns (they enter all this stuff in another place).

Anyway, that works great, it then automatically launches the Invoice form in data entry mode (this form is linked to the Invoice table) and I have some On load event procedure that looks like this:

Form_frmInvoice.AccountID.SetFocus
Form_frmInvoice.AccountID = Form_InvoiceAcquire.option1
Form_frmInvoice.VehicleID.SetFocus
Form_frmInvoice.VehicleID = Form_frmInvoiceAcquire.option2

Now this fills 2 text boxes with the keys and plugs them into my Invoice table.

Now for my ignorance. I want to take the 2 keys that passed along, and fill in other unbounded text boxes for the Customer's first name and last name, address, ect. automatically. I first thought I would like them save in the Invoice table, but it wouldn't be necessary since they could be easily referenced anytime. In other words, all I need to do is look up the customer's info using the passed AccountID in my where statement. I just need to know how.

I have all my relationships set up and I know it should work, I just need to know how to populate those text boxes. I had success by placing an select statement in Row Source property of an list box, but I don't think that is a proper way.

Thanks for your help Richard and everyone else!

 

[willir]

FreeFour

I want to take the 2 keys that passed along

Where do these keys come from? Is the form open with the required info? We need to know this if we are to help. (I am assuming will pick the name means that the this info is available on the calling form.

fill in other unbounded text boxes for the Customer's first name and last name, address

Piece of cake.

Okay, here is an idea. I am going to assume that the combo box with the customer's name is called cmbCustomer, and the bound field for the cusomter is CustomerID.

Create a combo box on the Invoice form, dont use the wizard, and make it invisible. How? Form is open in design view, and the "Properties" window is open (from the menu, "View" -> "Properties". Click on the Combobox object tool and add it to the form. Cancel out of the wizard, and delete the lable. Click on the "Other" tab in the Properties and change the name to "cmbCustomer". Now click on the "Data" tab and change the value for the "Visible" field to "No".

I would suggest that you use this field to bind the CusomterNo to the Invoice table.

Now we want to create an event procedure for the OnCurrent event. Click on the square box for the form where the verticle and horizontal rulers meet in the top left corner. This selects the form. Click on "Events" in the Property window, and select the OnCurrent field. Choose the [Event Procedures" from the drop down box, and then click on the "..." button to the right of the field. This takes you to the VBA coding window.

You will see...

Private Sub Form_Current()

Sub

Add the following...

Dim strSQL as String, lngCustomerID as Long

'You will have to teak the following to match your form
'and field names
lngCustomerID = Nz([forms]![NameOfYourFirstForm]![cmbCustomer], 0)

If lngCustomer > 0 Then
   strSQL = "Select CustomerID, CustomerLN, CustromerFN, Address FROM tblCustomer Where CustomerID = " & lngCusomterID

   'Now load the combo box with one record for the customer
   Me.cmbCusomter.RowSource = strSQL
   Me.cmbCusomter.Requery

   'You will define three text fields to match
   'text box names on left side of equation
   Me.CustomerLN = Me.cmbCusomter.Column(1)
   Me.CustomerFN = Me.cmbCusomter.Column(2)
   Me.Address = Me.cmbCusomter.Column(3)
Else
   Msgbox "No customer selected", vbOKOnly
   DoCmd.Close acForm, Me.Name, acSaveNo
End If

You will obviously have to tweak...
- associated form and field
- SQL statment to match your design

You mentioned "2 keys". I assume the other is the vehicle. But I don't know the purpose -- well I guess it would be to print the vehicle info on the form. A similar approach can be used.

By-the-way, another approach is to use DLookup. Here, you use one query to grab four fields. The other would require individual statements, and you would still need to bind the CustomerID to the Invoice record.

Richard

 

[freefour]

Richard,

Ok on my first form (the one you select the customer then vehicle):

For some reason, if the combo box says:

1 Smith Bill
2 Doe John

and you select say the second option it just says

2

on the box after selecting, why doesn't it show the whole line?



Anyway, your programming idea is great! I am still having one problem with it:

Here's my code:


Dim strSQL As String, lngAccountID As Long

lngAccountID = Nz([Forms]![InvoiceAcquire]![comboclient], 0)

If lngAccountID > 0 Then
strSQL = "Select FirstName, LastName FROM tblCustomer Where AccountID = " & lngAccountID

Me.cmbCustomer.RowSource = strSQL
Me.cmbCustomer.Requery

Debug.Print Me.cmbCustomer.Value

Me.FirstName = Me.cmbcustomer.Column(1)
Me.LastName = Me.cmbCustomer.Column(2)

Else

MsgBox "None selected", vbOKOnly
End If


--Now I added the debug in so I could see the value because I wasn't getting anything in my lastname box.
I also stopped it right at the beginning of the if statement and stepped along with it.

At the step:

Me.cmbCustomer.RowSource = strSQL

when I moused over the first part it of course says: Me.cmbCustomer.RowSource = "" and over the strSQL side it says: strSql="Select * FROM tblCustomer Where AccountID = 2"

It should work! My debug comes up Null and the combobox just shows "2" in it (I didn't make it not visible yet).

Any ideas would be great. Also, is there a good book or website that has the different functions and commands. I did not know you could use that Nz command and that would have came in really handy in the past!

Thanks again Richard!

 

[freefour]

Richard, I need to correct one thing. When the Invoice form finishes it does not show anything in the cmbCustomer combo box, it does show a "2" when you click on the right side of the box to see the options. Sorry!

Thanks again!!

 

[freefour]

Hey Richard,

I think I got this figured out (sorta).

I changed my SQL statment to grab something else (like Address) and ran my forms.

The form still has nothing showing in the combo box, but if I click on the down arrow it indeed shows the address.

So, what do I need to do to automatically make the first row (since there will always be just one row) the selected item?

Still after selecting the item nothing shows up in my lastname text box.

Thanks again!!!

 

[freefour]

Richard,

I got it! I had to modify:

Me.FirstName = Me.cmbcustomer.Column(1)

to:

Me.FirstName = Me.cmbcustomer.Column(1,0)

and it worked!

Thanks again for your help, I'm sure this won't be the last question I have.

 

[willir]

Glad you are making progress FreeFour

For some reason, if the combo box says:
1 Smith Bill
2 Doe John

You can clean this cosmetic aspect up by hiding the ID number. Open the form in design mode, and make sure the Properties window is open ("View" -> "Properties")

Select your combo box. Select the "Format" tab. Change ColumnWidths to 0";1";1"

The 0" column will be hidden. You will then see the person's last name. You can create an unbound text field to display the first name using a similar approach used to pass the info to the second form.

moused over the first part ... strSQL side it says: strSql="Select * FROM tblCustomer Where AccountID = 2"

My instructions were to use...[tt]
Select CustomerID, CustomerLN, CustromerFN, Address ...
Column 0 1 2 3
[/tt]

SELECT * will retrieve all records. The sequence of records will depend on the design schema.

And glad you are playing -- it is a great way to learn. Especially using the debugging tools, etc.

Richard