Newbee -- set field based on another field in the same record

[rcoutts]

I have a table, 'Jobs,' for all the jobs in our office that contains the fields JobName and JobNumber. I also have second table, 'Time Cards,' that also has the fields JobName and JobNumber and whose values are selected via a combo box linked to the values in 'Jobs.' What I would like to do is after JobName is selected in the table 'Time Cards,' JobNumber is set automatically.

Conceptually its easy to do, but I'm having a Dickens of a time figuring out how. My Time Cards form is a datasheet. I created a Module that gets fired after JobName is updated, but I couldn't find a Function to set the value of JobNumber based on the record number. In my module I have the line "recordNumber = JobName" which correctly returns the record number in 'Jobs' that contains JobName. How do I then set JobNumber based on the value of recordNumber?

It may be that I don't even have to use a Module. If someone could clue me in on the best way to proceed, I'd really appreciate it.

Thanks!
Rich

 

[dawnd3]

Hi there, here is a sample of one of mine. I am not sure if this is a similar scenario, but I needed to have the "CandidateID" field automatically populated from the same field in the main form. It will be entered as soon as I start to enter data in the first field.(I make the field I am trying to copy locked and unenabled so the user doesn't try to type in it) I am sure you can change this to work on the "on open" event as well, this was just how I wanted it to work.

Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me![numCandidateID]) Then
Me![numCandidateID] = Forms![frmMain]![numCandidateID]
End If
End Sub

Did this help?
Dawn

 

[dawnd3]

Rich, I should also clarify that I used the After Insert event in the subform property, not the field.

Dawn

 

[rcoutts]

Thanks for the response. I think what I want to do is a _little_ different. E.g., my Jobs table looks like this:

JobNum JobName Date
------ ------- -------- ...
1123 Smith 1/1/2001 ...
1124 Jones 2/1/2001 ...
1125 Lascarro 3/1/2001 ...

In my TimeCard datasheet, when I select JobName from a combo box, say "Jones," I want the JobNum field to be populated with corresponding JobNum: "1124." Currently my Method has the line recordNum = [JobName] which in the case of "Jones" is set to "2." It seems to me that I need a function that will return the recordNum'th value in the field JobNum?

Thanks again,
Rich

 

[Jables]

Have you thought about running a query on JobName's After_Update property?

Basically it would go like this:

1. Select a Name from the JobName combo box.
2. This calls the after_update subroutine that tells the JobNum combo box to set its recordsource to the results of a query that selects all instances of JobNum from the Jobs table where JobNum corresponds to JobName.

Sorry I can't be more specific with the code, but I'm pretty new to Access and VBA, but I think if you messed around with it for a few hours, it might give you what you want.

Anyone else see this approach as a good idea?