Updating a table field from a form the 'Lazy' way

[kidvegas19]

One table with 2 fields - Number, Name

What I want to do (by using a form) is when I enter a number in the number field the Name field is populated AND saved to the table. Example:

Input the number 1 in the Number field of the form

The Name field is automatically populated with "Customer 1"

When I advance to the next record, this information is saved to the table.

I have a limited number of customers, so I am thinking an If/Then statement would suffice. Can't seem to make it work. Any help would be appreciated.

TIA

 

[pdldavis]

Hi, I don't quite get what you are after. If you have a table with two columns, CustID and CustName, you have to input both fields unless CustID is an Autonumber. Otherwise, how is the name going to appear?

Is for a list of names, or is it some sort of link table -linking a customer to an order number?

Need a little more detail, I think.

 

[TheAceMan1]

[blue]when I enter a number in the number field [purple]the Name field is populated[/purple] AND saved to the table.[/blue]

[purple]All well & good[/purple] accept . . . . . where is the data for the [blue]Name Field[/blue] coming from?

What is the Name of the Table?

Note: when you change records, the previous record is automatically saved.

See Ya! . . . . . .

 

[kidvegas19]

Thanks for the replies. The customer name would come from an if/then statement. In another unrelated database I had an if/then statement in the form's BeforeUpdate event procedure that entered points. Example:

If Finish = 1 then
Points_Earned = 50
ElseIf
Finish = 2 then
Points_Earned = 45
(so on and so on)
End If

This worked perfectly. Points_Earned in the underlying table updated and I could ship it on it's merry way. I would like to do something similar for this little database.

If Number = 1 then
Name = Customer 1
ElseIf
Number = 2 then
Name = Customer 2
(so on and so on)
End If

The problem I am having is that the Name field is neither populated or saved.

Thanks again.

 

[pdldavis]

The problem I am having is that the Name field is neither populated or saved.

As the AceMan1 said, where is the data for the Name field coming from? Another table? Or are you building customer names directly into the If Statement?

 

[TheAceMan1]

kidvegas19 . . . . .

Let me try another way . . . . .

In your If/then statement, what is the [blue]Data Source[/blue] for the name?

See Ya! . . . . . .

 

[kidvegas19]

The data source for the Name field would be the If/Then statement, like the one that works for my Points example. With that database I have one table. The two fields that are dependent are Finish and Point_Earned. When entering data using the form, the data for Points_Earned comes from an If/Then statement; Finish being the IF and Point_Earned being the Then. Without me having to click, drop down and choose, or physically type anything in the Points_Earned field on the form it is updated on the form and stored correctly in the table.

I figured it should be just as easy for my Number-Name database. Without having to build extra tables containing Name information used for lookups, one should be able to accomplish the same results as the Points example with an If/Then statement (Number being the If, Name being the Then). When a 1 is entered in the Number field on the form, the Name field on the form fills in Acme Industries and it is saved to the underlying table. Enter 26 on the next record in Number and Name becomes Zinkly Widgets Inc. When the table is opened there would be two rows of data:

Number| Name
1 | Acme Industries
26 | Zinkly Widgets Inc

(No fictional company was harmed in the making of this post, LOL!)

I think the Finish/Points_Earned If/Then process works because it is dealing with integers. The Number/Name is not working because the Name is a string. That is what it looks like on the surface.

 

[pdldavis]

Ok, assuming you have two text boxes, txtNo (number) and txtName (Name) - in the after update event of txtNo:

Private Sub txtNo_AfterUpdate()

Select Case Me.txtNo

Case Is = 1

Me.txtName = "Joe Smith"

Case Is = 2
Me.txtName = "John Brown"

Case Is = 3
Me.txtName = "Jim Jones"

Case Else:

MsgBox "Not in your List"

End Select


End Sub


That should save it for you.

HtH

 

[kidvegas19]

Thanks pdl. It worked after I made some changes. Below is the code that worked.

[COLOR=blue]Private Sub[/color] Name_GotFocus()[COLOR=green]{Changed to GotFocus because I got error: You can't reference a property on a method for a control unless that control has focus.}[/color]
[COLOR=blue]Select Case[/color] Me!Number
[COLOR=blue]Case Is[/color] = 1
Me!Name = "Case 1" [COLOR=green]{Changed from Me.Name to Me!Name because I got error: Invalid Qualification}[/color]
[COLOR=blue]Case Is[/color] = 2
Me!Name = "Case 2"
[COLOR=blue]Case Is[/color] = 3
Me!Name = "Case 3"
[COLOR=blue]Case Else[/color]
Me!Name = "not in list"
[COLOR=blue]End Select[/color]
[COLOR=blue]End Sub[/color]

I also met my goals via the If/Then. It was all syntax. Here is the code that works:

[COLOR=blue]Private Sub[/color] Name_GotFocus()
[COLOR=blue]If[/color] Number = 1 [COLOR=blue]Then[/color]
Me!Name.Text = "Acme Industries"
[COLOR=blue]ElseIf[/color] Number = 26 [COLOR=blue]Then[/color]
Me!Name.Text = "Zinkly Widgets"
[COLOR=blue]End If[/color]
[COLOR=blue]End Sub[/color]

Thanks again to all for your help. I can now be filed under "Happy Camper".